Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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.. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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.. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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.. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I stop automatically copying an entry to the next column? | Excel Discussion (Misc queries) | |||
stop automatically changing fomula ... | Excel Worksheet Functions | |||
How do I stop excel automatically changing my date to 2005? | Excel Discussion (Misc queries) | |||
stop automatically making email address | Excel Discussion (Misc queries) | |||
How do i stop the colors changing on my pivot chart when i refres. | Excel Discussion (Misc queries) |