SOLVED
Home

Auto fill from consecutive drop down boxes

%3CLINGO-SUB%20id%3D%22lingo-sub-840181%22%20slang%3D%22en-US%22%3EAuto%20fill%20from%20consecutive%20drop%20down%20boxes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-840181%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20work%20in%20the%20building%20industry%20and%20I'm%20trying%20to%20set%20up%20a%20spreadsheet%20to%20help%20my%20team%20work%20out%20measurements%20based%20on%20some%20drop%20down%20boxes%2C%20which%20pull%20data%20from%20the%20next%20sheet.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESheet%201%20has%20my%20table%2C%20column%20B%20has%20a%20drop%20down%20box%20with%20the%20suite%20selection%2C%20column%20C%20has%20another%20drop%20down%20box%20with%20a%20type%2C%20depending%20on%20the%20two%20of%20these%20the%20data%20varies.%20I%20have%20all%20the%20corresponding%20data%20in%20Sheet%202%20for%20my%20drop%20down%20boxes%20and%20measurements%20to%20pull%20through%20but%20I%20cannot%20figure%20out%20how%20to%20pull%20those%20measurements%20through.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20my%20spreadsheet%20so%20far%20but%20hoping%20there%20is%20a%20way%20to%20do%20this%2C%20I%20have%20been%20googling%20for%20days%20and%20don't%20think%20either%20VLOOKUP%20or%20HLOOKUP%20is%20the%20way%20to%20go.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%2C%20I%20look%20forward%20to%20learning%20more!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-840181%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-840204%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20fill%20from%20consecutive%20drop%20down%20boxes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-840204%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F402951%22%20target%3D%22_blank%22%3E%40Emosh1987%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENot%20sure%20what%20do%20you%20want%20to%20pull%20from%20Sheet2%20into%20the%20Sheet1%20as%20you%20didn't%20mock%20up%20the%20desired%20output%20but%20try%20the%20below%20formula%20which%20will%20pull%20the%20value%20from%20column%20D%20on%20Sheet2%20into%20the%20Sheet1.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIFERROR(OFFSET(INDEX(Sheet2!%24C%3A%24C%2CMATCH(%24B2%2CSheet2!%24C%3A%24C%2C0))%2CMATCH(%24C2%2CSheet2!%24C%3A%24C%2C0)-2%2C%3CSTRONG%3E1%3C%2FSTRONG%3E)%2C%22%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%201%20(bold%20number)%20in%20the%20above%20formula%20will%20pull%20the%20value%20from%201%20column%20right%20to%20the%20column%20C%20i.e.%20column%20D%20on%20Sheet2%20so%20if%20you%20want%20to%20pull%20the%20info%20from%20column%20E%2C%20change%20it%20to%202.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-840209%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20fill%20from%20consecutive%20drop%20down%20boxes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-840209%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20so%20much!!!!!!%20Definitely%20beyond%20my%20knowledge%20of%20formulas%20but%20that%20is%20doing%20exactly%20what%20I%20needed.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20added%20it%20into%20the%20D%20and%20E%20columns%20(with%201%20changed%20to%202%20for%20E).%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20I%20ask%20for%20a%20simpler%20one%20for%20the%20Fixing%20tags%20columns%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHave%20reattached%20with%20the%20Fixing%20tags%20drop%20down%20and%20values%20in%20Sheet2.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAgain%20thank%20you%20very%20much!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-840216%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20fill%20from%20consecutive%20drop%20down%20boxes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-840216%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F402951%22%20target%3D%22_blank%22%3E%40Emosh1987%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20simply%20use%20the%20following%20formula%20in%20G2%20on%20Sheet1...%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIFERROR(INDEX(Sheet2!%24H%243%3A%24H%244%2CMATCH(%24F2%2CFixingtags%2C0))%2C%22%22)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20that%20takes%20care%20of%20your%20question%2C%20please%20don't%20forget%20to%20accept%20my%20post%20which%20resolved%20your%20question%20as%20an%20Answer%20and%20hit%20the%20Like%20button.%20-%3A)%3A)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-862451%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20fill%20from%20consecutive%20drop%20down%20boxes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-862451%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3BI've%20found%20some%20of%20the%20measurements%20aren't%20pulling%20through%20correctly.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOnce%20I%20get%20to%20the%20Metro%2FResidential%20ThermalHeart%2FMetro%20ThermalHEART%20suites%20and%20the%20two%20sliders%20are%20fine%20but%20the%20Open%20Out%20Door%20and%20Window%20frames%20are%20pulling%20through%20incorrect%20information%20or%20just%200.%20Does%20this%20have%20anything%20to%20do%20with%20the%20Residential%20having%20six%20options%20while%20the%20rest%20have%20five%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHave%20reattached%20as%20made%20a%20couple%20of%20minor%20changes.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-862549%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20fill%20from%20consecutive%20drop%20down%20boxes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-862549%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F402951%22%20target%3D%22_blank%22%3E%40Emosh1987%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20try%20this...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20D2%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DINDEX(OFFSET(INDEX(Sheet2!C%3AC%2CMATCH(B2%2CSheet2!C%3AC%2C0))%2C%2C%2C10%2C3)%2CMATCH(C2%2COFFSET(INDEX(Sheet2!C%3AC%2CMATCH(B2%2CSheet2!C%3AC%2C0))%2C%2C%2C10)%2C0)%2C2)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20E2%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DINDEX(OFFSET(INDEX(Sheet2!C%3AC%2CMATCH(B2%2CSheet2!C%3AC%2C0))%2C%2C%2C10%2C3)%2CMATCH(C2%2COFFSET(INDEX(Sheet2!C%3AC%2CMATCH(B2%2CSheet2!C%3AC%2C0))%2C%2C%2C10)%2C0)%2C3)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Emosh1987
New Contributor

Hi,

 

I work in the building industry and I'm trying to set up a spreadsheet to help my team work out measurements based on some drop down boxes, which pull data from the next sheet. 

 

Sheet 1 has my table, column B has a drop down box with the suite selection, column C has another drop down box with a type, depending on the two of these the data varies. I have all the corresponding data in Sheet 2 for my drop down boxes and measurements to pull through but I cannot figure out how to pull those measurements through. 

 

I have attached my spreadsheet so far but hoping there is a way to do this, I have been googling for days and don't think either VLOOKUP or HLOOKUP is the way to go. 

 

Thanks in advance, I look forward to learning more!

5 Replies

@Emosh1987 

Not sure what do you want to pull from Sheet2 into the Sheet1 as you didn't mock up the desired output but try the below formula which will pull the value from column D on Sheet2 into the Sheet1.

 

=IFERROR(OFFSET(INDEX(Sheet2!$C:$C,MATCH($B2,Sheet2!$C:$C,0)),MATCH($C2,Sheet2!$C:$C,0)-2,1),"")

 

The 1 (bold number) in the above formula will pull the value from 1 column right to the column C i.e. column D on Sheet2 so if you want to pull the info from column E, change it to 2.

@Subodh_Tiwari_sktneer 

 

Thank you so much!!!!!! Definitely beyond my knowledge of formulas but that is doing exactly what I needed. 

 

I've added it into the D and E columns (with 1 changed to 2 for E). 

 

Can I ask for a simpler one for the Fixing tags columns? 

 

Have reattached with the Fixing tags drop down and values in Sheet2. 

 

Again thank you very much!!

Solution

@Emosh1987 

You can simply use the following formula in G2 on Sheet1...

=IFERROR(INDEX(Sheet2!$H$3:$H$4,MATCH($F2,Fixingtags,0)),"")

 

If that takes care of your question, please don't forget to accept my post which resolved your question as an Answer and hit the Like button. -:)

 

 

@Subodh_Tiwari_sktneer I've found some of the measurements aren't pulling through correctly. 

 

Once I get to the Metro/Residential ThermalHeart/Metro ThermalHEART suites and the two sliders are fine but the Open Out Door and Window frames are pulling through incorrect information or just 0. Does this have anything to do with the Residential having six options while the rest have five?

 

Have reattached as made a couple of minor changes.

@Emosh1987 

You may try this...

 

In D2

=INDEX(OFFSET(INDEX(Sheet2!C:C,MATCH(B2,Sheet2!C:C,0)),,,10,3),MATCH(C2,OFFSET(INDEX(Sheet2!C:C,MATCH(B2,Sheet2!C:C,0)),,,10),0),2)

 

In E2

=INDEX(OFFSET(INDEX(Sheet2!C:C,MATCH(B2,Sheet2!C:C,0)),,,10,3),MATCH(C2,OFFSET(INDEX(Sheet2!C:C,MATCH(B2,Sheet2!C:C,0)),,,10),0),3)

 

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies