Auto repeate parts of functions
 

Hi!

I have a function in the function bar of my excel spreadsheet, and it
works well, and here is part of it:

=(F4/Constants!B1)+(G4*Constants!B4)

Now that is great, but I don't want to type it everytime for 50 rows
like F5/Constants!B1 F6/Constants!B1 So I grab the corner of the cell
and drag it down all the cells I want to have this function, and Excel
is smart enough to increment F4, F5, F6 etc across all the cells I
want.

Great! But Excel is not smart enough to tell that Constants!B1 is
constant, and should not change, and should not imcrement but stay B1
everytime. I tried to set 2 cells as I wanted, with B1 being the same,
and thought that might work, but it does not, I get the pattern B1 B1
B3 B3 B5 B5...you can take it from there.

So! Excel is not smart enough to know Constants!B1 should not change, I
am not smart enough to tell Excel how to do it, so I am looking for
someone smarter than the both of us put together to help me with my
problem!

To sum up, I would like to auto-complete a function, by dragging the
bottom right corner of a cell that has a function in it, down many
cells(I wish I knew what this was called) I would like some of the cell
designations to icriment(F4, F5, F6) but others to stay the same(B1,
B1, B1).

Any hints, help, or comments would be appriciated.

Use $ characters to indicate which part(s) of a cell reference
you do not want to update. E.g.,

=(F4/Constants!$B$1)+(G4*Constants!B4)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


wrote in message
ups.com...
Hi!

I have a function in the function bar of my excel spreadsheet,
and it
works well, and here is part of it:

=(F4/Constants!B1)+(G4*Constants!B4)

Now that is great, but I don't want to type it everytime for 50
rows
like F5/Constants!B1 F6/Constants!B1 So I grab the corner of
the cell
and drag it down all the cells I want to have this function,
and Excel
is smart enough to increment F4, F5, F6 etc across all the
cells I
want.

Great! But Excel is not smart enough to tell that Constants!B1
is
constant, and should not change, and should not imcrement but
stay B1
everytime. I tried to set 2 cells as I wanted, with B1 being
the same,
and thought that might work, but it does not, I get the pattern
B1 B1
B3 B3 B5 B5...you can take it from there.

So! Excel is not smart enough to know Constants!B1 should not
change, I
am not smart enough to tell Excel how to do it, so I am looking
for
someone smarter than the both of us put together to help me
with my
problem!

To sum up, I would like to auto-complete a function, by
dragging the
bottom right corner of a cell that has a function in it, down
many
cells(I wish I knew what this was called) I would like some of
the cell
designations to icriment(F4, F5, F6) but others to stay the
same(B1,
B1, B1).

Any hints, help, or comments would be appriciated.

Thank you very much. Not only is that exactly what I needed, it was
quicker than I could have hoped for.

I was just about to change 5 values in a function across 55 cells which
is...a lot.

Thanks again.