Jun 04 2021 04:07 AM
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
Jun 04 2021 04:42 AM
Jun 04 2021 05:36 AM
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
Jun 04 2021 05:54 AM
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.