Link to home
Start Free TrialLog in
Avatar of bobrossi56
bobrossi56

asked on

Excel VLookup in a macro where returned column changes every month

I hope I can explain this clearly. I have an Excel file that shows YTD months and a total that I need to create a macro for to do some formatting, and a VLookup. I know how to do that. Problem is, the column # in the VLookup will change every month as the total column will get pushed over as a new month is added. I attached a mock up of the file, if you go to the SUGAR worksheet you will see the macro button. Running it now works fine because the VLookup is =VLOOKUP(B:B,Sheet1!C:H,5,FALSE) where column # 5 is the total column from the pivot table. Next month when April is added the total column # will be 6, and so on. Not sure how to control that in the macro. Hope this makes sense.
thx experts...
BobR
vlookup-question.xlsm
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

Here is my suggestion. I created a combobox in sheet sugar where a small code fills it from 1 to 12 which are the Months. and modified the VLOOKUP to point to cell F4 that is linked to this combobox. So you can now select a month that you want and see the results instantaneously. If the month has not been active you will get a message Not run Yet. You can change the message in the formula to something that suits you.

PLease let me know if this is what you need or we can modify it.
gowflow
ASKER CERTIFIED SOLUTION
Avatar of Norie
Norie

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bobrossi56
bobrossi56

ASKER

Gowflow....I tried your example but I don't see the VLookup statement being modified. Its still hard coded with a 5.
thx
You must be opening the wrong file.

Look at the file I posted which is:
vlookup-question-V01.xlsm

Maybe by mistake you opened yours. Mine has a combobox and the formula has $F$4 and not a 5 !!!
check again
gowflow
Both solutions worked but using the 2 lines of code is much easier for the user than dealing with a combo box change. Thanks to both for your efforts.
Noted your comment however please allow me a comment.

My solution allow the user to select any month and view the results at ANY time.
thank you.

gowflow