Need to stop formuls from automatically changing

  • Thread starter Thread starter ThermalJay
  • Start date Start date
T

ThermalJay

I have a file with mulitiple worksheets. The first worksheet is the summary
and pretty much just rolls up the info from all the other worksheets. My
problem is, everymonth i enter new information into Row 6 / Column C in all
of the supporting worksheets. What i want to happen is, the summary worksheet
to grab the infomation from this cell and disregard the others. Whenever I
insert a column into the supporting worksheets each month the formula on the
summary page automatically cahnges. How do i stop it from changing? Here is
the formula I am currently using

=Electric!$C$7

When I had a new row it will automatically change to

=Electric!$C$8


Please help..
 
You could use the OFFSET function.

=OFFSET('Electric'!A1,6,2)

This will find the value in the cell that is 6 rows down and 2 columns right
of cell A1 on the "Electric" worksheet. As long as you don't insert any rows
above Row 1, the formula shouldn't change.

HTH,
Elkar
 
You always want to point at C7--no matter what's inserted/deleted:

=indirect("electric!c7")
 
Thanks a ton.. both of these worked perfectly.. You both are life savers!!
I get to staring at the screen for so long I just can't think anymore..

I have one more thing that is related to this same spreadsheet.

I need to add an average and sum to the summary page of the information i add
to each of the support pages. Again, when i add in a row, the formula on the
summary page changes. See below..

Before I add a row to a support worksheet-

=AVERAGE(Electric!B8:B51)

After I add a row-

=AVERAGE(Electric!B9:B51)

I need this formula to stay the same and not change. I tried to fool around
and use the "Offset" and "indirect" commands but i couldn't get then to work
for this. I am probably just not putting it into the right order.



Dave said:
You always want to point at C7--no matter what's inserted/deleted:

=indirect("electric!c7")
I have a file with mulitiple worksheets. The first worksheet is the summary
and pretty much just rolls up the info from all the other worksheets. My
Click to expand...
[quoted text clipped - 12 lines]
Please help..
Click to expand...
Click to expand...
 
=AVERAGE(indirect("Electric!B8:B51"))

maybe???
Thanks a ton.. both of these worked perfectly.. You both are life savers!!
I get to staring at the screen for so long I just can't think anymore..

I have one more thing that is related to this same spreadsheet.

I need to add an average and sum to the summary page of the information i add
to each of the support pages. Again, when i add in a row, the formula on the
summary page changes. See below..

Before I add a row to a support worksheet-

=AVERAGE(Electric!B8:B51)

After I add a row-

=AVERAGE(Electric!B9:B51)

I need this formula to stay the same and not change. I tried to fool around
and use the "Offset" and "indirect" commands but i couldn't get then to work
for this. I am probably just not putting it into the right order.

Dave said:
You always want to point at C7--no matter what's inserted/deleted:

=indirect("electric!c7")
I have a file with mulitiple worksheets. The first worksheet is the summary
and pretty much just rolls up the info from all the other worksheets. My
Click to expand...
[quoted text clipped - 12 lines]
Please help..
Click to expand...
Click to expand...
Click to expand...
 
Back
Top