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

Can the array argument of a vlookup refer to a cell that contains the array info

T

taylorreportingsolutions

New Member
I have a summary tab that shows 52 weeks worth of data. The formulas in each of the weekly columns link to that week's file, so 52 columns means the file links to 52 outside files. The formulas in each column are vlookup formulas. Right now, I copy the formula for week one, paste it in each of the remaining weeks and then find and replace the date part of the file path. So I highlight week two and replace all the occurrences of "1.6.12" with "1.13.12" in the file path name. I do this 52 times each time I build a file for a new client. I feel like I should be able to make the array part of each vlookup dynamic so I don't have to manually find and replace to update the date of each week.


So here's my question: Can the array part of a vlookup formula refer to a cell that includes the file path? Let's say I put a formula in cell A2 that generates the text of my file path. Then I want my vlookup formula to read: =VLOOKUP("b",A2,2,FALSE) but this doesn't work. If I paste the contents of A2 into my formula in the spot for the array, it works, but a reference to A2 doesn't work.


Thoughts?
 
Hi, taylorreportingsolutions!


I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).

Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.

Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, well, come back here, tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


Yes, it can. The VLOOKUP syntax is =VLOOKUP(SearchValue,SearchMatrix,ColumnIndicator,Sorted)" so the trick is to provide a SearchMatrix value that references to a range with your data specifications.


The structure of a range reference in other book is: '[book.xlsx]sheet'!range

So you only have to build a text string with this format, embed it into an INDIRECT function "INDIRECT(String)" and use it as argument for VLOOKUP.


And about your workbook structure...


You can fix a top left cell (let's say A1 for the file name), another top left cell for the year (let's say B1), 52 cells in a top row for the week's date (let's say C1:BB1), and playing with those fixed values you should be able to construct only one formula and then copying it once and not 52 times (or 53 always depending on your starting week definition, or up to 54 for leap years).


Regards!
 
Wahoo!!!! I've never used INDIRECT and that's exactly what I needed! I've been searching the Chandoo site for months trying to figure out the right question to ask...I knew if I had the right key words I could find the answer but I didn't know how to phrase it. SirJB7, you're a life saver!!!


Thanks a bunch!
 
Hi, taylorreportingsolutions!


Glad you solved it. Thanks for your feedback and for your kind words too. Welcome back whenever needed or wanted.


But... being here for months and haven't read the green topics... :(

Go there right now! :)


Regards!
 
Hi, taylorreportingsolutions!


INDIRECT function retrieves a range reference (cell, range, worksheet) for the argument passed as string, for example =INDIRECT("A"&1) will retrieve the content of cell A1.

For any Excel function you can access to the built-in function description by selecting a blank cell, typing =FUNCTIONNAME( and then pressing the "f(x)" button at the left of the formula bar and then clicking on "Help about this function".


BTW, nothing to sorry about, it was just an invitation to the 2nd green post, introduce yourself.


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