If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Lookup values based on column headers?
I have a data validation dropdown list box in "sheet1" cell $J$1. Depending
on what I have selected in that dropdown list I would like it to match or lookup the data with the respective columns the heading is over in "sheet2". The receiving array is "Sheet1" Y3:AA2284 and the sending array is "Sheet2" CF3:HE2284 Also, the column headers are merged for two or three columns each. I am hoping there might be a way to pull the data from say all columns that the header is over? So to clarify, If I select the word "chair" from the dropdown, and "chair" is over 2 columns CF & CG, then it will show all of those values. Likewise if there were 3 columns the heading was over it would return the values for all three columns. If this requires very much work for you, please know that I very much appreciate it, as this will really help me out. -- Thank you! |
#2
|
|||
|
|||
Lookup values based on column headers?
Assuming your headings are in Sheet2!CF2:HE2, follow this:
in Sheet2:HF2 put "x", this is required as a "back-stop". then in Sheet1Y3, array entered*: =IF((COLUMN()-COLUMN($Y$3)+1)MIN(IF(OFFSET( Sheet2!$CF$2:$HF$2,,MATCH($J$1,Sheet2!$CF$2:$HF$2, 0))"",COLUMN(Sheet2!$CF$2:$HF$2)-CELL("COL", Sheet2!$CF$2:$HF$2)+1)),"",INDEX(Sheet2!$CF$3:$HE$ 22, ROW()-ROW($Y$3)+1,MATCH($J$1,Sheet2!$CF$2:$HF$2,0)+ COLUMN()-COLUMN($Y$3))) Copy Y3 down as far as Y22, then copy Y3:Y22 across as far as required. HTH Steve D. *press Ctrl+Shift+Enter instead of just Enter. "Doug" wrote in message ... I have a data validation dropdown list box in "sheet1" cell $J$1. Depending on what I have selected in that dropdown list I would like it to match or lookup the data with the respective columns the heading is over in "sheet2". The receiving array is "Sheet1" Y3:AA2284 and the sending array is "Sheet2" CF3:HE2284 Also, the column headers are merged for two or three columns each. I am hoping there might be a way to pull the data from say all columns that the header is over? So to clarify, If I select the word "chair" from the dropdown, and "chair" is over 2 columns CF & CG, then it will show all of those values. Likewise if there were 3 columns the heading was over it would return the values for all three columns. If this requires very much work for you, please know that I very much appreciate it, as this will really help me out. -- Thank you! |
#3
|
|||
|
|||
Lookup values based on column headers?
I have given this a try and have not been able to get it to work. Can you
help me determine what it wrong with my entries? Note: I moved some things around prior to your reply but think I changed them properly. Please double check me? The receiving array was "Sheet1" Y3:AA2284 and is now "Economy" JO3:JQ2284 The sending array was "Sheet2" CF3:HE2284 and is now "Data" EN3:JM2284 The Validation list box cell is "Economy" $J$1 Also, I originally said that the headers were over either 2 or 3 columns each, but will this work for a single as well? I just noticed today that I have several that are not merged single header columns? Thank you very much for your efforts! =IF((COLUMN()-COLUMN($JO$3)+1)MIN(IF(OFFSET($EN$2:$JN$2,,MATCH( Economy!$J$1,$EN$2:$JN$2,0))"",COLUMN($EN$2:$JN$ 2)-CELL("COL"$EN$2:$JN$2)+1)),"",INDEX($EN$3:$JM$22,R OW()-ROW($JO$3)+1,MATCH(Economy!$J$1,$EN$2:$JN$2,0)+COL UMN()-COLUMN($JO$3))) -- Thank you! "Steve Dunn" wrote: Assuming your headings are in Sheet2!CF2:HE2, follow this: in Sheet2:HF2 put "x", this is required as a "back-stop". then in Sheet1Y3, array entered*: =IF((COLUMN()-COLUMN($Y$3)+1)MIN(IF(OFFSET( Sheet2!$CF$2:$HF$2,,MATCH($J$1,Sheet2!$CF$2:$HF$2, 0))"",COLUMN(Sheet2!$CF$2:$HF$2)-CELL("COL", Sheet2!$CF$2:$HF$2)+1)),"",INDEX(Sheet2!$CF$3:$HE$ 22, ROW()-ROW($Y$3)+1,MATCH($J$1,Sheet2!$CF$2:$HF$2,0)+ COLUMN()-COLUMN($Y$3))) Copy Y3 down as far as Y22, then copy Y3:Y22 across as far as required. HTH Steve D. *press Ctrl+Shift+Enter instead of just Enter. "Doug" wrote in message ... I have a data validation dropdown list box in "sheet1" cell $J$1. Depending on what I have selected in that dropdown list I would like it to match or lookup the data with the respective columns the heading is over in "sheet2". The receiving array is "Sheet1" Y3:AA2284 and the sending array is "Sheet2" CF3:HE2284 Also, the column headers are merged for two or three columns each. I am hoping there might be a way to pull the data from say all columns that the header is over? So to clarify, If I select the word "chair" from the dropdown, and "chair" is over 2 columns CF & CG, then it will show all of those values. Likewise if there were 3 columns the heading was over it would return the values for all three columns. If this requires very much work for you, please know that I very much appreciate it, as this will really help me out. -- Thank you! |
Thread Tools | |
Display Modes | |
|
|