Home

Populating a list based on another cell

%3CLINGO-SUB%20id%3D%22lingo-sub-789752%22%20slang%3D%22en-US%22%3EPopulating%20a%20list%20based%20on%20another%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-789752%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20been%20having%20a%20lot%20of%20trouble%20trying%20to%20figure%20out%20something%20that%20I%20require%20for%20a%20model%20that%20I%20am%20building.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20model%20is%20for%20calculating%20the%20cost%20of%20renovating%20a%20house%20based%20on%20a%20standardised%20list%20of%20inputs%20that%20I%20have.%20I%20have%20a%20large%20master%20list%20of%20inputs%20that%20will%20be%20constantly%20updated%2C%20shown%20below.%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F125736iB0414B43E6901FC1%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Screenshot%202019-08-06%20at%2010.40.00.png%22%20title%3D%22Screenshot%202019-08-06%20at%2010.40.00.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20also%20have%20another%20sheet%20which%20will%20act%20as%20a%20template%20for%20a%20room.%20I%20want%20to%20be%20able%20to%20type%20the%20room%20in%20cell%20A1%20'Kitchen'%20on%20the%20template%20sheet%2C%20and%20have%20it%20pull%2C%20from%20the%20master%20sheet%2C%20every%20input%20that%20could%20possibly%20go%20into%20renovating%20a%20kitchen.%20Now%20I%20have%20all%20the%20data%20in%20the%20correct%20sheet%20I%20can%20do%20some%20calculations%20to%20work%20out%20total%20cost%20(this%20is%20unimportant%20for%20the%20sake%20of%20this%20question.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20summary%2C%20I%20want%20to%20have%20a%20sheet%20for%20each%20room%20which%20will%20populate%20a%20certain%20section%20of%20a%20list%20based%20on%20whichever%20room%20I%20have%20typed%20in%20cell%20A1.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%20only%20think%20about%20doing%20some%20sort%20of%20long%20IF%20function%20combined%20with%20INDEX(MATCH))%20but%20was%20looking%20for%20some%20sort%20of%20more%20concise%20method.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-789752%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-789805%22%20slang%3D%22en-US%22%3ERe%3A%20Populating%20a%20list%20based%20on%20another%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-789805%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F387878%22%20target%3D%22_blank%22%3E%40CharlieSB%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20have%20the%20Monthly%20Channel%20version%20of%20Excel%20365%20then%20Dynamic%20Arrays%20and%20the%20Filter%20Function%20would%20be%20perfect%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel-Blog%2FExcel-Dynamic-Array-Improvements%2Fba-p%2F332070%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel-Blog%2FExcel-Dynamic-Array-Improvements%2Fba-p%2F332070%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-790591%22%20slang%3D%22en-US%22%3ERe%3A%20Populating%20a%20list%20based%20on%20another%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-790591%22%20slang%3D%22en-US%22%3EI%E2%80%99d%20like%20to%20see%20the%20%E2%80%9Csummary%E2%80%9D%20you%20want%20in%20an%20Excel%20sample%20file.%3C%2FLINGO-BODY%3E
Highlighted
CharlieSB
Occasional Visitor

Hi,

 

I have been having a lot of trouble trying to figure out something that I require for a model that I am building.

 

The model is for calculating the cost of renovating a house based on a standardised list of inputs that I have. I have a large master list of inputs that will be constantly updated, shown below.Screenshot 2019-08-06 at 10.40.00.png

I also have another sheet which will act as a template for a room. I want to be able to type the room in cell A1 'Kitchen' on the template sheet, and have it pull, from the master sheet, every input that could possibly go into renovating a kitchen. Now I have all the data in the correct sheet I can do some calculations to work out total cost (this is unimportant for the sake of this question.

 

In summary, I want to have a sheet for each room which will populate a certain section of a list based on whichever room I have typed in cell A1. 

 

I can only think about doing some sort of long IF function combined with INDEX(MATCH)) but was looking for some sort of more concise method.

2 Replies

@CharlieSB 

 

If you have the Monthly Channel version of Excel 365 then Dynamic Arrays and the Filter Function would be perfect

 

https://techcommunity.microsoft.com/t5/Excel-Blog/Excel-Dynamic-Array-Improvements/ba-p/332070

 

 

I’d like to see the “summary” you want in an Excel sample file.
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies