Using INDIRECT Across Worksheets

Vintage79

Board Regular
Joined
May 29, 2007
Messages
187
Office Version
  1. 2019
Platform
  1. Windows
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
ABCDEFGH
1FruitFR001VegVG001MeatMT001Fruit
2Apples£1.20Onions£1.65Pork£3.20Veg
3Oranges£0.95Carrots£1.24Beef£4.80Meat
4Bananas£1.30Leeks£1.00Lamb£5.60
5Pears
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
ABCDEFGH
1TypeNamePriceCodecol letterval. In colName Range
2Veg
3C40
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.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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
Code:
[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,
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,853
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top