Make excel worksheet pull data based off of what worksheet i'm in

[jondavis1987]

I have a worksheet that's a cost of materials worksheet. In the workbook are several different worksheets with different products that use different percentages of different materials. Is there a way to make the cost of materials worksheet update automatically to the information pulled from the worksheet i'm in?

For instance the worksheet with costs is just named "Costs". In "Costs" B20:B30 has a list of all material names. C20:C30 has current percentages of materials. D20:D30 has new percentages we are looking at. In worksheet named "S3pv0261800500" C2:H2 has matierals used in this product. C4:H4 has current percentages. C5:H5 has the new percentages.

But worksheet "S4pv0261600100" only has materials in C2:G2 with percentages in C4:G4 and new percentages in C5:G5.

As it is i would either have to create a cost sheet to go with each product, which would be tedious, or i have to manually type it into the "Costs" sheet. Was wondering if there's a better way?

 

[Toadstool]

Hi JonDavis1987,

If you select the sheet to use from a dropdown then you can use INDIRECT to populate the Costs sheet.

JonDavis1987.xlsx
	B	C	D	E	F	G	H
2		Mat 1	Mat 2	Mat 3	Mat 4	Mat 9	Mat 10
3
4	Current % of Materials	4.00%	2.00%	8.00%	24.00%	0.50%	12.00%
5	Possible % of Materials	4.56%	2.28%	9.12%	27.36%	0.57%	13.68%
S3pv0261800500

JonDavis1987.xlsx
	B	C	D	E	F	G
2		Mat 3	Mat 4	Mat 5	Mat 6	Mat 8
3
4	Current % of Materials	12.00%	5.00%	12.00%	36.00%	1.00%
5	Possible % of Materials	15.00%	4.00%	2.00%	52.00%	0.00%
S4pv0261600100

JonDavis1987.xlsx
	B	C	D	E	F
2		Mat 3	Mat 4	Mat 5	Mat 6
3
4	Current % of Materials	1.00%	2.00%	3.00%	4.00%
5	Possible % of Materials	2.00%	3.00%	4.00%	5.00%
S onemore123

JonDavis1987.xlsx
	B	C	D	E	F	G	H	I
19	Material Names	Current % of Materials	Possible % of Materials		Select Sheet			Sheets
20	Mat 1				S4pv0261600100			S3pv0261800500
21	Mat 2							S4pv0261600100
22	Mat 3	12.00%	15.00%					S onemore123
23	Mat 4	5.00%	4.00%
24	Mat 5	12.00%	2.00%
25	Mat 6	36.00%	52.00%
26	Mat 7
27	Mat 8	1.00%	0.00%
28	Mat 9
29	Mat 10
Costs

Cell Formulas
Range	Formula
C20:C29	C20	=IFERROR(INDEX(INDIRECT("'"&$F$20&"'!C4:H4"),MATCH($B20,INDIRECT("'"&$F$20&"'!C2:H2"),0)),"")
D20:D29	D20	=IFERROR(INDEX(INDIRECT("'"&$F$20&"'!C5:H5"),MATCH($B20,INDIRECT("'"&$F$20&"'!C2:H2"),0)),"")

Cells with Data Validation
Cell	Allow	Criteria
F20	List	=$I$20:$I$24

 

[jondavis1987]

Toadstool, you are the greatest! Sorry it took me so long to respond, I just got it plugged in at work.

 

[Toadstool]

You're welcome and thanks for the feedback.