Index Match with Indirect and Named Range

Hi and welcome to the forum.

Seems to work for me.
NamedRange is presumably a sheet tab name.

If so it's probably because you have a space in the tab name.

Try instead

Formula:

Please Login or Register  to view this content.

note the all important ' characters which are vital where the tab name characters are not contiguous and contain one or more spaces.

If this is not the case then please upload the workbook

Hi Richard,

Thanks for your welcome and response!

The NamedRange represents various worksheet names. None of the worksheet names have spaces in them. I created an example workbook to show the formula and I am using. It will not return anything in its current form, but I would just like to see the results in the Evaluate Formula option to show as "Worksheet1!C1:C10";"Worksheet2!C1:C10";"Worksheet3!C1:C10 and so on.

I'm still not clear what results you expect and why your MATCH() is liiking to match A1 on Worksheet1 (which is blank) with B1:B10 on Sheet1.

Would you manually add the results you expect to see and indicate the which are the result cells. It alwasy he;ps of we have real data to work with and know what the actual result required is.

The workbook is just a stripped down version with the necessary components for the formula. I can put together a workbook with actual numbers later on but the full formula has a SUMPRODUCT to combine the results. I wanted to isolate the INDEX/MATCH piece because that is what's giving me an issue right now. Additionally, the C1:C10 and B1:B10 ranges in the formula do not refer to Sheet1 specifically. The purpose of my INDIRECT formula is to combine the values in my named range (WSNames) with the C1:C10 and B1:B10 ranges. When I go to Formulas -> Evaluate Formula and click through step by step, I want to see the

Please Login or Register  to view this content.

piece to be showing up as {"Worksheet1";"Worksheet2";"Worksheet3";"Worksheet4";"Worksheet5"}, because that is what is in the named range WSNames. The next step should then combine each value with the C1:C10 or B1:B10 to get {"Worksheet1!C1:C10";"Worksheet2!C1:C10";"Worksheet3!C1:C10";"Worksheet4!C1:C10";"Worksheet5!C1:C10"}.

If you put the below formula in any cell and click through the Evaluate Formula, you'll see what I mean (sorry I forgot to include it in the previous workbook).

Please Login or Register  to view this content.

Originally Posted by BMV

hikari8 Actualy my language skills more less then Excel skills, sorry. But I'm still confused. Could you prepare example with some manually calculated results.

@BMV

This is not meant as any crticism, merely a helpful comment for your further benefit in the English language.
We'd usually say: "Actually my (English) language skills are fewer than my Excel skills.

We use 'fewer' for countable things and 'less' for stuff that isn't countable, like say water in a jar.

Originally Posted by XLent

You can't do a 3d MATCH, if that's what you're asking... so, to BMVs posts, I think you need to outline your desired output relative to your conditional tests.

I suspect you may be looking to return the first match as opposed to the aggregate of all matches... ?

Conditional 3d calculations are generally inefficient, and Volatile, and UDFs are often preferable in that respect.

Hi XLent, thanks for your input. I think you might be right that I am trying to achieve a 3D calculation. I'm not really familiar with these kinds of formulas so I don't really know what alternatives (UDF) are available. Please see attached workbook where I have the full formula in Worksheet1 cell D2. The result I want is the sum of AD across row 5 and sum of 12345-1234 down column B, for all the worksheets in the named range WSNames.

I'm not sure it's good idea to use this formula but

Please Login or Register  to view this content.

XLent , No, but I can read code . Now I did. Use UDF and change Worksheet2!H9 to any. Not Worksheet2 and not work sheets list.
also for UDF you can check are sheets present or not, now it is error only.

Please Login or Register  to view this content.

- the addition of an ISREF test is of (very) debatable merit as it masks invalid references

ok it could be replace to error handling

Please Login or Register  to view this content.

Good point. Actually I tested with reference to one cell but not single constant. Thanks.

Please Login or Register  to view this content.

Originally Posted by XLent

. . . E2: =SUM3D(WSNames,$B$5:$K$10,$B$3,$B$2) . . .

Your UDF doesn't include Application.Volatile, so it may not recalculate when you believe it would.

I'm annotating this because XLent doesn't want to report back what happens at each of these steps. I'm running Office 365 MSO (16.0.12508.20000) 32-bit under Windows 10 Insider build 19551.

Missed a step: enter XLent's SUM3D UDF code in a general module of the new workbook first. Sorry for that oversight.

Make a workbook with worksheets named results, A, B and C. First, put the formula in the results worksheet as

D1: =SUM3D({"A";"B";"C"},A1:C3,X99,X99)

where results!X99 is blank, so criteria always true. At this point, D1 returns 0, as it should.

In worksheet A,

B2: =results!A1
B3: 1002001
C2: 1001002
C3: 1002002

In worksheet B,

B2: =results!B1
B3: 20020010
C2: 20010020
C3: 20020020

B2: =results!C1
B3: 300200100
C2: 300100200
C3: 300200200

Return to the results worksheet. What does its cell D1 show? Since there should be nothing entered in results!A1:C1, shouldn't it return 963555555? Does it? D1 still returns 0. Press [F9] a few times. What does D1 show then? D1 still returns 0.

Now enter values in results!A1:C1. Does the value shown in results!A1 change? I enter 1 in A1, 2 in B1 and 3 in C1, and D1 returns 963555561, the same result as =SUM('A:C'!B2:C3). It should because values in its A1:C3 argument changed.

The point here is that Excel sees arguments A1:C3 and X99 twice, all of which appear without worksheet qualifiers, so Excel believes that those refer to the worksheet which contains the cell with the formula calling SUM3D. OTOH, Excel has no idea whatsoever that it should really depend on the A1:C3 range in worksheets A, B and C.

Now change the B2 formulas in A, B and C to refer to row 5. Return to the results worksheet. What does cell D1 show? D1 still returns 963555561, which is now incorrect. Now change values in A5:C5. The values in A:C!B2 should change, but does the value in results!D1 change? I enter -1 in A5, -2 in B5 and -3 in C5, and D1 still returns 963555561.

There's a trick to add ad hoc volatility to UDFs in cases like this. Add an optional 5th Variant argument (I prefer to name it kludge). Add nothing else to your code, so that kludge isn't used in it. Then use a formula like

results!D1: =SUM3D({"A";"B";"C"},A1:C3,X99,X99,COUNTA(A:C!A1:C3))

Now anytime anything in A:C!A1:C3 changes, the value of results!D1 changes. Why? Because the otherwise useless COUNTA call in the 5th argument makes the formula depend on the cells in the relevant 3D block.

FTHOI, I saved the workbook I was using and opened it in Excel 2010 under Windows 7, and it has the same cell dependency problems. Maybe this works differently on XLent's machine, but I very much doubt it. Thus the need for thorough testing.

Thank you everyone for all your time and inputs!

-XLent, thanks for writing out a tailored UDF for my specific situation. I tried using it in the example workbook and worked very well. However, I tried copying the same UDF into my larger spreadsheet and it doesn't seem to work well. Due to the size of my spreadsheet, the formulas don't load properly and it takes forever to calculate/save. For more background, I use the sum3d formula across many columns and several worksheets (one row per worksheet, across ~200 columns). Each column is referencing a different horizontal criteria (AA, AB, AC, AD) while each worksheet references a different vertical criteria (12345-1234, 11111-1111, etc). Just speculating, but does the UDF not work as well in a large spreadsheet? Thanks!

-hrlngrv, would you be able to comment on the above? I do believe a UDF would be the most reasonable solution. I currently have a workaround but it utilizes a lot of INDIRECTs so it is also slow.

Originally Posted by hikari8

. . . hrlngrv, would you be able to comment on the above? . . . I currently have a workaround but it utilizes a lot of INDIRECTs so it is also slow.

Try BMV's latest UDF. IMO, the UDF approach needs Application.Volatile OR an additional optional argument which could force needed dependent ranges.

If the data in these worksheets were changing frequently, you may have to learn to tolerate slow recalc. The most reliable way to speed things up in that situation is to consolidate all data into one worksheet, then use nonvolatile functions to process that consolidated single-worksheet data. Bluntly, the LATEST version of Excel is still a DECADE behind Lotus 1-2-3 from the 1990s in terms of 3D functionality. Always best to avoid anything beyond the subfunctions the AGGREGATE function provides for 3D references. That DOESN'T include conditional summing.

Actually, one other approach. If all the worksheets you'd be processing were sequential, i.e., no other worksheets between them you wouldn't want to process, BETTER BY FAR to put 2D functions in each of these worksheets, say beginning in col AAA, so AAA1: =SUMPRODUCT((B1:HZ1=col_crit)*(A2:A500000=row_crit),B2:HZ500000) then insert a new worksheet just before the first of these worksheets and name that new worksheet alpha, insert a new worksheet just after the last of these worksheets and name that new worksheet omega, then calculate the conditional sum across these worksheets as

=SUM(alpha:omega!$AAA$1)

To make that even easier, select the first of these worksheets, then scroll the worksheet tabs to the last of these worksheets, hold down [Shift] and click on the last worksheet's tab. That should group all your worksheets, then enter the cell AAA1 formula above in all of them at once. I partially retract my 1-2-3 comment above: Excel's worksheet grouping is very handy, Sorry I didn't think of it before. Anyway, use 2D approaches WITHOUT volatile function calls where they make sense, then use simple 3D-capable nonvolatile functions to aggregate all the worksheets' results. That's the best way to do this sort of thing in Excel.

IOW, 3D conditional summing using either INDIRECT or UDFs may look cool, but they suck for recalc. Better recalc performance requires doing things piece by piece, using the best approach for each separate piece. This approach will cause far fewer headaches in the long run.

Originally Posted by hikari8

. . . I don't really understand the concept of volatility but I can kinda see where you guys are going with it. . . .

Simple example. In a new workbook, in its 1st worksheet, B2: =C2, and fill B2 down into B3:B9, B10: =SUM(B2:B9). Enter numbers in C2:C9, and they flow into corresponding cells in B2:B9, and the formula in B10 also reflects those changes. Changing cells in C2:C9 triggers recalc in B2:B10. Now enter numbers in A2:A9. No change in B2:B10 because nothing changed in cells to which the formulas in those cells refer. In that sense, the SUM function in B10 is nonvolatile. It recalcs only when cells to which it refers, namely, B2:B9, change. Now change B2's formula to =INDIRECT("C2"). Enter numbers in A2:A9, and while C2 is unchanged, Excel will recalc B2 and so also B10. INDIRECT doesn't know ahead of time the cells on which it depends, so it recalcs whenever ANYTHING triggers recalc.

. . . I did think about using a formula across several worksheets like you mentioned with alpha:omega! - but I thought I would have to use an array formula

Nope. =SUM(alpha:omega!AAA1) isn't an array formula. Simple 3D aggregations (COUNT, COUNTA, SUM, AVERAGE, MIN, MAX, etc) aren't array formulas.

(which tends to slow down workbooks too right?)

Depends on the array formulas. Some, e.g., =SUM(IF(one_condition,IF(another_condition,IF(yet_another_condition,values)))) can be faster than equivalents (in this case SUMIFS) because items in another_condition are only evaluated for corresponding TRUE items in one_condition, and likewise items in yet_another_condition are only evaluated for corresponding TRUE items in one_condition AND another_condition. OTOH, LARGE(...,array) and SMALL(...,array) can be problematic.

As for recalc speed, same conditional sums in all worksheets of interest, then simple SUM of those cells across all worksheets is likely to recalc a lot faster than any formulas using INDIRECT or udfs using Application.Volatile. CLARIFICATION: if the affected cells in the worksheets of interest change, then recalc involving INDIRECT or nonvolatile approaches should take about the same time; however, changes in OTHER CELLS which don't affect the conditional sums of interest won't trigger recalc of SUMIFS on each worksheet of interest. That's where you save recalc time.

Originally Posted by hrlngrv

Your UDF doesn't include Application.Volatile, so it may not recalculate when you believe it would....

I see this conversation has moved on somewhat however, I can only reiterate my earlier point - the attached, for me, operates devoid of the Volatile

e.g. change values on other worksheets (as denoted) and the UDF (Worksheet1!G2) will recalculate.... at least it does on O365

Now, the above is a) odd and b) odd... but certainly the case for me ... I don't have access to earlier versions, presently, so perhaps you could test the same, and revert findings?
(I did do a quick google, at the time, to see if the UDF behaviour had changed in O365 but could find nothing)

Like yourself, (and BMV), I would have expected the need for Application.Volatile given implicit precedents or, as you have suggested, a pseudo explicit precedent - either embedded as preemptive IF, or the UDF itself, to enforce the dirty flag)

I don't disagree with any of your assertions re: 3d conditional calcs being poor performing etc, indeed the same was posted a few pages back, by various people.

XLent.
2013 - Application.Volatile (False) by default
2016 - Application.Volatile (False) by default
2019 - Application.Volatile (False) by default
Excel 365 x64 personal 1912 - Application.Volatile (False) by default

So I tried using a SUMPRODUCT formula and including Worksheet1:Worksheet5, but it gives me #NAME? error. I moved the formulas to Sheet1, where I tested BMV's two UDFs as well as XLent's UDF. I updated the other worksheets to show the extent of how I would like to use the formula. My spreadsheet is several times larger than this example and does not seem to work well with either of the UDFs.

-BMV, I saw your other workbook (must've missed it earlier) but I need to go through the formula and try to apply it to my spreadsheet; but upon reading it - I feel like it might have a long refresh time as well.

Originally Posted by XLent

. . . the attached, for me, operates devoid of the Volatile . . .

No it doesn't.

Still using Excel from Office 365.

Change Worksheet1!B2 to AC. The desired result in E2 and the formula using your UDF in G2 both change to 432. Why did yours change? Because it's a formula in Worksheet1 with a reference to $B$2, so to Worksheet1!B2, so the change in Worksheet1!B2 to AC triggered recalc of the formula in Worksheet1!G2.

Now switch to Worksheet2. It's cell G9 had G5 = Worksheet1!B2 and B9 = Worksheet1!B3, so it should be included in the 3D sum's result. Subtract 1,000,000 from Worksheet2!G9. Now return to Worksheet1. The resired result in E2 now shows -999568. Unfortunately, G2 blissfully continues to show 432. Press [F9] as many times as you like, and G2 will still show 432.

Originally Posted by XLent

. . . Another has kindly tested the prior attachment on 2016 which confirms change in O365 behaviour.

In which case that other has as little understanding of the problem as you seem to.

In short, in O365, it seems UDFs are treated as Volatile functions by default . . .

The only accurate categorizations for this claim are comprehensive misunderstanding or pure BS.

ADDED: Re VBA's Time function, did you stop to consider that it itself might be volatile? Since Excel's worksheet function NOW() is volatile, it'd come as a complete shock that VBA's Time might also be volatile? FWIW, that's another trick for ad hoc volatility. Using your SUM3D as-is, add 0*NOW() to it, and, Voilą, the formula recalcs all the time.

Try this instead.

Please Login or Register  to view this content.

Use them in cell formulas.

A1: =BeatThisDeadHorseToABloodyPulp($E$1)
A2: =BeatThisDeadHorseToAnEvenBloodierPulp($E$1)

These should produce the same result. Do they?

Now change the worksheet's name. Do they still produce the same result? Not on my system.

However, enter the formula =NOW() in E1. A1 and A2 are once again the same. Change the worksheet name again. Are A1 and A2 still the same? This time both formulas depend on a cell containing a formula calling a volatile function. Dunno about your system, but on mine A1 doesn't change.

Change x's argument type to Variant in both UDFs, then change the A1 and A2 formulas' arguments to COUNT($E$1). At this point, changing worksheet names changes both A1 and A2 so they remain the same and return the current worksheet name.

Volatility is not a simple subject, and much as you may want to believe it's the new default, it ain't.