![]() |
If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
![]()
Help!
I am trying to link two workbooks with a vlookup that searches for the last populated columns' entry in a range (see example below). 27/03 28/03 29/03 30/03 31/03 Data 6 4 Is there a way of doing this in a function? In a macro I would use the Range().End(xltoleft) code but I cannot figure out how to do something similar in a worksheet function. I basically want the lookup column to start at day 31 and make its way backwards until it finds a value. Another issue is that the seemingly blank cells contain formula - ie. not empty. Any ideas? Thanks!! Jen |
#2
|
|||
|
|||
![]()
1 1-Jan 2-Jan 3-Jan 4-Jan
2 Data A 1 2 3 Data B 1 2 3 4 5 6 Data Lookup 7 Data B 3 The formula for the Lookup is =VLOOKUP(A7,A1:E3,MATCH("",OFFSET(B1,MATCH(A7,A2:A 3,0),0,1,4),0),FALSE) This assumes that the "blank" cell formulsa are evaluating to "", a suppressed 0 does not work for this. The whole key is this MATCH("",OFFSET(B1,MATCH(A7,A2:A3,0),0,1,4),0) The Offset establishes a range for the Match ("",...) to use the Match (A7,A2:A3,0) establishes how many rows down from B1 to start the range (is this case 2). The 0 establishes 0 columns over from B1, the 1 is the height or number of rows in the range and the 4 is the number of columns. The whole offset function basically boils down to establishing the range (B3:E3 as when A7=Data B) to match the first time that "" appears, which establishes the column number for the V lookup. When you expand to a whole month change the 4 to 31. -- If this helps, please remember to click yes. "jenVBA" wrote: Help! I am trying to link two workbooks with a vlookup that searches for the last populated columns' entry in a range (see example below). 27/03 28/03 29/03 30/03 31/03 Data 6 4 Is there a way of doing this in a function? In a macro I would use the Range().End(xltoleft) code but I cannot figure out how to do something similar in a worksheet function. I basically want the lookup column to start at day 31 and make its way backwards until it finds a value. Another issue is that the seemingly blank cells contain formula - ie. not empty. Any ideas? Thanks!! Jen |
Thread Tools | |
Display Modes | |
|
|