Summary Tab from Inconsistent Data Structure and Location

[jdutle]

Hello!

I am trying to create a summary tab in excel that will look up data in other tabs based on specified criteria, but the structure of those other tabs and location of data is not always consistent. I’ve used a combination of Vlookup, Index, and Match to achieve the desired result, but I was wondering if there was a more streamlined way to do it, or if anyone else had any other ideas. I know it would make the most sense to structure each tab in the same way, but that is not an option in this circumstance.



My current solution finds the location of the Manufacturer name row, matches the brand, and then pulls the price on to the summary tab. I feel like there is a simpler way to identify the row with Manufacturer name. Its currently “MATCH("Manufacturer Name ",INDIRECT("'"&$A7&"'!A:A"),0)&":"&MATCH("Manufacturer Name ",INDIRECT("'"&$A7&"'!A:A"),0)” Its basically the same formula twice to return a row reference of #:#.



I have attached a sample file for your reference. The formula in question is in yellow. Thank you in advance for any ideas or suggestions.

Sample.xlsx
	A	B	C	D	E	F	G	H
4		Financial Summary
5
6	Tab Name
7	Compact Cars	Compact Car Brands	Toyota		Honda		Lexus
8		Annual Premium	$31,800		$34,450		$34,980
9
10	SUV	SUV Brands	Toyota		Honda		Lexus
11		Annual Premium	$47,700		$50,880		$52,470
12
13		Total	Toyota		Honda		Lexus
14		Total Annual Premium	$79,500		$85,330		$87,450
Financial Summary

Cell Formulas
Range	Formula
C8,E8,G8,C11,E11,G11	C8	=VLOOKUP("Price with Tax",INDIRECT("'"&$A7&"'!A6:ZZ100"),MATCH(C7,INDIRECT("'"&$A7&"'!"&MATCH("Manufacturer Name ",INDIRECT("'"&$A7&"'!A:A"),0)&":"&MATCH("Manufacturer Name ",INDIRECT("'"&$A7&"'!A:A"),0)),0),FALSE)
D8,F8,H8,D11,F11,H11	D8	=VLOOKUP("Annual Premium ",INDIRECT("'"&$A7&"'!A6:N70"),MATCH(D7,INDIRECT("'"&$A7&"'!"&MATCH("Carrier Name ",INDIRECT("'"&$A7&"'!A:A"),0)&":"&MATCH("Carrier Name ",INDIRECT("'"&$A7&"'!A:A"),0)),0),FALSE)
C10,C13	C10	=$C$7
E10,E13	E10	=$E$7
G10,G13	G10	=$G$7
C14,E14,G14	C14	=SUM(C8,C11)
D14,F14,H14	D14	=D8+D11+#REF!+#REF!+#REF!+#REF!

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1:DW40	Cell Value	contains "☻"	text	NO

Sample.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3	Compact Cars
4
5
6	Manufacturer Name				Toyota			Honda			Lexus
7	Information Section				"data"	"data"		"data"	"data"		"data"	"data"
8	Information Section				"data"	"data"		"data"	"data"		"data"	"data"
9
10	Car Price				$30,000			$32,500			$33,000
11	Price with Tax				$31,800			$34,450			$34,980
Compact Cars

Cell Formulas
Range	Formula
E11,K11,H11	E11	=E10*1.06

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
P9:DS9,A9:N9,A10:DS38,A1:DS8	Cell Value	contains "☻"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
K7:L8	List	$0,$25,$50,$75,$100
E7:F8	List	$0,$25,$50,$75,$100
H7:I8	List	$0,$25,$50,$75,$100

Sample.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	SUV
2
3
4
5
6
7
8	Manufacturer Name				Toyota			Honda			Lexus
9	Information Section				"data"	"data"		"data"	"data"		"data"	"data"
10	Information Section				"data"	"data"		"data"	"data"		"data"	"data"
11	Information Section				"data"	"data"		"data"	"data"		"data"	"data"
12	Information Section				"data"	"data"		"data"	"data"		"data"	"data"
13	Information Section				"data"	"data"		"data"	"data"		"data"	"data"
14	Information Section				"data"	"data"		"data"	"data"		"data"	"data"
15	Information Section				"data"	"data"		"data"	"data"		"data"	"data"
16	Information Section				"data"	"data"		"data"	"data"		"data"	"data"
17
18	Car Price				$45,000			$48,000			$49,500
19	Price with Tax				$47,700			$50,880			$52,470
20
SUV

Cell Formulas
Range	Formula
E19,K19,H19	E19	=E18*1.06

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A11:DS11	Cell Value	contains "☻"	text	NO
A12:DS13	Cell Value	contains "☻"	text	NO
A14:DS14	Cell Value	contains "☻"	text	NO
P17:DS17,A17:N17,A18:DS46,A1:DS10,A15:DS16	Cell Value	contains "☻"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
H9:I16	List	$0,$25,$50,$75,$100
E9:F16	List	$0,$25,$50,$75,$100
K9:L16	List	$0,$25,$50,$75,$100

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Summary Tab from Inconsistent Data Structure and Location
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.