Vlookup from different month worksheets

[makavity]

Hi All.

I'm working on a spreadsheet where I have 13 worksheets. One master, and 12 monthly spreadsheets.

I'm trying to use VLOOKUP formulas on the master worksheet, and use the current month to indicate which worksheet the vlookup info should come from.

I have the current month indicated, followed by the next 11 months, but always want the current month first - followed by next month, etc.

Is there a way using the VLOOKUP formula to change the workbook? Ex. =VLOOKUP(A5,April!A:B,2,0) ---- On day 1 of May, I want the formula to automatically pull from =VLOOKUP(A5,May!A:B,2,0)

Can only have 7 If's, so 5 short there. Any suggestions are appreciated.

JR

 

[SkipVought]

JR,

BIG mistake to chop SIMILAR data up into different locations!

All your data need to be in ONE TABLE with DATE as one of the columns. Excel has a wealth of features to analyze and report data from a table. What you have is a NIGHTMARE! But it is typical of users who do not understand database concepts.

I'd put your effort into CONSOLIDATING your data. You will avoid a myriad of pitfalls and headaches in the future!



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Crystalyzer]

Skip,

Generally you are [blue]NICE[/blue] and [blue]HELPFUL[/blue]. Your response to this post was [blue]NEITHER[/blue]. Perhaps the data in question is Monthly P&L or Balance sheets that [blue]CANNOT[/blue] be combined into [blue]ONE[/blue] spreadsheet. What you suggested isn't a possible solution. But that is typical of [red]programmers[/red] who do not understand accounting concepts.

I'd drop the [blue]SNOTTY[/blue] attitude if I were you.

 

[SkipVought]

Crystalyzer,

I've been around the block a time or two, and have observed spreadsheet usage not only in large companies, (specifically two commercial aircraft manufacturing companies and a DOD missiles contractor) but also in several small companies (a specialty steel manufacturer, a commercial plumbing contractor, a residential fence installer, a commercial property management company to name a few) and I have found precious few instances where bad spreadsheet design and usage was a result of some external data over which the user had no control. If a person is getting a P & L or Balance sheet as a data source, then I would STILL insist that there is a systemic problem in such a separtment/company.

If I were beating a nail with screwdriver out of ignorance, I'd sure appreciate it if someone pointed out a better way with a hammer. Unfortunately, in some cases, the only tool available IS a screwdriver -- in that case, we do the best that we can under adverse circumstances.



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[xlbo]

Being as this is a free forum and no charge is made for advice given, I'd suggest that Skip can take any tone he wants. Apart from the fact that what he has stated is nothing but correct (even for P&L sheets - you can still date stamp 'em), this has nothing to do with "accounting concepts" and everything to do with data collation, storage and presentation techniques.

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[KenWright]

I like it all in a single worksheet as well, as i can then shove it in a Pivot table. That having been said, I do also from time to time use a multi sheet approach if the data itself isn't something that I will need to throw into a Pivot.

If the latter is what you really want to do then take a look at INDIRECT, which should allow you to do what you want. Stick the Month in a cell somewhere, then using INDIRECT and that cell, build yourself a reference to your sheets, that will be dynamic anytime you change the month in that cell. Put that little lot into your VLOOKUP and you are done.

Don't quite see why Skip's note should have caused such a strong reaction, but to be honest, right now, I don't care either Why not you may say? - Well, Anne just posted that she is OK, and right now that is the only thing that is of any interest to me, so everything else pretty much takes a much lower precedence. Those that know Anne will understand, those that don't won't.

Regards
Ken................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[makavity]

I have actually found an answer on the internet... but appreciate all the hubub that it generated.

I'm working on weekly forcasting. The idea is to be able to put in the weeks I'm concerned with, and through VLOOKUPs and other formulas, I'd get my forcast information for this week, but still get all 52 weeks by having it auto update the columns. Each week will have different information based on sales.... and combining to 1 worksheet means placing (and replacing) a number of columns - as we carry some things over a year, and advertise weekly. The idea is to have the current week forcast, by using vlookup to the week on the spreadsheet. 1 calculation in the vlookup can make the spreadsheet more versatile on the front end and require little work thereafter.

The method I found would change this =VLOOKUP(H9,W10!$E$7:$J$10,5,FALSE) to =VLOOKUP(H9,INDIRECT("Sheet"&A2&"!$E$7:$J$10"),5,FALSE).

Now I can type in week, and get forecast, estimates and actuals for that week in a printable format to share with vendors, and have the rest of the weeks update in the correct order. Mostly wanted to see if it could be done before spending a lot of time trying to put it together.

Hope some of you may find this helpful.

JR

 

[KenWright]

So you mean you did exactly as I suggested, and used INDIRECT within a VLOOKUP

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[makavity]

Yep! Found it before i had the opportunity to read your reply, but that's exactly how I'll do it.

Thanks again

JR

 

[KenWright]

LOL - Good stuff. Only caveat with that, and it's likely not a problem anyway, but still worth bearing in mind, is that INDIRECT will not work on a closed workbook, so if the data you are referencing is in an external file there could be problems.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------