Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting formula with absolute cell and relative cell
hello, this is my first time here. a friend suggested that you all
could help me out. here is the situation. i have a set of data which i need to insert a formula for. i need to insert the formula and make sure that one of the cells that the formula is using is abolsute and that the other is relative. example: I have a list of data in column A. A1 contains the number which needs to be absolute. A2:A4 contain the data that should be relative. B will contain the formulas. B2 should be something like this =A2/A1, B3 should be =A3/A1..etc. I decided to approach this by name A1 as MyCell...but i cant seem to make a formula which will include the other relative cells...Help!! thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting formula with absolute cell and relative cell
you just need an absolute reference to a1.
from your example in b2 enter , = a2/$a$1 when you copy down column a, b3 will be, =a3\$a$1 b4 will be, =a4\$a$1 and so on -- Gary "Ernesto" wrote in message oups.com... hello, this is my first time here. a friend suggested that you all could help me out. here is the situation. i have a set of data which i need to insert a formula for. i need to insert the formula and make sure that one of the cells that the formula is using is abolsute and that the other is relative. example: I have a list of data in column A. A1 contains the number which needs to be absolute. A2:A4 contain the data that should be relative. B will contain the formulas. B2 should be something like this =A2/A1, B3 should be =A3/A1..etc. I decided to approach this by name A1 as MyCell...but i cant seem to make a formula which will include the other relative cells...Help!! thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting formula with absolute cell and relative cell
Enter in B2 =A2/$A$1
and extend down the column (click onto the right hand bottom corner and drag down) A1 will stay the same A2 will change to A3 etc -- Greetings from New Zealand Bill K "Ernesto" wrote in message oups.com... hello, this is my first time here. a friend suggested that you all could help me out. here is the situation. i have a set of data which i need to insert a formula for. i need to insert the formula and make sure that one of the cells that the formula is using is abolsute and that the other is relative. example: I have a list of data in column A. A1 contains the number which needs to be absolute. A2:A4 contain the data that should be relative. B will contain the formulas. B2 should be something like this =A2/A1, B3 should be =A3/A1..etc. I decided to approach this by name A1 as MyCell...but i cant seem to make a formula which will include the other relative cells...Help!! thanks! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting formula with absolute cell and relative cell
thanks for the quick reply...
but the issue with this worksheet is that the number of columns going across the right will vary from worksheet to worksheet...and i would have to copy and paste that formula...and i'm assuming it will still refernce that absolute... example: worksheet contains five columns of data. i've inserted a column inbetween each. soo now we are at ten columns. but i want to enter that formula..saying that the first cell of each coumn is the absolute and the rest of the relative. how could i do that and still be able to copy and paste that to each of the other columns? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting formula with absolute cell and relative cell
not sure what you mean, but yes, this will always reference a1, no matter
which column it is copied to or if you insert a column between a and b. try it and see if it works or doesn't work and give us an example of what didn't work. the $ preceding the "A" will tell excel to always reference that column and the $ before the 1 will tell excel to always reference that row. you can keep absolute references to row, columns or both depending where you put the $. -- Gary "Ernesto" wrote in message oups.com... thanks for the quick reply... but the issue with this worksheet is that the number of columns going across the right will vary from worksheet to worksheet...and i would have to copy and paste that formula...and i'm assuming it will still refernce that absolute... example: worksheet contains five columns of data. i've inserted a column inbetween each. soo now we are at ten columns. but i want to enter that formula..saying that the first cell of each coumn is the absolute and the rest of the relative. how could i do that and still be able to copy and paste that to each of the other columns? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting formula with absolute cell and relative cell
again thanks for all the replies...but let me apolgize and make myself
a bit clearer... i'm writing a macro for my worksheet. I've been able to anchor down the abolsute cell. I've named the cell...but what i'd like to know is how can i add the relative cell into my formula along with the absolute cell... thanks again! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting formula with absolute cell and relative cell
enter in B2 =A2/A$1
you can copy this across to the other columns in D2 it will become =C2/C$1 and then down it will become =C3/C$1 -- Greetings from New Zealand Bill K "Bill Kuunders" wrote in message ... Enter in B2 =A2/$A$1 and extend down the column (click onto the right hand bottom corner and drag down) A1 will stay the same A2 will change to A3 etc -- Greetings from New Zealand Bill K "Ernesto" wrote in message oups.com... hello, this is my first time here. a friend suggested that you all could help me out. here is the situation. i have a set of data which i need to insert a formula for. i need to insert the formula and make sure that one of the cells that the formula is using is abolsute and that the other is relative. example: I have a list of data in column A. A1 contains the number which needs to be absolute. A2:A4 contain the data that should be relative. B will contain the formulas. B2 should be something like this =A2/A1, B3 should be =A3/A1..etc. I decided to approach this by name A1 as MyCell...but i cant seem to make a formula which will include the other relative cells...Help!! thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change a cell reference from relative to absolute | Excel Discussion (Misc queries) | |||
Absolute and Relative Cell References | Excel Discussion (Misc queries) | |||
F4 key does not toggle relative/absolute cell references. | Excel Worksheet Functions | |||
Absolute vs. Relative Cell References | Excel Programming | |||
Relative/Absolute Formula writing in to a cell. | Excel Programming |