Last seen: Jun 12, 2026
It sounds to me like a pivot table would be ideal for this. Or Power Query, if you have it.
Try modifying the copy/paste section to this: With FilterRange .AutoFilter Field:=NameCol, Criteria1:=UniqueNames(Index) .SpecialCells(xlCellT...
You could use something like this: =SUMPRODUCT(0+(INDEX(B2:E2,1,N(IF(1,{1,4})))="Y")) which will check the 1st and 4th columns of the ...
You can use: =AVERAGE(IF(C3:V3<>"",(C3:V3-$C$2:$V$2)/$C$2:$V$2,"")) array-entered with Ctrl+Shift+Enter, then copied down.
Interestingly, it seems to be the combination of INDIRECT with ADDRESS. INDIRECT on its own works fine. As it happens, you can avoid both here by u...
In B3: =INDEX('Reference tale sheet'!B:B,4+INT((ROWS($A$1:$A1)-1)/9)) Copy to C3, then you can copy and paste your 9*2 blocks.
INDIRECT expects a text address, not an actual cell reference (which is what it returns). So you can use that to provide the necessary starting cell f...
It needs to be: INDIRECT("Sheet1!"&ADDRESS($J$2,3)) to specify the sheet for the returned range.
Assuming you can't simply apply the filter to the PQ output, here's one method per Chris Webb:
You have to include a sheet name in the INDIRECT part, otherwise there's no context as to what range you actually want.
If they’re files using PQ or data models, perhaps they’ve made improvements to the Vertipaq storage methods. Be interesting to see what Mynda hears fr...
Switching from the old BIFF8 (.xls/.xlt/.xla) format to any of the newer compressed formats (.xlsx/.xlsm/xlsb/.xlst/.xlam) would reduce the file size,...
That's not quite what I suggested. It should be this: IF(EXACT('Data 2019'!$A$1:$A$517,$A$2) not this: IF(EXACT('Data 2019'!$A...
You'd need: IF(EXACT('Data 2019'!$A$1:$A$517,$A$2) instead of: IF('Data 2019'!$A$1:$A$517=$A$2 Ideally, you'd also use...
Why not just filter them out?