Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Looking up Values (Excel 2007)

    I need code to look up the values in three different columns on 7 different workbooks. What I am trying to do is to consolidate some of the items. I am familiar with lookup table, Match & Index function, but do not know how to apply this on several worksheets.

    The columns in one of the workbooks is R, T & AD , but this could differ on the other worksheets, but for the purposes of this worksheet, lets assume that the column are all the same.

    See attached example what the decriptions are which I need to look up the values in the 7 workbooks named BR1, Br2, Br3 etc up up BR7

    Your assistance in this regard will be most appreciated

    regards

    Howard
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Looking up Values (Excel 2007)

    Could you give us an idea what the workbooks BR1 etc. look like, and what the expected results are?

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Re: Looking up Values (Excel 2007)

    Hi Hans

    Thanks for the reply. I need to look up the values of the desriptions under "total Net Turnover" & "TOTAL ADJUSTED GROSS PROFIT" for NEW VEHICLES, USED VEHICLES, SERVICE,PARTS & DRIVEWAY
    in column B in the workbook Sample Consolidated Data" The data is to be obtained from 7 Workbooks B1, Br1, Br3 up to BR7 from column R, T & AC

    The result under "total Net Turnover" for New Vehicles should be -17,310,037


    See Sample workbook Br1

    regards


    Howard
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Looking up Values (Excel 2007)

    Since there are no column headers to indicate what is what, you'll have to write out the entire addition. In the following I've assumed that you want to add columns R, T and AC for br1.xlsx and columns P, S and AB for br2.xlsx. For cell B2:

    =[br1.xlsx]Sheet1!$R2+[br1.xlsx]Sheet1!$T2+[br1.xlsx]Sheet1!$AC2+[br1.xlsx]Sheet1!$P2+[br1.xlsx]Sheet1!$S2+[br1.xlsx]Sheet1!$AB2+...

    This formula can be filled down to B15, then you can clear the cells that don't need to be included (B7 through B10).

  5. #5
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Re: Looking up Values (Excel 2007)

    Hi Hans

    Thanks for the help, which is always appreciated.

    I thought that I may have to use a linking formula, but thought that there may be an easier solve problem

    Regards

    Howard

Posting Permissions

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