I have a spreadsheet that has two worksheets: A summary worksheet -- which is just summarized data using formula calculations to do my year end report.
While each of the calculations used to get the summary data is slightly different -- they are all some variation of the following:
The highlights are where I'm having problems. I have a second worksheet that has all the data.
My goal was that all I'd have to do to update my report would be to cut the old data out of the second worksheet and paste in the updated data. All the cells, column headers, etc. are the same. I'm just updating entries.
Originally, I had created the formulas to allow for as many as 524 rows of data (i.e. -
When I cut out the old data from the second worksheet and pasted in the new data -- the column references all changed from 524 to 278 (278 was the actual number of rows in the data worksheet. I used 524 to allow me to grow the number of lines in the data worksheet without having to redo the formulas). When I deleted the data worksheet the column references changed in all of my formulas from 524 to 278 (there were only 278 lines of actual data in the older version of the data worksheet). Moreover, when I pasted in the updated data to the worksheet (which had 350 lines of data -- the formulas did not adjust -- meaning they all still refer to only 278 columns -- not 375).
So, here are my questions:
Is there a way to do a search and replace of all the instances of the incorrect formula references -- so that I can update the "278"s to "524"s again?
Many thanks!
While each of the calculations used to get the summary data is slightly different -- they are all some variation of the following:
=COUNTIFS(Data!H3:H278,"=SLO",Data!E3:E278,">=1/1/2014",Data!E3:E278,"<=12/31/2014",Data!A3:A278,"=UK")
The highlights are where I'm having problems. I have a second worksheet that has all the data.
My goal was that all I'd have to do to update my report would be to cut the old data out of the second worksheet and paste in the updated data. All the cells, column headers, etc. are the same. I'm just updating entries.
Originally, I had created the formulas to allow for as many as 524 rows of data (i.e. -
=COUNTIFS(Data!H3:H524,"=SLO",Data!E3:H524,">=1/1/2014",Data!E3:H524,"<=12/31/2014",Data!A3:H524,"=UK")
When I cut out the old data from the second worksheet and pasted in the new data -- the column references all changed from 524 to 278 (278 was the actual number of rows in the data worksheet. I used 524 to allow me to grow the number of lines in the data worksheet without having to redo the formulas). When I deleted the data worksheet the column references changed in all of my formulas from 524 to 278 (there were only 278 lines of actual data in the older version of the data worksheet). Moreover, when I pasted in the updated data to the worksheet (which had 350 lines of data -- the formulas did not adjust -- meaning they all still refer to only 278 columns -- not 375).
So, here are my questions:
Is there a way to do a search and replace of all the instances of the incorrect formula references -- so that I can update the "278"s to "524"s again?
- Is there a way that I can make these formulas absolute, so that in the future the references to the rows will not change when I make changes to the data worksheet?
Many thanks!
