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.
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

920 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

14 Experts available now in Live!

Get 1:1 Help Now