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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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...
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.


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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

My experience with Windows 10 over a one year period and suggestions for smooth operation
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

733 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