Problem with Indirect function to make more dynamic

[WednesdayC]

Hi Team

Please can someone help me as I have trying for ages to sort this out.

I am trying to reference other worksheets

The simple formula I have is:-
=INDIRECT(L2&"!A1")

However, I want to replace the hard coded 2 from L2 to the columns function i.e. in this case Columns($A:B) as I have a list of worksheets I am trying to reference (in column L). I can't find a way to do this.

Any help would be much appreciated.

Regards
Wednesday

 

[Toadstool]

Hi WednesdayC,

I'm struggling to understand what you are trying to do.

I have Sheet2 through Sheet6 with

="This is Sheet number "&SHEET()

in A1 of each sheet.
I have listed sheet names in L2 through L7 which works, with the expected #REF as there's no Sheet7.

Can you give an example of what you want the results to look like?

WednesdayC.xlsx
	I	J	K	L
1	Retrieved from A1			List
2	This is Sheet number 2			Sheet2
3	This is Sheet number 3			Sheet3
4	This is Sheet number 4			Sheet4
5	This is Sheet number 5			Sheet5
6	This is Sheet number 6			Sheet6
7	#REF!			Sheet7
Sheet1

Cell Formulas
Range	Formula
I2:I7	I2	=INDIRECT(L2&"!A1")

 

[WednesdayC]

HI Toadstool
Thank you for getting back to me.
I am trying to copy the formula across, so that in one column I have =INDIRECT(L2&"!A1") and then the next column becomes INDIRECT(L3&"!A1) etc.
This isn't the actual formula, I need (I am trying to do an Xlookup across several worksheets, but if I can get this working then I should be able to figure out the rest)

So basically, I need L2, L3, L4 as part of the formula, as the worksheet names are all in a vertical list.

I hope this makes sense.

Regards

Wednesday

 

[Toadstool]

You could INDIRECT that part as well:

WednesdayC.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Retrieved from A1											List
2	This is Sheet number 2	This is Sheet number 3	This is Sheet number 4	This is Sheet number 5	This is Sheet number 6	#REF!						Sheet2
3												Sheet3
4												Sheet4
5												Sheet5
6												Sheet6
7												Sheet7
8
Sheet1

Cell Formulas
Range	Formula
A2:F2	A2	=INDIRECT(INDIRECT(ADDRESS(COLUMN()+1,COLUMN($L$2)))&"!A1")

 

[WednesdayC]

Hi Toadstool

Yes!!!!

Thank you so much. I couldn't figure this out at all.

Regards Wednesday

 

[Toadstool]

Hi Toadstool
Yes!!!!
Thank you so much. I couldn't figure this out at all.
Regards Wednesday

You're welcome. Glad to help.