Using INDIRECT Across Worksheets

[Vintage79]

Hello,

I've been working through an excellent video on YouTube, but I have come across a problem that I cannot solve. Here is a simplified example from my workbook:

Excel 2010
	A	B	C	D	E	F	G	H
1	Fruit	FR001	Veg	VG001	Meat	MT001		Fruit
2	Apples	£1.20	Onions	£1.65	Pork	£3.20		Veg
3	Oranges	£0.95	Carrots	£1.24	Beef	£4.80		Meat
4	Bananas	£1.30	Leeks	£1.00	Lamb	£5.60
5	Pears
6
Sheet1

<p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />

Excel 2010
	A	B	C	D	E	F	G	H
1	Type	Name	Price	Code		col letter	val. In col	Name Range
2	Veg
3						C	4	0
4
5
Sheet1

<p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet2</p><br /><br />
Formulas on Sheet 2:
F3: =SUBSTITUTE(ADDRESS(1,MATCH(A2,Sheet1!A1:F1,0),4),1,"")
G3: =COUNTIF(INDIRECT("Sheet1!"&F3&":"&F3),"?*")
H3: =INDIRECT(Sheet2!$F$3&"2:"&Sheet2!$F$3&Sheet2!$G$3)

The idea is that the formula in H3 will eventually become the formula I paste into a range name, and then use data validation on cell B2, and only allow this range name to be used. The user can then select Fruit, Veg, or Meat in A2, and be given the appropriate options in B2.

This all works perfectly if I have everything on one sheet, but because I am trying to grab data from Sheet1 to put onto Sheet2, there seems to be an issue. The H3 formula grabs the right range of cells, but it grabs them from Sheet2 and not Sheet1.

I'm hoping there is an easy way to rewrite the H3 formula for this. Please let me know if you can help!

Thank you.

Note: The columns of fruit/veg etc contain blank cells at the bottom, so that items can be added later - this is the reason for a complicated work around.

 

[tdh777]

Hi Vintage,

If you use the "Evaluate Formula" function under the "Formulas" Menu on the Cell in question (H3), you will see how it evaluates the various bits to C & "2:" & C & 4 ie C2:C4.

To make it refer to Sheet1 simply add the Sheet1 reference to the start of the Formula, ie

[B]=[/B]INDIRECT([B]"Sheet1!" &[/B] Sheet2!$F$3&"2:"&Sheet2!$F$3&Sheet2!$G$3)

..which will produce a final result of "Sheet1!C2:C4" which is what you want.

Easy as Pie !

Cheers,

 

[njimack]

The final argument of ADDRESS allows you to specify the sheet you wish to reference.

 

[Vintage79]

Thank you both for the help! You are right - it was actually quite easy in the end. Although it didn't seem that way when I spent all night trying to figure out a solution, nor when I spent the whole weekend on a complicated workaround involving tables and VLOOKUP hahah.

Thanks again for the help - I appreciate it.