Vlookup across sheets, nested Vlookup possibly?

[paid2mkgrlspanic]

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!

[NBVC]

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.

[paid2mkgrlspanic]

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!

[NBVC]

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

[paid2mkgrlspanic]

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

[NBVC]

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...

[paid2mkgrlspanic]

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.

[NBVC]

Something like this:

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

[paid2mkgrlspanic]

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

[NBVC]

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

[paid2mkgrlspanic]

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

[NBVC]

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

[paid2mkgrlspanic]

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

[NBVC]

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...

[paid2mkgrlspanic]

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

[NBVC]

Definitely it would...

[NBVC]

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.