index match - using indirect and automating the Columns

the spreadsheet is very large -so example not loaded - if necessary, i can try setting up an example
=INDEX(INDIRECT("'"&$C10&"'!AI:AI"),MATCH(D$8,INDIRECT("'"&$C10&"'!AA:AA"),0))
variations of this is in 52 rows and about 30 columns - so hence the question , as any modification on sheet2 which is referenced by C10 (actually 52 different sheets) reference, is not reflected in the formula columns

this formula or version is used in a large spreadsheet - which looks up values

on the sheet1 (summary)

in column C , I have all the names of the sheets (actually product codes)
in the example sheet2
then in cell D8 , i have a week number to lookup

in the product sheet , i have a weekly entry - so i have the week numbers in a column and results in other columns

so the above formula

will return the result as follows

=INDEX(INDIRECT("'"&$C10&"'!AI:AI"),MATCH(D$8,INDIRECT("'"&$C10&"'!AA:AA"),0))

returns the value in column AI for the sheet name in C10
and then looks up the week number from sheet1 D8 and matches with the column of week numbers on sheet 2 column AA

all works great

NOW the issue

if i insert a column into the sheet2 , everything moves along
so
AI becomes AJ
and
AA becomes AB

BUT because the values in the indirect formula are hardcoded , they do not change and so i get errors

how can i modify the indirect formula , so its relative addressing sheet2 and not hardcoded , therefore any changes to the columns in sheet2 - will be modified on the sheet 1 indirect formula

thanks

well you could replace the hard AA:AA with
=ADDRESS(1,COLUMN(AA1),,1)&":"&ADDRESS(10000,COLUMN(AA1),,1)

thanks for the quick reply

=INDEX(INDIRECT("'"&$C10&"'!AC:AC"),MATCH(D$8,INDIRECT("'"&$C11&"'!"&ADDRESS(1,COLUMN(AA1),,1)&":"&ADDRESS(10000,COLUMN(AA1),,1)),0))

didnt like that in the formula

i assume thats what you meant ?

edit - update
got it working now

so I assume I need the same for AC

=INDEX(INDIRECT("'"&$C10&"'!"&ADDRESS(1,COLUMN(AC1),,1)&":"&ADDRESS(10000,COLUMN(AC1),,1)),MATCH(D$8,INDIRECT("'"&$C11&"'!"&ADDRESS(1,COLUMN(AA1),,1)&":"&ADDRESS(10000,COLUMN(AA1),,1)),0))

but that does not work , when I insert a column or two into column R and so move the AC across
it still picks up the column AC

hi etaf. do you have a header in the other sheets that identify AA is the column you should be looking at? maybe you should do a MATCH formula to find that column.

that would be far easier! even if you havent got a column header at the moment you could add one

thats an idea

yes all the headers are in row 9

and so we have the same title on sheet1 row9
as we do in sheet 2 row 9

so the headers are
target
Processed
delivered
stock
etc

and so in the sheet1 column I want to pull across the target column in sheet2 , from an area after AA
I do also have those titles in sheet2 in columns D,E,F,G etc, where data entry is carried out
so it needs tolook after column AA - or I need to think about hiding the headers somehow - or font colour maybe to change
like for d,e,f,g add a1
target1
delivered1
stock1
maybe


how would I code for the header names

mind you
=INDEX(INDIRECT("'"&$C10&"'!"&ADDRESS(1,COLUMN(AC1),,1)&":"&ADDRESS(10000,COLUMN(AC1),,1)),MATCH(D$8,INDIRECT("'"&$C10&"'!"&ADDRESS(1,COLUMN(AA1),,1)&":"&ADDRESS(10000,COLUMN(AA1),,1)),0))
when i insert say 2 new columns at column R
changes to
=INDEX(INDIRECT("'"&$C10&"'!"&ADDRESS(1,COLUMN(AE1),,1)&":"&ADDRESS(10000,COLUMN(AE1),,1)),MATCH(D$8,INDIRECT("'"&$C10&"'!"&ADDRESS(1,COLUMN(AC1),,1)&":"&ADDRESS(10000,COLUMN(AC1),,1)),0))

that does change if the columns are changed on sheet 1 where the formula is
BUT is the other sheet thats also changing

if i repace C10 with the sheet name - if that helps

The formula is in sheet1 as follows
=INDEX(INDIRECT("'"&$SHEET2&"'!AI:AI"),MATCH(D$8,INDIRECT("'"&$SHEET2&"'!AA:AA"),0))

i know the formula is wrong now - but for illustration

the reference is
sheet2!AI:AI

now if i insert a couple of columns in sheet 2 column R say

it needs to refer to
sheet2!AK:AK

the suggestion does not change

if i change sheet1 the address changes
not if i change sheet2

thanks for helping

yep i see its coz im testing all on one sheet doh!

thanks

I basically would like to delete columns or add columns in any sheet1 or sheet2
and the reference move - like it would if you had simply

=sheet2!A3

ok attempt number ??????
uses index with indirect
looks for the headers in row 9 aa:bb of sheet name in cell c10

didnt seem to work

I have made a sample

summary , i would like to add columns and the code change

shee2 or sheet3
again add or delete columns and the code on summary stay correct

for example in summary be able to delete columns I and J
in Sheet2
add a column to after G

i dont think there is anything about the company in the sheets
can you remove if there is and i will modify

thanks

In pub so wont be sober enough

no urgency, thanks for looking

why are you using
01/01/1900?

i'm not- its probably where i changed things in creating a summary - should probably be 1 or 5 -
but those titles exist in the other sheet and ALL Titles will be TEXT

p8
=IF(D8=1,#REF!,D8-1)

where I have deleted some things
should be
=IF(D8=1,"",D8-1)

well you need some consistency in headers so i have changed a couple but maybe you should consider how this is laid out
but ive added the formula below the curent one you have to show how it works

Thanks, only just picked up this reply, sorry for the delay,
I will remove over the next few days and see how it works,