Free Microsoft Excel 2013 Quick Reference

How do you copy a formula without incrementing some constants?

I have a formula that I want to copy across different worksheets in a
workbook but it keeps incrementing values that need to remain constant.
The formula is as follows:
=VLOOKUP(A95,Items!A2:B500,2,FALSE)
And the part of the formula that I don't want to change is "A2:B500" (Items!
being alpha remains as does "2,FALSE")
I've tried using paste special but have not found the right combination. Is
there a wilcard character that can be used? I've seen the use of $ but am not
sure of the ramifications and where/when to use it.
Currently, I'm having to manually edit each formula but there's got to be a
better way!
Thanks in advance for any help you can offer,
-jt.


Post your answer or comment

comments powered by Disqus
I have a formula that I want to copy across different worksheets in a
workbook but it keeps incrementing values that need to remain constant.
The formula is as follows:
=VLOOKUP(A95,Items!A2:B500,2,FALSE)
And the part of the formula that I don't want to change is "A2:B500" (Items!
being alpha remains as does "2,FALSE")
I've tried using paste special but have not found the right combination. Is
there a wilcard character that can be used? I've seen the use of $ but am not
sure of the ramifications and where/when to use it.
Currently, I'm having to manually edit each formula but there's got to be a
better way!
Thanks in advance for any help you can offer,
-jt.

How do you copy a sheet from one workbook to a different workbook?

Sub Workordercopy()
'
ActiveSheet.Select
ActiveSheet.Copy Before:=Workbooks("workorders.xls").Sheets(1)
End Sub

How do I copy & paste formulas without the formulas linking back to the
original source spreadsheet. The formulas should calculate only from
internal cells and tabs .

HOW DO YOU MAKE A FORMULA STOP AT A BLANK CELL? For example, if you have data in a single column with a blank row separating each data set, how can you have a sum displayed in the separating space if the number of rows between each space is different? Obviously I'm looking for a fuction to automate the task to avoid manual processing.
Thanks!

How do I copy & paste formulas without the formulas linking back to the
original source spreadsheet. The formulas should calculate only from
internal cells and tabs .

I created a list by mistake on a column and now I can't take it off. How do
you delete a LIST without deleting the data?

thanks

This is probably something simple, but how do you include a 'return' within data you are entering in a cell (so that the data fills two or more lines)?

Eg - Product
Name

I have tried using the vbCrLf function that you use in a message box, like this:

Cells(1, 1).Value = "Product" & vbCrLf & "Name"

But this leaves a small square between the two words(?)

Any ideas?

Hi another easy one (I think) for the MVPs

If I have a cell lets say A1 with a formula in it say "=A2" how do I copy A1 into A3 so that A3 will contain the text =A2 (and not the formula)?

I have tried Paste Special but this doesn't do the trick....

How do you embed a new line in a formula similar to &" "& but with a new line
instead

I have a formula such as "=sum(d34+d35)", I decided to move the location, but
when I move the location the formula is now "=sum(d31+d31). I would like to
move the first formula anywhere on the spreadsheet w/o it being changed. How
do you do that? Is it an absolute value or something like that?

How do I copy a formula from one workbook to another so that the pasted
formula does NOT have a link back to the copied workbook...... WITHOUT
redirecting/editing the Link?

I have a formula such as "=sum(d34+d35)", I decided to move the location, but
when I move the location the formula is now "=sum(d31+d31). I would like to
move the first formula anywhere on the spreadsheet w/o it being changed. How
do you do that? Is it an absolute value or something like that?

How do you copy an entire row in excel to a new tab based on a cell value. Then delete the cell in the first sheet

How do you write a conditional formula that answers for example IF A3 < B3 then B4 =B5 + B6, and IF A 3 = B3 then B4 = B5 * B7 and IF A3 > B3 then B4 = 0

How do you put the above formula into a cell using vba? Below is what I've come up with.

However, the second " cuts off the first one. Does anyone know how to get around this?

How do you display a zip code with a leading zero without changing the format
to a text field?

How do I create a formula that references a worksheet tab without using the
tab's name? I'm using the formula in a different file from the source data
I'm referencing. This source file will be written over constantly with a new
source file. The file name and path will always remain the same, and I
always want to reference the same worksheet tab and cell, but the worksheet
tab's name will change. In other words, I always want my file to reference
cell A2 in the 2nd tab from the left from the source file.

I need to know how to copy a formula and have it adjust

How do you copy RANK w/o it changing the range when you are copying multiple
cells on a worksheet in Excel 2003?

How do I copy cells with relative references into other cells kepping the
same references? e.g. cell A3 has the formula A1+A2. A6 has the formula
A4+A5. I want to copy A3 and A4 into A15 and A16 in order to have A15 with
the formula A1+A2 and A16 with the same formula as A6 (A4+A5).

*** In Excel how do you .....

- code a formula in cell B1 to do the following ....?

Example
If cell B2= anything from 0.001 to 1.999
Then I need cell B1 to show 2.000

If cell B2 = 0.000
Then I need cell B1 to show 0.000

But If cell B2 is anything from 2 or higher
Then I need cell B1 to be whatever number is in cell B2

Thanks ahead a time for assistance with my problem.

How do I keep a formula from changing?
Just as a primitive example:
In cell A1 is the formula =B1
In cell B1 I write the word cat.
Now A1 will show the word cat.
So far, so good.
However, if I insert a column between A and B
the formula in A1 now shows =C1.
I want the formula in cell A1 to stay and continue saying =B1 after I insert a column.
Is it possible to "freeze" a formula.

I am creating an excel file for employees to enter their time. I want to have a formula set up that will automatically total the number of hours spent on specific project categories. For example, cell C6 is a drop down list having multiple categories to choose from. Cell D6 is the total number of hours spent on that project. Cell C7 is a drop down list and D7 is the total number of hours, etc... How do I create a formula that will total only the total number of hours for a specific category selected in the drop down list?

For Example:
Totals hours spent on administration: =D7 if C7 = administration

I don't know if this is possible, but I hope so! If I'm confusing, I apologize because I'm not well versed in computer terminology.

Thanks,

Hello,

How can I copy a formula but only every 65 cells.

For exmaple, I want the formula in A66, A110, and so on but not in A65 or A67.

The problem is that I have a table full of information and I need to extract identification characters from it. They appear every 65 cells at the exact same location.

Please let me know if I explained it decently.

V.


No luck finding an answer? You could always try Google.