Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using the VLOOKUP function
I have a report that runs each month and it places a new sheet in the file
with all the info I need. Another sheet is used for trendind the results of the monthly sheets. I have the VLOOKUP function working properly (thanks to all of you) but the problems is that it will work ok this month but in future months it will not use column A as the Lookup value. Here is what I have Worksheets("Trend").Activate Range("A1").Activate Selection.End(xlToRight).Select ActiveCell.Offset(0, 1).Activate ActiveCell.Value = LastMonth & " Rank" ActiveCell.Offset(1, 0).Activate ActiveCell.FormulaR1C1 = _ "=VLOOKUP(RC[-11],'" & LastMonth & "'!C[-11]:C[67],64,FALSE)" ActiveCell.Offset(0, -1).Activate Selection.End(xlDown).Select ActiveCell.Offset(0, 1).Activate Range(Selection, Selection.End(xlUp)).Select Selection.FillDown The Vlookup is looking back 11 columns but I need it to always look to column A. The same for the 'Table Array' section. It needs to include column A. Any help is greatly aprreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using the VLOOKUP function
Hi Tony,
"=VLOOKUP(RC[1],'" & LastMonth & "'!C[1]:C[67],64,FALSE)" -- Kind Regards, Niek Otten Microsoft MVP - Excel "Tony" wrote in message ... I have a report that runs each month and it places a new sheet in the file with all the info I need. Another sheet is used for trendind the results of the monthly sheets. I have the VLOOKUP function working properly (thanks to all of you) but the problems is that it will work ok this month but in future months it will not use column A as the Lookup value. Here is what I have Worksheets("Trend").Activate Range("A1").Activate Selection.End(xlToRight).Select ActiveCell.Offset(0, 1).Activate ActiveCell.Value = LastMonth & " Rank" ActiveCell.Offset(1, 0).Activate ActiveCell.FormulaR1C1 = _ "=VLOOKUP(RC[-11],'" & LastMonth & "'!C[-11]:C[67],64,FALSE)" ActiveCell.Offset(0, -1).Activate Selection.End(xlDown).Select ActiveCell.Offset(0, 1).Activate Range(Selection, Selection.End(xlUp)).Select Selection.FillDown The Vlookup is looking back 11 columns but I need it to always look to column A. The same for the 'Table Array' section. It needs to include column A. Any help is greatly aprreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using the VLOOKUP function
That will do the same thing but in the opposite way. By using the RC[1] it
makes it look one column to the right to get the value for the vlookup. In the original line it used RC[-11] which makes it look 11 columns to the left for the lookup value. Next month I would need it to look 12 columns to the left so I can't use a set number, it would have to have the abilitiy to change. Maybe some way of setting a variable equal to how many columns over that the active cell is putting the formula in. Then use that variable in the vlookup formula to look to the left that many columns.....problem is I am not sure how to set that up. "Niek Otten" wrote: Hi Tony, "=VLOOKUP(RC[1],'" & LastMonth & "'!C[1]:C[67],64,FALSE)" -- Kind Regards, Niek Otten Microsoft MVP - Excel "Tony" wrote in message ... I have a report that runs each month and it places a new sheet in the file with all the info I need. Another sheet is used for trendind the results of the monthly sheets. I have the VLOOKUP function working properly (thanks to all of you) but the problems is that it will work ok this month but in future months it will not use column A as the Lookup value. Here is what I have Worksheets("Trend").Activate Range("A1").Activate Selection.End(xlToRight).Select ActiveCell.Offset(0, 1).Activate ActiveCell.Value = LastMonth & " Rank" ActiveCell.Offset(1, 0).Activate ActiveCell.FormulaR1C1 = _ "=VLOOKUP(RC[-11],'" & LastMonth & "'!C[-11]:C[67],64,FALSE)" ActiveCell.Offset(0, -1).Activate Selection.End(xlDown).Select ActiveCell.Offset(0, 1).Activate Range(Selection, Selection.End(xlUp)).Select Selection.FillDown The Vlookup is looking back 11 columns but I need it to always look to column A. The same for the 'Table Array' section. It needs to include column A. Any help is greatly aprreciated. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using the VLOOKUP function
Hi Tony,
So Sorry, It should be RC1, without the brackets -- Kind Regards, Niek Otten Microsoft MVP - Excel "Tony" wrote in message ... That will do the same thing but in the opposite way. By using the RC[1] it makes it look one column to the right to get the value for the vlookup. In the original line it used RC[-11] which makes it look 11 columns to the left for the lookup value. Next month I would need it to look 12 columns to the left so I can't use a set number, it would have to have the abilitiy to change. Maybe some way of setting a variable equal to how many columns over that the active cell is putting the formula in. Then use that variable in the vlookup formula to look to the left that many columns.....problem is I am not sure how to set that up. "Niek Otten" wrote: Hi Tony, "=VLOOKUP(RC[1],'" & LastMonth & "'!C[1]:C[67],64,FALSE)" -- Kind Regards, Niek Otten Microsoft MVP - Excel "Tony" wrote in message ... I have a report that runs each month and it places a new sheet in the file with all the info I need. Another sheet is used for trendind the results of the monthly sheets. I have the VLOOKUP function working properly (thanks to all of you) but the problems is that it will work ok this month but in future months it will not use column A as the Lookup value. Here is what I have Worksheets("Trend").Activate Range("A1").Activate Selection.End(xlToRight).Select ActiveCell.Offset(0, 1).Activate ActiveCell.Value = LastMonth & " Rank" ActiveCell.Offset(1, 0).Activate ActiveCell.FormulaR1C1 = _ "=VLOOKUP(RC[-11],'" & LastMonth & "'!C[-11]:C[67],64,FALSE)" ActiveCell.Offset(0, -1).Activate Selection.End(xlDown).Select ActiveCell.Offset(0, 1).Activate Range(Selection, Selection.End(xlUp)).Select Selection.FillDown The Vlookup is looking back 11 columns but I need it to always look to column A. The same for the 'Table Array' section. It needs to include column A. Any help is greatly aprreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to combine Combo Box function with Vlookup function | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
HOW DO I NEST THE VLOOKUP FUNCTION WITH THE LEFT FUNCTION | Excel Worksheet Functions | |||
how do I write a vlookup function within an iserror function so t. | Excel Worksheet Functions | |||
I want to use Vlookup function and AND function in a single formu. | Excel Worksheet Functions |