This can't be that hard - but I can't find the answer!!

[cjordan]

I've got a spreadsheet #1 that refers to another spreadsheet #2.
This is what #1 looks like:
Null Null BCI BFI BCL
AUM
A/R
A/P

I want to fill #1 with the corresponding intersection values from #2 which looks like this
Null Null BFI BME BCL BCI
A/R
Salaries
Benefits
AUM
A/P

So, basically it's a simultaneous lookup from the column and row from Spreadsheet #1 to corresponding intersection from Spreadsheet #2. The position of the relative data from Spreadsheet #2 is not static - it comes from a database, so I can't reference cells statically.

Any thoughts?

Thanks!!

Chris.
This message was edited by cjordan on 2002-02-27 13:31
This message was edited by cjordan on 2002-02-27 13:37
This message was edited by cjordan on 2002-02-27 13:54

 

[Mark W.]

Okay, so Sheet1!B1:D1 contains {"BCI","BFI","BCL"} and Sheet1!A2:A4 contains {"AUM";"A/R";"A/P"}. On Sheet2, B1:E1 contains {"BFI","BME","BCL","BCI"} and A2:A6 contains {"A/R";"Salaries";"Benefits";"AUM";"A/P"}.

1. Select Sheet2!A1:E6, choose the Insert | Name | Create... menu command, check "Top row" and "Left column", and press [ OK ].

2. Back on Sheet1 enter the formula...

=INDIRECT("Sheet2!"&SUBSTITUTE($A2,"/","_")) INDIRECT("Sheet2!"&B$1)

...into cell B2, fill right to D2 and fill down to D4.
This message was edited by Mark W. on 2002-02-27 15:33

 

[cjordan]

Mark,

Thanks - I tried this and I'm getting a #REF! - I've clicked on "=" to edit formula and result says "volatile".

What's the problem?
Thanks for your help.

Chris

 

[cjordan]

Ok - I got it!!

I also had spaces in the header - which I found INDIRECT doesn't like so I added another substitute in the second indirect.

Works great!!

You're the man....

Christina.