Update range in formula easily

Posted on 2013-02-05
Last Modified: 2013-02-05
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?
Question by:jlcannon
  • 5
  • 4
LVL 23

Expert Comment

ID: 38856512
Why does the 188 need to change to 189?  is the range supposed to offset each time at the top and bottom?

Author Comment

ID: 38856591
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.
LVL 23

Expert Comment

ID: 38856632
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...

Author Comment

ID: 38856658
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.
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

LVL 23

Expert Comment

ID: 38856780
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....

Author Comment

ID: 38856886
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.
LVL 23

Expert Comment

ID: 38856981
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.

Author Comment

ID: 38857063
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.
LVL 23

Accepted Solution

NBVC earned 500 total points
ID: 38857171
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?

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now