INDIRECT - worksheet level

[MartinL]

I am having problems using match and index with INDIRECT and I cant see why.

Cell G1 is populated in VBA when the new weeks sheet is created.

    'retain previous worksheet name for Saturday calcs
        Range("G1") = "''" + WS.Name + "'!"

The reason for not using a direct link to the cells on the previous week is on any week rows are allowed to be inserted. - I started on this foot.

Below shows H8 which does not work and cells G8 & I8 which do but point to the wrong worksheet when the sheet is copied week by week...

Excel Workbook
	G	H	I
1	JS PREP WK (44)'!
2
3
4	SATURDAY
5	Pack Plan	Arrivals	SG1
6		17-Oct
7
8	1	#REF!	3
9
10
11
12
13
JS PREP WK (45)

Martin

 

[RoryA]

Your INDIRECT formula looks wrong to me:
INDIRECT($G$1&$AV$1:AV166)
is trying to use the contents of AV1:Av166 as part of the address string. It would need to be:
INDIRECT($G$1&$"AV$1:AV166")
to use that actual range reference.

 

[MartinL]

Duh! smacked in the face by the dreaded quotes.
- what a plonker

cheers Rory