I am trying to do Vlookups across multiple sheets but cannot make it work. My result is #VALUE!
When I look at the Function Arguments it says the Table Array is Volatile.
My named range "Centers" has 19 tabs listed.
=VLOOKUP(A114,INDIRECT(""&Centers&"!A5:R395"),13,0)
I appreciate any help.
Renleff
Please ensure you mark your thread as Solved once it is. Click here to see how.
If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.
mike,
I appreciate your link to the example. Is there a simpler example that uses a named range. I was not sure why there was a "countif" statement in the formula.
Thanks.
Renleff
The simpel approach would be to aggregate the data to one tab and do a VERY simple vlookup on the aggregated data.
Mike,
It would be nice if that were the case; if it were, I would not need to ask the question.
I appreciate the suggestions and will seek others' help.
Renleff.
Keep in mind I am suggesting it because in excel there are 100 ways to solve a problem and it sometimes helps to have a different set of eyes (and potential solutions). Creating an aggregation tab is something that I often do to keep the formulas very simple on any reporting tab and to also keep things easier to audit compared to arrays.
Is the workbook locked down so you can't add another tab, is the data set larger than 1.04MM rows, or do you just not think it is a solution?
I understand your suggestion and appreciate it. What I am trying to do is aggregate information of all the tabs into one tab that will itself be used to create other reports. Because the file, and tabs, have a completely different function from that which I intend, my "piggybacking" prevents me from changing the file.
In effect, what I am trying to do is create the aggregate tab as you suggest.
Thanks.
Does the file structure change? Do the number of tabs remain constant? Do the number of rows in each tab remain constant? What I am saying is you could do something as simple as a linked formula to each of the tabs. It wouldn't be the most fun to develop ONE time, but then you can always use a vlookup on the entire data set.
Or you could create a simple macro that will copy data from all tabs and append it all to a DATA tab of some sort, if the data row count in the tabs, and number of tabs are changing. I think either would be easier to maintain than the array formula
For example I have a dashboard that houses 4 data sets, but the unique keys remain the same, so I have linked cells to a DATA tab then I can index+Match my way to a very simple dashboard instead of having nested logic and indirects which are a little more difficult to develop and make the file much slower, if that makes any sense.
Certainly linking the cells/tabs to the aggregate tab would be sufficient under the right circumstances. As I have new tabs periodically and new lines added all the time, I am looking for a way to be sure to always capture all the information. This is why I thought using a table with tab names and a sufficiently broad target data range would eliminate my having to look for new information each month.
Ok so then I would probably find/develop a macro to copy and paste all of the data from all of the tabs to an AGGREGATE tab, then use vlookups. That is a fairly simple macro if all of the tabs have identical columns.
http://www.vbaexpress.com/kb/getarticle.php?kb_id=151
http://stackoverflow.com/questions/2...e-single-sheet
http://www.ozgrid.com/forum/showthread.php?t=84220
I am not sure how much VBA experience you have but I don't have much and I am finding it easier to understand than I would have thought. Plus VBA can go on the resume
Is this a duplicate thread:
http://www.excelforum.com/excel-form...a-vlookup.html
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Tony,
Somehow one of your answers was in a closed thread so I copied your questions below.
What type of data is the formula supposed to return? Is it text? Numeric? Could be either? Something else?
What are the sheet names? Are they random names or do they follow some sequential naming pattern?
Will the lookup value always be found on one of the sheets?
The formula will return numeric information.
The sheet names are random names relating to specific process centers.
The lookup value will always be found on one of the sheets.
Thanks.
Renleff
Ok, you'll have to list the sheet names in a range of cells.
A2:A20 = sheet names
B2 = lookup value
Then, this formula:
=SUMPRODUCT(SUMIF(INDIRECT("'"&A2:A20&"'!A5:A395"),B2,INDIRECT("'"&A2:A20&"'!M5:M395")))
In your VLOOKUP formula column M is the 13th column in the table array.
Tony,
Thanks for the formula. I may not get to it today but will certainly give it a try tomorrow. Purely for my edification, is it impractical or not possible to use a named range to reference a2:a20?
Appreciate the help.
Renleff
Sure, you can do that:
=SUMPRODUCT(SUMIF(INDIRECT("'"&Centers&"'!A5:A395"),B2,INDIRECT("'"&Centers&"'!M5:M395")))
Tony,
I decided to change the formula to test it out before using a named range.
=VLOOKUP(A121,INDIRECT("'"&55&"'!M5:M395"),1,0)
A121 is my criteria in the current worksheet
"55" is the target tab name.
M5:M395 is target range to query
"1" is the column offset
This returned a #NA
The following formula returned a #VALUE
=VLOOKUP(A2,INDIRECT("'"&Centers&"'!M5:M395"),0)
Thanks.
Renleff
Tony,
Did a little experimenting. The following formula works...
=VLOOKUP(A118,INDIRECT("'"&55&"'!a5:M395"),13,0)
...but when I substitute in Centers, my Named_Range, I receive a #VALUE
Renleff
You can't use VLOOKUP if Centers refers to more than 1 sheet.
Tony,
I just proved that out right before your response. When I made Centers one sheet, it worked; more than that it returned #VALUE. As you know what I want to do, do you have any formulaic suggestions?
Thanks.
Tom
The suggestion in post #16 will do what you want as far as I understand what you want to do.
The formula is just doing a SUMIF across the sheets. As long as the lookup value will not appear multiple times it should work.
Tony,
Not sure if I understand the conversion of the SumProduct to a VLookup. Comments on my formula below welcome.
=SUMPRODUCT(SUMIF(INDIRECT("'"&Centers&"'!A5:A395"),B2,INDIRECT("'"&Centers&"'!M5:M395")))
=VLOOKUP(A114,INDIRECT("'"&M116:M134&"'!a5:M395"),13,0)
M116:m134 is my list of tabs. If this is an array, do I need to do anything to create the array?
Thanks.
Tom
Like this...
=SUMPRODUCT(SUMIF(INDIRECT("'"&M116:M134&"'!A5:A395"),B2,INDIRECT("'"&M116:M134&"'!M5:M395")))
Tony,
Forgive my seeming intransigence. Do I put a "Vlookup" in the formula at all or will the formula you propose find the like value and return a figure?
Much thanks.
Renleff
Tony,
The following formula works for one tab, M116, but the formula does not like M116:M134
=SUMIF(INDIRECT("'"&M116&"'!A5:A395"),A114,INDIRECT("'"&M116&"'!M5:M395"))
Thanks.
Renleff
You keep changing the formulas that I suggest.
Here's a small sample file that demonstrates this.
Lookup Across Sheets.xlsx
Tony,
You must understand that there is a chasm between the knowledge you possess and that which I have. The formulas that you have provided have not seemed to work for me. I just found my error. I have listed below the formula that does work in the sample file you sent me. Below it is the formula adjusted to use the parameters within my workbook (eliminated sumproduct). If I use only M$116 as my target sheet then I have a valid figure returned. If I use the range M$116:M$143, I receive #REF.
=SUMPRODUCT(SUMIF(INDIRECT("'"&A2:A5&"'!A2:A6"),B2,INDIRECT("'"&A2:A5&"'!B2:B6")))
=SUMIF(INDIRECT("'"&M$116:M$134&"'!A5:A395"),A114,INDIRECT("'"&M$116:M$134&"'!m5:m395"))
A tangential note; if we (you) get the range to work, can I use a Named Range, Centers, instead of the range in the sheet (m116:M134)?
I really appreciate your help and patience.
Renleff
You can't eliminate the SUMPRODUCT function. We need that to get it to work across all the sheets.
Yescan I use a Named Range, Centers, instead of the range in the sheet (m116:M134)?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks