Thread: [Solved] VLookup and Nested IFs or Index Function
View Single Post
 
Old 04-08-2016, 10:06 PM
xor xor is offline Windows 10 Office 2016
Expert
  
Join Date: Oct 2015
Posts: 1,101
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default
I forgot a couple of things.

You should clear the data validation in SavRef, AL7 as this cell contains a simple reference to [Filter.xlsx]Sheet1'!$B$1.

I have inserted helper cells in SavRef, AF12:AF27. You can move these to another place if you want, but don't delete.

The formulas in SavRef AL12:BN21 are array formulas. Please be aware that array formulas require a special way of entering. Normally you just press Enter when finishing a formula. Array formulas must be entered by holding down Ctrl and Shift before pressing Enter. If you do it correct Excel will automatically put braces {} around the formula. Do not try to put these braces manually. When you have entered one array formula correctly you can copy to other cells as you normally do.

If you extend the data range then remember to redefine d in the Name Manager.

Be aware that array formulas are calculation intensive, so if you have really many rows (and columns) of data you may experience a reduced recalculation time.
Reply With Quote