thewiseguy
Well-known Member
- Joined
- May 23, 2005
- Messages
- 1,013
- Office Version
- 365
- Platform
- Windows
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?
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])))))))))))))))))))))))))))))))),"") |