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!

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!

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!

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?

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?

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!

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!