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

cjordan · Feb 27th, 2002, 02:23 PM

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. · Feb 27th, 2002, 04:26 PM

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 · Feb 28th, 2002, 08:52 AM

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 · Feb 28th, 2002, 09:32 AM

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Feb 27th, 2002, 02:23 PM	#1
cjordan New Member Join Date: Feb 2002 Posts: 6	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 ]

Feb 27th, 2002, 04:26 PM	#2
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	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 ]

Feb 28th, 2002, 08:52 AM	#3
cjordan New Member Join Date: Feb 2002 Posts: 6	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

Feb 28th, 2002, 09:32 AM	#4
cjordan New Member Join Date: Feb 2002 Posts: 6	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.