1. ## index/match simple question

i mean, in sheet1 i have this:

in sheet2:

in sheet3:

my question is:

how can i use index/match across multiple sheets (even when new sheet is created, possible?) at sheet1 A1 (i have thousand lines in B column in reality)?

thank you all

2. ## Re: index/match simple question

what formula are you using now? most formulas will stay across multiple sheets if additional sheets are added inbetween so you could make the last sheet not count IE nil return or zeros then insert new sheets just in front of that one

3. ## Re: index/match simple question

Originally Posted by Dryver14
what formula are you using now? most formulas will stay across multiple sheets if additional sheets are added inbetween so you could make the last sheet not count IE nil return or zeros then insert new sheets just in front of that one
im using index/match now, but i have to put name of sheet into the formula, im thinking of indirect, but i dont know how

5. ## Re: index/match simple question

I do not know a way to use indirect with this function, how many sheets are we talking for a few you could do something like this

iferror(INDEX(Sheet2!B:B,MATCH(A2,Sheet2!A:A,0)),INDEX(Sheet3!B:B,MATCH(A2,Sheet3!A:A,0)))

Other than that I can offer no more, sorry, sometimes saying simple question doth not a simple answer return me thinks

6. ## Re: index/match simple question

Originally Posted by Dryver14
I do not know a way to use indirect with this function, how many sheets are we talking for a few you could do something like this

iferror(INDEX(Sheet2!B:B,MATCH(A2,Sheet2!A:A,0)),INDEX(Sheet3!B:B,MATCH(A2,Sheet3!A:A,0)))

Other than that I can offer no more, sorry, sometimes saying simple question doth not a simple answer return me thinks
i have somes but this way when new sheet is created, i will have to update the formula again

or any other formula do you think its possible?

thanks anyway tho

7. ## Re: index/match simple question

I dont think there is any formula that knows a new sheet has been added and to update itself.

There may be a vba solution but I would not know how to do it sorry.

Good luck

8. ## Re: index/match simple question

Originally Posted by Dryver14
I dont think there is any formula that knows a new sheet has been added and to update itself.

There may be a vba solution but I would not know how to do it sorry.

Good luck
thank you

btw, can you please take a look at this: https://www.mrexcel.com/forum/excel-...stion-3-a.html