Forum Discussion
returning data from a dropdown option
Hi All,
I am looking for some help with a spreadsheet that i am creating.
I want to setup a dropdown option to select the relevant company and return the necessary data.
This is what the data looks like:
| COMPANY 1 | COMPANY 2 | COMPANY 3 | COMPANY 4 | |||||||||
| PRODUCT CODE | CODE | SIZE | PRICE | CODE | SIZE | PRICE | CODE | SIZE | PRICE | CODE | SIZE | PRICE |
| ABC123 | 1 | M | 7 | 1 | L | 7 | 1 | S | 7 | 1 | XSM | 7 |
| DRE234 | 2 | M | 7 | 1 | L | 7 | 1 | S | 7 | 1 | XSM | 8 |
| FRE356 | 3 | M | 7 | 1 | L | 7 | 1 | S | 7 | 1 | XSM | 9 |
| FET533 | 4 | M | 7 | 1 | L | 7 | 1 | S | 7 | 1 | XSM | 10 |
The aim would be for the individual to enter the product code then select the company to return the data from the relevant sections i.e. if ABC123 was entered and company 3 selected then the CODE, SIZE and PRICE would be gathered from that company
| PRODUCT CODE | COMPANY | CODE | SIZE | PRICE |
| ABC123 | COMPANY 3 | 1 | S | 7 |
Not quite sure how to go about it. Any advice would be appreciated.
Thank you
3 Replies
- It's a bit difficult to say, but it looks to me as if your data is not organized in such a way as to make it possible to do what you want to do. "Company 3" is just a heading over another heading (sub-heading?) that is "Price"
Perhaps you could post an actual copy of an actual spreadsheet, so long as there's no proprietary information in it. Basically, though, it looks very much like the data itself needs to be organized differently as a "flat file" database....then what you're seeking to do will be relatively easy. That means, though, that each combination of Company and Product deserves its own row of data.Hi mathetes
I thought that might be the case; designing the data to be flat I just didn't know if it was possible with the current layout.
With that being said do you have any advice on how to covert the data to a 'flat file' with the current format?
Also the data should look like this:
Thank you
For such layout
it could be in D12
=INDEX($C$4:$N$7, MATCH($B12, $B$4:$B$7,0), MATCH($C12,$C$2:$N$2,0)+COLUMN()-COLUMN($D$11) )and drag it to the right and down.
If in such style I'd recommend not not to merge cells but use Center Across Selection
where the text is in most left cell.
