Excel Formula to switch sheets

[Finalfight40]

Hi Everyone

I currently created the formula below which pulls back information from another sheet. However currently it only looks at 1 sheet which would be '28th-29thApr'!$1:$1048576. I was wondering if it was possible to amend this formula so that when i drag this formula to the right, it would select the next sheet rather than the same sheet?

Any help here would be appreciated and thank you in advance.

=IFERROR(IF(VLOOKUP($A3,[B]'28th-29thApr'!$1:$1048576[/B],MATCH(H$2,[B]'28th-29thApr'!$2:$2[/B],0),FALSE)="X",1,(VLOOKUP($A3,[B]'28th-29thApr'!$1:$1048576[/B],MATCH(H$2,[B]'28th-29thApr'!$2:$2[/B],0),FALSE))), 0)

 

[Special-K99]

You could create a table of sheet names and use INDIRECT(INDEX()) in your formula to get the name of the sheet, but you'd need to update the table if you added more sheet.

Sheet 1
A1 '28th-29thApr'
A2 '30thApr'
A3 '1st-2ndMay'

Youre INDEX would have to be based on COLUMNS() but I cant get it to work at the moment

 

[Finalfight40]

You could create a table of sheet names and use INDIRECT(INDEX()) in your formula to get the name of the sheet, but you'd need to update the table if you added more sheet.

Sheet 1
A1 '28th-29thApr'
A2 '30thApr'
A3 '1st-2ndMay'

Youre INDEX would have to be based on COLUMNS() but I cant get it to work at the moment

Hi Special-K99

Sorry for the late reply but it was bank holiday over here.

I thought so. I've decided to go for the indirect approach and found a some VBA code which will pull through all sheet names so im going to see if i can use this in a macro.

Thanks for taking the time to answer though.