Is It A Problem to Have Your Table Contain Reference Cells?

[thewiseguy]

Hi all,

I need to be able to take data from other sheets and then sort that output. I'm curious to know, if this will cause me any problems?

v2024.38-2.xlsm
	A	B	C	D	E	F
3	ECM #	Install Location	Lighting Location	Category	LED Measure	Subcategory
4	1	0	0
5	2	0	0
6	3	0	0
Lighting&Controls (JCPLRebates)

Cell Formulas
Range	Formula
A4:B6	A4	=Helper!A4
C4:C6	C4	=Input!D4
D4:D6	D4	=IFERROR((XLOOKUP(Helper!K4,Tbl_Area[MaskedPartNumber],Tbl_Area[JCPL_Category],XLOOKUP(Helper!K4,Tbl_Decorative[MaskedPartNumber],Tbl_Decorative[JCPL_Category],XLOOKUP(Helper!K4,Tbl_Downlight[MaskedPartNumber],Tbl_Downlight[JCPL_Category],XLOOKUP(Helper!K4,Tbl_Exit_and_Emergencies[MaskedPartNumber],Tbl_Exit_and_Emergencies[JCPL_Category],XLOOKUP(Helper!K4,Tbl_Flat_Panel[MaskedPartNumber],Tbl_Flat_Panel[JCPL_Category],XLOOKUP(Helper!K4,Tbl_Flood_Light[MaskedPartNumber],Tbl_Flood_Light[JCPL_Category],XLOOKUP(Helper!K4,Tbl_Garage_and_Canopy[MaskedPartNumber],Tbl_Garage_and_Canopy[JCPL_Category],XLOOKUP(Helper!K4,Tbl_Highbay[MaskedPartNumber],Tbl_Highbay[JCPL_Category],XLOOKUP(Helper!K4,Tbl_Linear[MaskedPartNumber],Tbl_Linear[JCPL_Category],XLOOKUP(Helper!K4,Tbl_Lowbay[MaskedPartNumber],Tbl_Lowbay[JCPL_Category],XLOOKUP(Helper!K4,Tbl_Strip[MaskedPartNumber],Tbl_Strip[JCPL_Category],XLOOKUP(Helper!K4,Tbl_Troffer[MaskedPartNumber],Tbl_Troffer[JCPL_Category],XLOOKUP(Helper!K4,Tbl_Vapor_Tight[MaskedPartNumber],Tbl_Vapor_Tight[JCPL_Category],XLOOKUP(Helper!K4,Tbl_Wall_Mount[MaskedPartNumber],Tbl_Wall_Mount[JCPL_Category],XLOOKUP(Helper!K4,Tbl_Wrap[MaskedPartNumber],Tbl_Wrap[JCPL_Category],XLOOKUP(Helper!K4,Tbl_A_Lamp[MaskedPartNumber],Tbl_A_Lamp[JCPL_Category],XLOOKUP(Helper!K4,Tbl_BR_Lamp[MaskedPartNumber],Tbl_BR_Lamp[JCPL_Category],XLOOKUP(Helper!K4,Tbl_Candle_Lamp[MaskedPartNumber],Tbl_Candle_Lamp[JCPL_Category],XLOOKUP(Helper!K4,Tbl_CFL_Lamp[MaskedPartNumber],Tbl_CFL_Lamp[JCPL_Category],XLOOKUP(Helper!K4,Tbl_Filament_Lamp[MaskedPartNumber],Tbl_Filament_Lamp[JCPL_Category],XLOOKUP(Helper!K4,Tbl_Globe_Lamp[MaskedPartNumber],Tbl_Globe_Lamp[JCPL_Category],XLOOKUP(Helper!K4,Tbl_HID_Lamp[MaskedPartNumber],Tbl_HID_Lamp[JCPL_Category],XLOOKUP(Helper!K4,Tbl_MR_Lamp[MaskedPartNumber],Tbl_MR_Lamp[JCPL_Category],XLOOKUP(Helper!K4,Tbl_Par_Lamp[MaskedPartNumber],Tbl_Par_Lamp[JCPL_Category],XLOOKUP(Helper!K4,Tbl_Linear_Lamp[MaskedPartNumber],Tbl_Linear_Lamp[JCPL_Category],XLOOKUP(Helper!K4,Recessed_Retrofit_Kits!$A$3:$A$14,Recessed_Retrofit_Kits!$U$3:$U$14,XLOOKUP(Helper!K4,#REF!,#REF!,XLOOKUP(Helper!K4,Tbl_Materials[MaskedPartNumber],Tbl_Materials[JCPL_Category],XLOOKUP(Helper!K4,Tbl_Rental[MaskedPartNumber],#REF!,XLOOKUP(Helper!K4,Tbl_Accessory[MaskedPartNumber],Tbl_Accessory[JCPL_Category],XLOOKUP(Helper!K4,Tbl_Controls[MaskedPartNumber],Tbl_Controls[JCPL_Category])))))))))))))))))))))))))))))))),"")
E4:E6	E4	=IFERROR((XLOOKUP(Helper!K4,Tbl_Area[MaskedPartNumber],Tbl_Area[JCPL_Description],XLOOKUP(Helper!K4,Tbl_Decorative[MaskedPartNumber],Tbl_Decorative[JCPL_Description],XLOOKUP(Helper!K4,Tbl_Downlight[MaskedPartNumber],Tbl_Downlight[JCPL_Description],XLOOKUP(Helper!K4,Tbl_Exit_and_Emergencies[MaskedPartNumber],Tbl_Exit_and_Emergencies[JCPL_Description],XLOOKUP(Helper!K4,Tbl_Flat_Panel[MaskedPartNumber],Tbl_Flat_Panel[JCPL_Description],XLOOKUP(Helper!K4,Tbl_Flood_Light[MaskedPartNumber],Tbl_Flood_Light[JCPL_Description],XLOOKUP(Helper!K4,Tbl_Garage_and_Canopy[MaskedPartNumber],Tbl_Garage_and_Canopy[JCPL_Description],XLOOKUP(Helper!K4,Tbl_Highbay[MaskedPartNumber],Tbl_Highbay[JCPL_Description],XLOOKUP(Helper!K4,Tbl_Linear[MaskedPartNumber],Tbl_Linear[JCPL_Description],XLOOKUP(Helper!K4,Tbl_Lowbay[MaskedPartNumber],Tbl_Lowbay[JCPL_Description],XLOOKUP(Helper!K4,Tbl_Strip[MaskedPartNumber],Tbl_Strip[JCPL_Description],XLOOKUP(Helper!K4,Tbl_Troffer[MaskedPartNumber],Tbl_Troffer[JCPL_Description],XLOOKUP(Helper!K4,Tbl_Vapor_Tight[MaskedPartNumber],Tbl_Vapor_Tight[JCPL_Description],XLOOKUP(Helper!K4,Tbl_Wall_Mount[MaskedPartNumber],Tbl_Wall_Mount[JCPL_Description],XLOOKUP(Helper!K4,Tbl_Wrap[MaskedPartNumber],Tbl_Wrap[JCPL_Description],XLOOKUP(Helper!K4,Tbl_A_Lamp[MaskedPartNumber],Tbl_A_Lamp[JCPL_Description],XLOOKUP(Helper!K4,Tbl_BR_Lamp[MaskedPartNumber],Tbl_BR_Lamp[JCPL_Description],XLOOKUP(Helper!K4,Tbl_Candle_Lamp[MaskedPartNumber],Tbl_Candle_Lamp[JCPL_Description],XLOOKUP(Helper!K4,Tbl_CFL_Lamp[MaskedPartNumber],Tbl_CFL_Lamp[JCPL_Description],XLOOKUP(Helper!K4,Tbl_Filament_Lamp[MaskedPartNumber],Tbl_Filament_Lamp[JCPL_Description],XLOOKUP(Helper!K4,Tbl_Globe_Lamp[MaskedPartNumber],Tbl_Globe_Lamp[JCPL_Description],XLOOKUP(Helper!K4,Tbl_HID_Lamp[MaskedPartNumber],Tbl_HID_Lamp[JCPL_Description],XLOOKUP(Helper!K4,Tbl_MR_Lamp[MaskedPartNumber],Tbl_MR_Lamp[JCPL_Description],XLOOKUP(Helper!K4,Tbl_Par_Lamp[MaskedPartNumber],Tbl_Par_Lamp[JCPL_Description],XLOOKUP(Helper!K4,Tbl_Linear_Lamp[MaskedPartNumber],Tbl_Linear_Lamp[JCPL_Description],XLOOKUP(Helper!K4,Recessed_Retrofit_Kits!$A$3:$A$14,Recessed_Retrofit_Kits!$V$3:$V$14,XLOOKUP(Helper!K4,#REF!,#REF!,XLOOKUP(Helper!K4,Tbl_Materials[MaskedPartNumber],Tbl_Materials[JCPL_Description],XLOOKUP(Helper!K4,Tbl_Rental[MaskedPartNumber],#REF!,XLOOKUP(Helper!K4,Tbl_Accessory[MaskedPartNumber],Tbl_Accessory[JCPL_Description],XLOOKUP(Helper!K4,Tbl_Controls[MaskedPartNumber],Tbl_Controls[JCPL_Description])))))))))))))))))))))))))))))))),"")
F4:F6	F4	=IFERROR((XLOOKUP(Helper!K4,Tbl_Area[MaskedPartNumber],Tbl_Area[JCPL_Subcategory],XLOOKUP(Helper!K4,Tbl_Decorative[MaskedPartNumber],Tbl_Decorative[JCPL_Subcategory],XLOOKUP(Helper!K4,Tbl_Downlight[MaskedPartNumber],Tbl_Downlight[JCPL_Subcategory],XLOOKUP(Helper!K4,Tbl_Exit_and_Emergencies[MaskedPartNumber],Tbl_Exit_and_Emergencies[JCPL_Subcategory],XLOOKUP(Helper!K4,Tbl_Flat_Panel[MaskedPartNumber],Tbl_Flat_Panel[JCPL_Subcategory],XLOOKUP(Helper!K4,Tbl_Flood_Light[MaskedPartNumber],Tbl_Flood_Light[JCPL_Subcategory],XLOOKUP(Helper!K4,Tbl_Garage_and_Canopy[MaskedPartNumber],Tbl_Garage_and_Canopy[JCPL_Subcategory],XLOOKUP(Helper!K4,Tbl_Highbay[MaskedPartNumber],Tbl_Highbay[JCPL_Subcategory],XLOOKUP(Helper!K4,Tbl_Linear[MaskedPartNumber],Tbl_Linear[JCPL_Subcategory],XLOOKUP(Helper!K4,Tbl_Lowbay[MaskedPartNumber],Tbl_Lowbay[JCPL_Subcategory],XLOOKUP(Helper!K4,Tbl_Strip[MaskedPartNumber],Tbl_Strip[JCPL_Subcategory],XLOOKUP(Helper!K4,Tbl_Troffer[MaskedPartNumber],Tbl_Troffer[JCPL_Subcategory],XLOOKUP(Helper!K4,Tbl_Vapor_Tight[MaskedPartNumber],Tbl_Vapor_Tight[JCPL_Subcategory],XLOOKUP(Helper!K4,Tbl_Wall_Mount[MaskedPartNumber],Tbl_Wall_Mount[JCPL_Subcategory],XLOOKUP(Helper!K4,Tbl_Wrap[MaskedPartNumber],Tbl_Wrap[JCPL_Subcategory],XLOOKUP(Helper!K4,Tbl_A_Lamp[MaskedPartNumber],Tbl_A_Lamp[JCPL_Subcategory],XLOOKUP(Helper!K4,Tbl_BR_Lamp[MaskedPartNumber],Tbl_BR_Lamp[JCPL_Subcategory],XLOOKUP(Helper!K4,Tbl_Candle_Lamp[MaskedPartNumber],Tbl_Candle_Lamp[JCPL_Subcategory],XLOOKUP(Helper!K4,Tbl_CFL_Lamp[MaskedPartNumber],Tbl_CFL_Lamp[JCPL_Subcategory],XLOOKUP(Helper!K4,Tbl_Filament_Lamp[MaskedPartNumber],Tbl_Filament_Lamp[JCPL_Subcategory],XLOOKUP(Helper!K4,Tbl_Globe_Lamp[MaskedPartNumber],Tbl_Globe_Lamp[JCPL_Subcategory],XLOOKUP(Helper!K4,Tbl_HID_Lamp[MaskedPartNumber],Tbl_HID_Lamp[JCPL_Subcategory],XLOOKUP(Helper!K4,Tbl_MR_Lamp[MaskedPartNumber],Tbl_MR_Lamp[JCPL_Subcategory],XLOOKUP(Helper!K4,Tbl_Par_Lamp[MaskedPartNumber],Tbl_Par_Lamp[JCPL_Subcategory],XLOOKUP(Helper!K4,Tbl_Linear_Lamp[MaskedPartNumber],Tbl_Linear_Lamp[JCPL_Subcategory],XLOOKUP(Helper!K4,Recessed_Retrofit_Kits!$A$3:$A$14,Recessed_Retrofit_Kits!$V$3:$V$14,XLOOKUP(Helper!K4,#REF!,#REF!,XLOOKUP(Helper!K4,Tbl_Materials[MaskedPartNumber],Tbl_Materials[JCPL_Subcategory],XLOOKUP(Helper!K4,Tbl_Rental[MaskedPartNumber],#REF!,XLOOKUP(Helper!K4,Tbl_Accessory[MaskedPartNumber],Tbl_Accessory[JCPL_Subcategory],XLOOKUP(Helper!K4,Tbl_Controls[MaskedPartNumber],Tbl_Controls[JCPL_Subcategory])))))))))))))))))))))))))))))))),"")

 

[Jeffrey Mahoney]

Sorting data brought in from other sheets is not a problem. I'm going to be blunt: Those formulas are insane! I'm really trying to understand why you need 24 to 30 XLookup functions imbedded inside each other to get one result. Surely there has to be a way to create a cross reference.

 

[My Aswer Is This]

Sorting data brought in from other sheets is not a problem. I'm going to be blunt: Those formulas are insane! I'm really trying to understand why you need 24 to 30 XLookup functions imbedded inside each other to get one result. Surely there has to be a way to create a cross reference.

When I see formulas like this it makes my head spin. I like using Vba.

 

[thewiseguy]

Sorting data brought in from other sheets is not a problem. I'm going to be blunt: Those formulas are insane! I'm really trying to understand why you need 24 to 30 XLookup functions imbedded inside each other to get one result. Surely there has to be a way to create a cross reference.

ha! if you have another way, I am all ears. I don't know any VBA so I've tried to keep it all formula based.

 

[Jeffrey Mahoney]

Are you saying you have to lookup parts across 24 different tables to get a result? I can't see your data so it would be impossible for me to offer any kind of solution.

 

[My Aswer Is This]

All you have told us is:
Quote:
"I need to be able to take data from other sheets and then sort that output"

To help you we need a lot more details. And without asking us to read your formulas.