This can't be that hard - but I can't find the answer!!
Find bottlenecks in your Excel workbooks
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

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

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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 ]

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    New Member
    Join Date
    Feb 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com