+ Reply to Thread
Results 1 to 17 of 17

Vlookup across sheets, nested Vlookup possibly?

  1. #1
    Registered User
    Join Date
    06-09-2009
    Location
    Levittown, PA
    MS-Off Ver
    Excel 2003
    Posts
    16

    Vlookup across sheets, nested Vlookup possibly?

    Iím trying to develop a workbook which holds monthly data on loan information. It tracks the interest and balance on the loan. I want the first page to have a table displaying the interest payments for every individual tab. When I was brainstorming the idea, I was considering a sort of Vlookup function to find the tab the account is on and then a further function, possibly another vlookup which connects the month to that monthís interest payment. Can anyone help me figure this out?



    The attached spreadsheet is obviously simplified, there are well over 30 tabs. But I would like it to, ideally, search the account number column, search the workbook for that account number, and then when on that page use the month at the top of the first page and retrieve the interest payment and put it back in the cell. Itíd also be great if the formula can be transferred between workbooks. Iím not sure if that makes sense; basically if I were to copy that worksheet into the next months book, I would like that the formula read those tabs instead of becoming obsolete due to references from the first workbook.



    I really hope that this is not to confusing, and any help will be very appreciated!
    Attached Files Attached Files
    Last edited by paid2mkgrlspanic; 06-09-2009 at 05:08 PM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Vlookup across sheets, nested Vlookup possibly?

    Try:

    =VLOOKUP($B$1,INDIRECT("'"&A4&"'!B:D"),2,0)

    copied down.

    If you then copy the sheet (by right-clicking May Sheet tab and choosing "Move or Copy..." and check "Create a Copy" and insert it before sheet 123... then just rename the sheet and change the Month at the top... for new month's results, etc.
    Last edited by NBVC; 06-09-2009 at 02:23 PM.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    06-09-2009
    Location
    Levittown, PA
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: Vlookup across sheets, nested Vlookup possibly?

    Could you possibly explain what this is doing. I get the vlookup, I'm just confused as to how the function is grasping results on the page, and I need to edit it to read account strings longer than just the three numbers, but otherwise, the function itself works great on the simple version. Thanks so much!

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Vlookup across sheets, nested Vlookup possibly?

    It's using INDIRECT() function to indirectly refer to the sheetname that matches the name in cell A4. The "'" is used in case your sheetnames have special characters or spaces in it.. the B:D is the table range to lookup and it concatenates those symbols that are specific to sheetnames with the name of the sheet using the & symbol.. to form an actual range reference in that other sheet.

    INDIRECT("'"&A4&"'!B:D")

    So if you have a specific suffix on each sheet, then you could add that to the function without having to alter what's in A4

    For example if your sheetname is actually '123_Sheet' and you have '123 in A4, then formula would be

    =VLOOKUP($B$1,INDIRECT("'"&A4&"_Sheet'!B:D"),2,0)

    Hope that helps...

    ...also look at Indirect in the Excel help files

    and here: http://www.contextures.com/xlFunctions05.html#RefSheet
    Last edited by NBVC; 06-09-2009 at 03:03 PM.

  5. #5
    Registered User
    Join Date
    06-09-2009
    Location
    Levittown, PA
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: Vlookup across sheets, nested Vlookup possibly?

    Oh, so its looking for sheetnames instead of cells on those respective sheets then???

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Vlookup across sheets, nested Vlookup possibly?

    Well, no, the Vlookup is looking at the cell names, the Indirect is simply finding the range in the appropriate sheet...

    Without the Indirect, the formula in B4 would have been:

    =VLOOKUP($B$1,'123'!B:D,2,0)

    Which looks for the item that matches B1 within the column B of sheet named '123' and extracts item in corresponding column C...

    the Indirect simply saves you from hard-coding the sheetname into the formula..by indirectly referring to the sheetname matching item in A4....

    Hopefully I am not confusing you more...

  7. #7
    Registered User
    Join Date
    06-09-2009
    Location
    Levittown, PA
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: Vlookup across sheets, nested Vlookup possibly?

    I figured it out. Thanks! If I could bug you for one more thing:

    Some of the account sheets have a range of interest cells. Like for may theres 4 different cells that would normally be summed. I know previously, a Sumif was used. Is there a way to incorporate that into this formula so that it would return the sum of it. Each cell is listed as May-09 and an interest, for four rows. I guess a sumif would work normally too cause if theres only one line with May-09 it would only sum the one. I hope this does not go back on what you previously did.

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Vlookup across sheets, nested Vlookup possibly?

    Something like this:

    =SUMIF(INDIRECT("'"&A4&"'!B:B"),$B$1,INDIRECT("'"&A4&"'!C:C"))

  9. #9
    Registered User
    Join Date
    06-09-2009
    Location
    Levittown, PA
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: Vlookup across sheets, nested Vlookup possibly?

    That will do it, thanks so much! I know where to go next time I need help

  10. #10
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Vlookup across sheets, nested Vlookup possibly?

    Please remember to mark your thread as Solved.

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save

  11. #11
    Registered User
    Join Date
    06-09-2009
    Location
    Levittown, PA
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: Vlookup across sheets, nested Vlookup possibly?

    Hit a snag! God, I feel like such a bother. There are a couple accounts that have multiple tabs. Inside the tabs is a different sub account. Using my example, it would be like accounts 123-500 and 123-600 but they both are filed under 123 or some are filed like 123-456 for intercompany loans. Is there a way to get it to decipher the accounts so that the one function returns the number from 123-500 and the next formula returns the number from 23
    -600? Sorry for all of the questions

  12. #12
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Vlookup across sheets, nested Vlookup possibly?

    Can you re-attach the sheet with your new information.. I am finding it hard to follow...

  13. #13
    Registered User
    Join Date
    06-09-2009
    Location
    Levittown, PA
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: Vlookup across sheets, nested Vlookup possibly?

    Notice how company 123 has a loan, and then a loan between them and 456 and company 456 has two different loans. If the tab names are the same, or similar, like 123 (2), is there a way to differentiate the tabs without renaming all of the tabs. One account in my actual workbook holds 12 loan accounts
    Attached Files Attached Files

  14. #14
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Vlookup across sheets, nested Vlookup possibly?

    Well that's not a little snag

    I wondering if a VBA solution that cycles through your sheets would not be easier...

    I am looking for a way using formulas... but if someone has a VBA solution.. they can offer it...

  15. #15
    Registered User
    Join Date
    06-09-2009
    Location
    Levittown, PA
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: Vlookup across sheets, nested Vlookup possibly?

    I appreciate it, I might just give in and succumb to changing the tab names and then keeping a table of tab names next to the accounts on the main page... It might make things easier

  16. #16
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Vlookup across sheets, nested Vlookup possibly?

    Definitely it would...

  17. #17
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Vlookup across sheets, nested Vlookup possibly?

    If you download and install a free addin called morefunc from here:

    http://download.cnet.com/Morefunc/30...-10423159.html

    then you can use a formula as per attached:

    Formula in C4:

    Please Login or Register  to view this content.
    Which assumes your data in each sheet is consistently placed in the same ranges.....

    Note: Attachment will give #NAME error if you don't install the addin.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1