Problems with Offset and Index Formula

[Ronanm]

Hi

I am pulling my hair out with the formula below. I am pulling out a range of values for the Period, based on what week is being requested

This works
SUM(OFFSET(INDIRECT("$F$"&VLOOKUP(H1,B:C,2)+1):F$2917,(ROWS(INDIRECT("$F$"&VLOOKUP(H1,B:C,2)+1):INDIRECT("$F$"&VLOOKUP(H1,B:C,2)+1))-1)*4,,4))

but when I want to start the formula from a different sheet this fails!;

SUM(OFFSET(INDIRECT("'Tables'!F"&VLOOKUP(H1,Tables!B:C,2)+1):F$2917,(ROWS(INDIRECT("'Tables'!F"&VLOOKUP(H1,Tables!B:C,2)+1):INDIRECT("'Tables'!F"&VLOOKUP(H1,Tables!B:C,2)+1))-1)*4,,4))

Any help would be great.

Regards



P.S What should I do to post a sample of the worksheet?

 

[pgc01]

Hi

Just looking at the first part of the Offset() , it seems you are trying to mix ranges from different worksheets.

Try:

=SUM(OFFSET(INDIRECT("'Tables'!F"&VLOOKUP(H1,Tables!B:C,2)+1):Tables!F$2917, ...

 

[FDibbins]

Forum does not allow attachments, but maybe post a small sample of what you re working with, and what you want?

Also, on a side note, try to avoid using full-0column references in volatile functions like INDIRECT - rather, use just the range you need

 

[Ronanm]

Hi Thanks PGC01

I was forgetting to add the worksheet name to the second part of the formula (plus a couple of other issues)... There didn't seem much point in showing the full formula, which is now below.

It works, but only if the external workbook is open, otherwise I get the Ref error.. but it works.. thanks


SUM(OFFSET(INDIRECT("'\\NC1FH08\Homes-I$\RCM\Capacity Planning\A4C\[A for C - Reporting & Whiteboard - 2017-18 - NEW!.xlsx]Weekly Performance'!AL$" & VLOOKUP(I1,Lookup!B:C,2)+16):'[A for C - Reporting & Whiteboard - 2017-18 - NEW!.xlsx]Weekly Performance'!AL$200,(ROWS(INDIRECT("'\\NC1FH08\Homes-I$\RCM\Capacity Planning\A4C\[A for C - Reporting & Whiteboard - 2017-18 - NEW!.xlsx]Weekly Performance'!$AL"&VLOOKUP(I1,Lookup!B:C,2)+16):INDIRECT("'\\NC1FH08\Homes-I$\RCM\Capacity Planning\A4C\[A for C - Reporting & Whiteboard - 2017-18 - NEW!.xlsx]Weekly Performance'!$AL$"&VLOOKUP(I1,Lookup!B:C,2)+1))-16)*4,,4))

Ta!

 

[FDibbins]

INDIRECT only works on open workbooks

Again, I would advise against using full-column references, especially when using volatile functions like INDIRECT, it will slow your file down

 

[Ronanm]

INDIRECT only works on open workbooks

Again, I would advise against using full-column references, especially when using volatile functions like INDIRECT, it will slow your file down

Thanks FDibbins. Yes, shame about Indirect needing the file to be open to work and I'll keep the avoidance of "using full-column references" to mind too.

Regards