Need to stop formuls from automatically changing
 

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..

Need to stop formuls from automatically changing
 

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

"ThermalJay" wrote:

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..

Need to stop formuls from automatically changing
 

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

=indirect("electric!c7")



ThermalJay wrote:

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..

--

Dave Peterson

Need to stop formuls from automatically changing
 

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 Peterson wrote:
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
[quoted text clipped - 12 lines]

Please help..

Need to stop formuls from automatically changing
 

=AVERAGE(indirect("Electric!B8:B51"))

maybe???

ThermalJay wrote:

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 Peterson wrote:
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
[quoted text clipped - 12 lines]

Please help..


--

Dave Peterson