CODEWITHSUNDEEP
excel
Bearviking
Bearviking

Reputation: 1

Excel: Referencing to another workbook across worksheets

So I've been trying to automate one of my workbooks that summarizes monthly data from multiple other workbooks. To give some context, my workbook is a summary sheet that shows the current totals of various accounts at any given month.

As an example, my "summary" workbook pulls the account balance from another workbook called "vendor payment" which monthly tabs labelled "Nov15", for instance.

I currently use VLOOKUP on the "summary" workbook to pull the total from the "vendor payment" tab with:

   "=VLOOKUP("Total",'vendor payment.xlsx]Nov15'!$A$21:$H$40,4,0)"

I want to simplify the workbook by having the sheet reference a tab within the "vendor payment" worksheet based on a date and year I enter into an input cell within the "summary" workbook.

I currently have it setup so that if I enter in the month and year, it will populate a cell that concatenates the information into the same "Nov15" format to match the tab names in the "vendor payment" workbook. Is there a way for me to use this in a way that allows me to simply change the month to say, Dec and have it become:

  "=VLOOKUP("Total",'vendor payment.xlsx]Dec15'!$A$21:$H$40,4,0)"

I know that's a lot of text and hopefully it makes sense what I'm trying to go for. Thanks for the help!

Upvotes: 0

Views: 105

Answers (1)

BruceWayne
BruceWayne

Reputation: 23283

I think I see what you're asking. You'll need Indirect(). Let's say the cell that will return Nov15 or Dec15 is cell B1 in your worksheet. Use this formula:

"=VLOOKUP("Total",Indirect("'vendor payment.xlsx]"&B1&"'!$A$21:$H$40,4,0)"

Although, check the format of your worksheet reference (vendor payment.xlsx]), is that correct? Is that how it's named?

Edit: A quick example:

enter image description here

So, as I change C1 to Sheet2 or Sheet4, it'll look at the range A1:D4 on that sheet, and return the 4th column's relative value. Just tweak this to match your workbooks and such. A tip for using closed workbooks: Have the workbook open, enter the formula and use the mouse to select the ranges in your external workbook. Then the formula will have just the file name. If you then close the workbook, it'll update itself to include the whole path.

Upvotes: 1

Related Questions