1

Excel (2016). Suppose I have some piece of Excel in AnalysisSheet that analyzes data in some other data sheet. And suppose the following:

  1. There are actually several such data sheets. For example, they might be named MayData, JunData, JulData, and so on.

  2. All data sheets are identical in format; only their data are different. For example, they might be the same financial report run in different months

  3. AnalysisSheet only ever looks at ONE of the data sheets at a time. At any given "point of use", it's analyzing EITHER MayData, OR JunData, OR JulData. etc.

  4. The data sheets may be in the same workbook as AnalysisSheet, or they could be in a single different other workbook, or they could be spread across a number of other workbooks, or a combination of those

  5. I need the analysis to run today, and not cause me to want to throw rocks at my computer (see below where I describe how I currently do this)

QUESTION: In pure Excel (i.e. no VB, and no third-party add-ons), How do I minimize the work needed to allow the user of SheetAnalyze to change which of the data sheets' data is being taken from?


How I Currently Do It

I use INDIRECT() to dynamically construct the references to the required data sheet. So for example, in AnalysisSheet I may have the following:

B1 = "MayData" (i.e. the name of the sheet containing the data)

B2 = "MayReport.xlsx" (i.e. the name of the workbook containing the data sheet; this is left blank if the data sheet is in the same workbook as AnalysisSheet)

B3 = "C23" (for example; i.e. the name of some cell of interest in the data sheet)

And then I'd have something like this in B4:

B4 = CONCATENATE("'", IF(ISBLANK(B2),"",CONCATENATE("[",B2,"]")),B1,"'!",B3)

That last produces, in this example, the string: '[MayReport.xlsx]MayData'!C23

I can then use that in INDIRECT() for the analysis proper. So to look at that specific cell I could have;

B5=INDIRECT(B4) (see note**)

which is immune to changes in the name of the data sheet, or its workbook (or even, for that matter, the name of that cell of interest, although granted in this example I've said that data sheets are all identical in format).

All I have to do to change which data is being looked at is change cells B1 and B2. And if I'm careful about things, I could reduce it to changing only the month name (in this example) and then construct even what's in B1 and B2 fro that.

THE PROBLEM IS that INDIRECT() is volatile! As a result, when the data set is large, and maybe there are lots of similar things going on, performance gets hammered. Worst case, I've found myself having to wait minutes for a single change to a single cell in AnalysisSheet, while Excel goes over every single INDIRECT() and re-runs it. Down in the status area at the bottom right of my sheet, I can see the CPU usage going crazy on all nodes.

I'd appreciate any ideas.


** Of course typically I'd not just go for a single cell. Instead I'd pull one or more entire tables from the target data sheet into a sheet or sheets in the same workbook as AnalysisSheet. From there, everything else can use references to that local version of the data.

0

QUESTION: In pure Excel (i.e. no VB, and no third-party add-ons), How do I minimize the work needed to allow the user of SheetAnalyze to change which of the data sheets' data is being taken from?

That's a matter of perspective. Do you want to reduce your work or do you want to reduce the users work?

  • To reduce your work, simple, just leave whatever you have where it's at. Don't edit it, don't touch it. Doing this requires no work and you can't have less than no work so it is the optimal solution.

  • To reduce the users work, it is hard to simplify beyond editing a cell, but you can do improve upon it and create an easier user experience by using a drop down list.

When you need INDIRECT, you need INDIRECT But there are many times when other options are possible. For example, named ranges can be used with linked workbooks and they can be dynamic with COUNTA. Things really get interesting with you throw CHOOSE and INDEX into the mix. Just be sure to avoid OFFSET because it is volatie.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.