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
thx experts...
BobR
vlookup-question.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Gowflow....I tried your example but I don't see the VLookup statement being modified. Its still hard coded with a 5.
thx
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
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
ASKER
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
My solution allow the user to select any month and view the results at ANY time.
thank you.
gowflow
PLease let me know if this is what you need or we can modify it.
gowflow