• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Change the table array for multiple Vlookups

H

housekeys

New Member
Hello all,


Not the most experienced excel user, hoping you can help me out


I'm working on a large report (about 40 tabs, all representing different "stores") that pulls financial data from another workbook with vlookups


Currently, the file it pulls from is 2011 numbers, however we have a file for 2012 numbers and I would like to update it.


Is there a way to change the table array from the 2011 workbook to the 2012 workbook without altering every formula individually?


I can't drag the formula across cells, as there are subtotals and other formulas in between the vlookups. As well, like I said there are 40 tabs that would have to be updated


Probably not the best description, but if you understand what I mean, let me know if you can help me!
 
Hi ,


There was an earlier question , similar to yours I think ; can you check out this here ?


http://chandoo.org/forums/topic/changing-year


If you can go ahead without any further help , fine , otherwise please let us know.


Narayan
 
Thanks for the quick response,


I apologize for not searching through the forum, new member here!


Read through the other post but still have some questions.


Basically what happened for me is the 2011 file started as a blank template, similar to the one created in the other post. The 2011 data was then filled in via vlookups.


Unfortunately, I no longer have access to the original template, only the files that have been filled in, and I don't think I'm going to be able to change all the formula from 2011 to 2012? It would involve going into each formula and changing the table array, would it not? Basically I have about 30 rows of formula x 12 months of columns, and I need to change their source file. I can drag the formula across once I get one month correct, however I cant drag the formula down, as there are subtotal and other formulas in between that dont link to the specified file.


Is my best bet going to be recreating the template, and then filling in with the proper formula? Or is there a way to change the source file for all the vlookups without doing it for each sheet on the workbook.
 
Hi ,


Sorry if I am confused ; I don't think you need to change each and every formula individually.


Can you give me more information about your workbooks ?


1. What is the name of the workbook which has the data for 2011 ?

2. What is the name of the workbook which has the data for 2012 ?

3. What are the tab names in the two workbooks ?

4. Can you give one sample of a formula in your 2011 workbook ?


With this information , it should be possible to make your job easier.


Narayan
 
Hi, housekeys!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


Perhaps you'd want to read the three green sticky posts at this forums main page so as to know the guidelines that will lead to know how this community operates (introducing yourself, posting files, netiquette rules, and so on).


And about your question... It'd help a lot having the file to work on it. Could you upload a sample file? Give a look at the second green sticky post at this forums main page for uploading guidelines.


Regards!
 
Thanks for the help everyone, I've read the sticky's and I can't really post a sample because they are huge files, and even If I take one tab, it would be very hard to anonymize them without destroying the formulas.


That being said, i will try to elaborate. Even if I can't figure this out, please note I do appreciate your time and effort.


I have a large file called "Yearly Budget"

It has 40 tabs, corresponding to 40 different departments


Currently, it pulls data from another workbook called [Financial Statements 2011]


I would like to change this to [Financial Statements 2012]


I don't have the workbook at home, so I can't give the exact vloookup formula, but its something like this:


=If(isna(vlookup(D9,[Financial Statements 2011]$A$2:$C$5,2,false)),””,vlookup(D9,[Financial Statements 2011]$A$2:$C$5,2,false))
 
Hi ,


I just tried the following :


1. I opened one file , which I am using with other data , and renamed it Financial Statements 2011.xlsm


2. In the above file , I created a table in A2:C5 and entered some data in it.


3. I opened another file , and in it , I copied and pasted your formula ; with corrections , it should read :


=IF(ISNA(VLOOKUP(D9,'[Financial Statements 2011.xlsm]Sheet1'!$A$2:$C$5,2,FALSE)),””,VLOOKUP(D9,'[Financial Statements 2011.xlsm]Sheet1'!$A$2:$C$5,2,FALSE))


4. Now , I saved the file Financial Statements 2011 as Financial Statements 2012.


5. The above formula was automatically changed to :


=IF(ISNA(VLOOKUP(D9,'[Financial Statements 2012.xlsm]Sheet1'!$A$2:$C$5,2,FALSE)),””,VLOOKUP(D9,'[Financial Statements 2012.xlsm]Sheet1'!$A$2:$C$5,2,FALSE))


Narayan
 
You must log in or register to reply here.
Back
Top