Vintage79
Board Regular
- Joined
- May 29, 2007
- Messages
- 187
- Office Version
- 2019
- Platform
- 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:
<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 />
<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.
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 |
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 |
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.