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


<cjb@goldmancg.com> wrote in message
news:1124402148.131166.17640@f14g2000cwb.googlegroups.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.