Update range in formula easily

I have an excel workbook that has 2 worksheets in it. the first one is a summary report the other is the data page that the summary is built from. on the summary report sheet I have hundreds of cells that have formulas in them. each quarter i get the new data for the data sheet since there is maybe more lines per section of less lines I then have to go to the summary report and change each one of the formulas to reflect the correct ranges. all the other parts of the formula stays the same. for example. last quarter the formula in 1 cell looked like this:
=SUMIFS('Business Objects_Data'!P188:P211,'Business Objects_Data'!R188:R211,"PW0",'Business Objects_Data'!F188:F211,1)/E2
and this quarter it looks like:
=SUMIFS('Business Objects_Data'!P189:P212,'Business Objects_Data'!R189:R212,"PW0",'Business Objects_Data'!F189:F212,1)/E2
see the only thing that changed was the data ranges. is there a way to automatically do that?
Who is Participating?
NBVCConnect With a Mentor Commented:
With the way you are setup, there isn't an easy way with a single formula, which wouldn't involve inefficient array formulas.

I would add a helper column in the  Report_data sheet:

starting in E3:


copied down as far as you would need.

Then formula in Summary would be:


you can replace "Cookies" with a cell reference with the word.

You can incorporate that criteria in your SUMIFS() formula to segregate the spefic data.

Does that work for you?
Why does the 188 need to change to 189?  is the range supposed to offset each time at the top and bottom?
jlcannonAuthor Commented:
the range changes because of the number of lines in a particular section. when I pull the data next time that particular section my be from 178-243 depends on the number of records for a particular section.
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

It's hard to imagine for me how/why the references are changing.  Is it possible to show via a sample workbook what you mean?  Maybe there is a conditional check that can be done to determine the range...
jlcannonAuthor Commented:
I run a report that pulls data out of Business object. then I do a save as excel. in this excel there are several worksheets. i then have onother excel workbook that has a summary report sheet and a data sheet. on the summary report sheet is a button linked to a sub routine. the sub routine is as follows:

Sub CopyDataForPivot()
Dim wbThis As Workbook
Dim wbSrc As Workbook
Dim wsDst As Worksheet
Dim wsSrc As Worksheet
Dim rngDst As Range
Dim rngSrc As Range
Dim LastRowDst As Long
Dim strDataFilename As String

    Set wbThis = ThisWorkbook

    Set wsDst = wbThis.Worksheets("Business Objects_Data")

    wsDst.Cells.Range("A100", "AB20000").Clear

    Set rngDst = wsDst.Range("A102")  ' range where data to be copied to

    strDataFilename = "Pwr2012.xls"

    Set wbSrc = Workbooks.Open(ThisWorkbook.Path & Application.PathSeparator & strDataFilename)

    For Each wsSrc In wbSrc.Worksheets

        Set rngSrc = wsSrc.UsedRange

        With rngDst

            .Value = wsSrc.Name

            .Font.Bold = True

        End With

        Set rngDst = rngDst.Offset(1)

        rngSrc.Copy rngDst

        LastRowDst = wsDst.Range("A" & Rows.Count).End(xlUp).Row

        Set rngDst = wsDst.Range("A" & LastRowDst + 3)

    Next wsSrc



End Sub

Open in new window

once i click that button and the sub runs the data on the datasheet is updated with the new data I just got from the business objects report.
the ranges change each time I run the business objects report because there is either less or more data per section each quarter, its not a set number of records that just get updated.
Basically we need to tell the excel formula where the first and last rows of data are.  What logic can be used to determine those is what I am trying to understand?  This is why I am confused more about the start row....
jlcannonAuthor Commented:
ok no matter what the data starts on row 101. starting in row 102 down it puts the data from the business objects report. each worksheet in the business objects report is named something. that name in turn becomes the "Section" in the summary report workbook. so for instance in the business object report data sheet 1 is named Cookies, in the summary report on the "data" worksheet row 102 cell A will be populated with Cookies then lets say there are 50 rows of data in that section so the next like will be the column heading, pulled from the business objects report on the sheet name cookies then the nex row which is row 104 will begin the data and continue until the last record. then after the last record it will ship 2 rows and then begin at row A156 with the name of the next worksheet name for example if it is brownies on row 156 cell A will be Brownies, then belwo the column heading then below that will be the data and lets say there is 13 rows of data. so on and so forth until it goes theough all 20 different worksheets in the business object report. with tha said maybe next time I run it there will only be 30 rows of data in the cookies worksheet so then i would have to adjust the formula to reflect that on the summary report page.
If I understand correctly, if column you add a column which  was populated with the word Cookies all the way down (for the Cookies section), then you could incorporate that criteria into your SUMIFS using whole column references, so range then won't matter as it will be determined by the "Cookies" matchup in the new column.  Does that seem reasonable?

It really would help if you uploaded a workbook sample (removing confidential data).  It only needs to convey the idea of your setup and expected results.
jlcannonAuthor Commented:
ok I know in these examples the formula on the summary report workbook does not do anything but it is there just to she the ranges. so fro example next time i run the report cvookies has 20 entries instead of 14 well then ot only would the formula for cookies change but so would the formulas for each subsequent heading.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.