Vlookup Question

[Dreamxcell]

Consolidated Marketing Report May.xlsx

Hi,

I need help with the vlookup function.
I do this report in the attachment each month
On the attachment i need the formulae to automatically change each month on the 'budget v actual' tab to pull the figures from the other two tabs for that month.
Is this hard to do or even possible?

Thanks

[natefarm]

It's possible if you know the tricks! The first thing you would need to do is create named ranges for each of the tables on your Budget and Actual sheets. For example, on Budget, select A8:W24 and name the range BudCorp1, and on Actual, select A8:O26 and name it ActCorp1.

Then on your Budget v Actual May sheet, the fun begins:

First, we're going to add some formulas that determine the month column based on the tab name. Assuming that you will always use a 3-character month in the name, put the following formula in B2:

=RIGHT(CELL("filename",$A$1),3)

This should produce "May". If you want to use a different cell, that's fine. The value can be hidden by changing the font color to white. Whichever cell you use, name it SheetMonth.

Then in C2 (or wherever), put the following to produce "5", which is the number for May.

=MONTH(DATEVALUE(LEFT(SheetMonth,3) & " 1, 2000"))

Name that cell MonthNum.

In D4, enter

=SheetMonth & " Month"

Copy that formula to the heading of each section, changing to " YTD" or " Summary" as needed.

From MonthNum, we can determine the relative column numbers for our Vlookups. Note that in BudCorp1, May is in column 15 (O), and on the ActCorp1, it's in column 7 (G). So, in Budget v Actual May!D8, enter

=VLOOKUP(A8,BudCorp1,MonthNum+10,FALSE)

and in G8, enter

=VLOOKUP(A8,ActCorp1,MonthNum+2,FALSE)

Drag these down to pull in the remaining values. Hopefully from this you can figure out how to create the rest of the formulas. For the YTD and Summary Actual values, you'll need the Vlookup to point to the Totals column number.



I would also recommend putting the following in B8:

=VLOOKUP($A8,BudCorp1,2,FALSE)

and dragging that down. You should do that on the Actual Spend sheet as well. That way, if you ever change your descriptions on the Budget sheet, they'll carry forward to the other sheets.

When that's all done, here's the fun part. Double-click your tab name, change May to Apr, hit enter and see the magic happen.

To create your subsequent month sheets, just make 11 copies of this sheet, change the month names, and if it's set up right, you're done!

Be aware that the YTD values in all the sheets will always pull in the CURRENT YTD values. If that's a problem, you'd probably need to do a Copy/Paste Special - Values at the end of each month to lock the values in, but be careful to keep at least one sheet with the actual formulas.

Have fun!

[FDibbins]

Hi and welcome to the forum

I have taken the liberty of modifying your file a little, as follows

1. changed your "dates" to real dates
2. set up your dates so that all you need to do (next year) is change Budget K3 (called start now) and all dates adjust accordingly
3. modified the AvB sheet headings so the month and headings change automatically
4. put a drop-down in BvA sheet G1 for you to select the dates
5. I have put my formulas in columns F and L so you can compare them with what you had. If this is what you want, you can copy them to the correct columns, and then copy down as needed

I did not do much with the but perhaps you can see what I did and replicate that for the budget sheet?

A point to consider for future reference - you should try and avoid using/creating merged cells, they cause all sorts of problems with formulas, and there is almost always another way to do what you want. Take a look at Actual sheet row 440:442 and then 445. They look the same, but 445 uses only 1 row

[Dreamxcell]

Consolidated Marketing Report June.xlsx Also I have the file for June here but havn't inputted the numbers for June yet.
I havn't put it the vlookup formulae yet but i have tidied up the sheet to have a seperate box for YTD
should be easier now to use.
Will this mess up the info you guys gave me? i should have attached the June one first but its basically the same file just with extra boxes

EDIT: Just had a look there. The vlookup formulas that you put in for corporate Nate, If i was to do this for Brazil instead of corporate would the formula change much? i re-named BudCorp1 to A51:W58 and changed the A8 to A51 but the NA comes up.

[Dreamxcell]

thanks so much for the reply guys going to have a read of that now and see what i can do

[Dreamxcell]

Consolidated Marketing Report Jun - Copy.xlsx

This is where im at now. I have put your first 3 formulas in except i had to put the 6 in myself.
Then I tried to make the vlookup formula for Brazil on the Bud V Act tab.

Both your posts were very helpful though, I appreciate it.
Ive now got the months to change automatically since the attachment thanks to Ford so thats a start anyway

[FDibbins]

Hopefully, natefarn can update his suggestion to meet your needs

[Dreamxcell]

your folmula is exactly what i need. The way it changes the figures when I change the month. It just went weird when i tried to copy the formula into the June file

[natefarm]

1. I would have expected you to name the Corporate - 1 tables BudCorp1 and ActCorp1, and the Brazil tables BudBrazil and ActBrazil (or BudBrazil2/ActBrazil2) so the range names match the data they represent.

2. I see that in all three sheets, your Brazil data (and others below it) doesn't have values in column A, which is the lookup value. The lookups won't work if the lookup values are all blank. If there will be no values in column A, then you'll need to adjust the named ranges to start with column B instead of A, and adjust the VLoookup formulas to use column B as the lookup value and subtract 1 from the column indexes I had provided (MonthNum + 9 and MonthNum + 1).

If you're having trouble understanding what I mean, let me know and I'll attach a sample.