E
EU
I am stumped at solving the following issue. I'm not even
sure if a VLOOKUP will solve this, as my intuition says it
would be better solved with one of Excel's Database
functions (of which I have ZERO experience with!).
Anyway, this is the challenge I face:
I have three spreadsheets:
On Sheet1 I have unique Cost Center codes in Column A.
Example:
A2
1234
2345
3456
etc...
On Sheet2 I have redundant Cost Center codes in Column A
and unique Locations in Column B.
Example:
A2 B2
1234 London
1234 New York
1234 Munich
2345 Copenhagen
2345 Rome
etc...
Sheet3 will serve as an individual form for the unique
values on Sheet1; that is, it's a template that, with a
macro, will pull the unique code from Sheet1, A2 onto it.
What I need to do next is create a formula on Sheet3, cell
A3, that will use the unique code above it to search for,
and Match to, all identical codes on Sheet2, Column A and
return all the values in Column B that correspond to it.
For example:
Code 1234 from Sheet1 A2 writes onto Sheet3 A2 (from a
macro). THEN the formula in Sheet3 A3, A4, A5 etc. uses
the unique code in Sheet3 A2 to search Sheet2 A2:A300 for
said value and return the corresponding Locations from
Column B.
The trick is that the formulas in Sheet3 A3, A4, A5, etc.
have to "know" the value that was pulled above it to avoid
duplication.
So, to use a final example: Sheet3 A2 will pull code 1234
from Sheet1 A2 (then delete the line with a macro I've
created, so a new code occupies A2 for repeatability),
then the formula in Sheet3 A3 will use the value in A2
above it to search Sheet2 A2:A300 for matches and return
the adjacent Locations from Sheet2 ColumnB. In this case
Sheet3 A3 would return "London"; "New York" would populate
cell A4, and "Munich" would be pulled into cell A5, etc.
Thinking of this one makes my brain ache. I hope someone
out there has a solution for this one, otherwise I'm
looking at a manual cut-and-paste of 2000 spreadsheets!
Thanks. And regrets for the lengthy detail.
EU
sure if a VLOOKUP will solve this, as my intuition says it
would be better solved with one of Excel's Database
functions (of which I have ZERO experience with!).
Anyway, this is the challenge I face:
I have three spreadsheets:
On Sheet1 I have unique Cost Center codes in Column A.
Example:
A2
1234
2345
3456
etc...
On Sheet2 I have redundant Cost Center codes in Column A
and unique Locations in Column B.
Example:
A2 B2
1234 London
1234 New York
1234 Munich
2345 Copenhagen
2345 Rome
etc...
Sheet3 will serve as an individual form for the unique
values on Sheet1; that is, it's a template that, with a
macro, will pull the unique code from Sheet1, A2 onto it.
What I need to do next is create a formula on Sheet3, cell
A3, that will use the unique code above it to search for,
and Match to, all identical codes on Sheet2, Column A and
return all the values in Column B that correspond to it.
For example:
Code 1234 from Sheet1 A2 writes onto Sheet3 A2 (from a
macro). THEN the formula in Sheet3 A3, A4, A5 etc. uses
the unique code in Sheet3 A2 to search Sheet2 A2:A300 for
said value and return the corresponding Locations from
Column B.
The trick is that the formulas in Sheet3 A3, A4, A5, etc.
have to "know" the value that was pulled above it to avoid
duplication.
So, to use a final example: Sheet3 A2 will pull code 1234
from Sheet1 A2 (then delete the line with a macro I've
created, so a new code occupies A2 for repeatability),
then the formula in Sheet3 A3 will use the value in A2
above it to search Sheet2 A2:A300 for matches and return
the adjacent Locations from Sheet2 ColumnB. In this case
Sheet3 A3 would return "London"; "New York" would populate
cell A4, and "Munich" would be pulled into cell A5, etc.
Thinking of this one makes my brain ache. I hope someone
out there has a solution for this one, otherwise I'm
looking at a manual cut-and-paste of 2000 spreadsheets!
Thanks. And regrets for the lengthy detail.
EU