D
Dan B
I got the function below to work in my spreadsheet. I'm now wondering if I
can add something in here to enter the words "No Policy" if one of the rows
does not exist under a given customer's data.
If possible, would that go in the VBS code or the formula?
note: See original post at bottom for explanation of what I'm doing.
Thanks.
___________________________
try this function. but it depend on that ur custumernames are in column A
and the cell just below is empty, then a cell with #
just like it seems to be in ur example
otherwise the function have to be modifyed a bit.
put in a regular module then use like this:
=Vlokup("Cust. #1",Sheet1!A1:A100,1,2) - return 2005 (column B)
=Vlokup("Cust. #1",Sheet1!A1:A100,3,2) - return 2003 (column B)
=Vlokup("Cust. #1",Sheet1!A1:A100,0,2) - return 1999 (0=first year)
=Vlokup("Cust. #2",Sheet1!A1:A100,5,4) - return 15 (column D)
Function Vlokup(Custumer As String, MyRange As Range, YearNumber, Col)
Dim Mysheet
Dim c As Range, Last
Set Mysheet = MyRange.Parent
Set c = MyRange
Application.Volatile
If YearNumber <> 0 Then
Vlokup = Mysheet.Cells(c.Find(Custumer, LookIn:=xlValues).Row +
YearNumber + 2, Col)
End If
If YearNumber = 0 Then
Last = c.Find(Custumer, LookIn:=xlValues).Offset(2, 0).End(xlDown).Row
Vlokup = Mysheet.Cells(Last, Col)
End If
End Function
______________________
Hi,
I'm building a sheet that pulls data from another worksheet based on a
customer number, so when I enter a customer number into cell it will pull
specific information. The part I'm stuck on is this....There are varying
numbers of rows below each customers' data depending on the number of years
they have been a customer....one row per year, starting with the current
year. I want to pull data for the pervious year, three years ago, five
years ago and the first year they were a customer. The data looks like
this:
"Cust. #1" "Cust. Name"
# Year Premium Losses Claims.....etc
1 2005 1000 50 1
2 2004 1500 10 2
3 2003 1500 0 0
4 2002 1500 15 1
5 2001 1500 15 1
6 2000 1500 10 1
7 1999 1500 0 0
"Cust. #2" "Cust. Name"
# Year Premium Losses Claims.....etc
1 2005 500 50 1
2 2004 500 10 2
3 2003 500 0 0
4 2002 500 15 1
The column with the # and numbers is actually part of the data, not the
Excel row number. So when I enter a customer number to look up, I need it
to look at only the data for that customer and pull rows 1, 3, 5 and the
last row, whatever it might be.
Is this possible, or am I dreaming?? I hope that makes sense.
Thanks,
Dan
can add something in here to enter the words "No Policy" if one of the rows
does not exist under a given customer's data.
If possible, would that go in the VBS code or the formula?
note: See original post at bottom for explanation of what I'm doing.
Thanks.
___________________________
try this function. but it depend on that ur custumernames are in column A
and the cell just below is empty, then a cell with #
just like it seems to be in ur example
otherwise the function have to be modifyed a bit.
put in a regular module then use like this:
=Vlokup("Cust. #1",Sheet1!A1:A100,1,2) - return 2005 (column B)
=Vlokup("Cust. #1",Sheet1!A1:A100,3,2) - return 2003 (column B)
=Vlokup("Cust. #1",Sheet1!A1:A100,0,2) - return 1999 (0=first year)
=Vlokup("Cust. #2",Sheet1!A1:A100,5,4) - return 15 (column D)
Function Vlokup(Custumer As String, MyRange As Range, YearNumber, Col)
Dim Mysheet
Dim c As Range, Last
Set Mysheet = MyRange.Parent
Set c = MyRange
Application.Volatile
If YearNumber <> 0 Then
Vlokup = Mysheet.Cells(c.Find(Custumer, LookIn:=xlValues).Row +
YearNumber + 2, Col)
End If
If YearNumber = 0 Then
Last = c.Find(Custumer, LookIn:=xlValues).Offset(2, 0).End(xlDown).Row
Vlokup = Mysheet.Cells(Last, Col)
End If
End Function
______________________
Hi,
I'm building a sheet that pulls data from another worksheet based on a
customer number, so when I enter a customer number into cell it will pull
specific information. The part I'm stuck on is this....There are varying
numbers of rows below each customers' data depending on the number of years
they have been a customer....one row per year, starting with the current
year. I want to pull data for the pervious year, three years ago, five
years ago and the first year they were a customer. The data looks like
this:
"Cust. #1" "Cust. Name"
# Year Premium Losses Claims.....etc
1 2005 1000 50 1
2 2004 1500 10 2
3 2003 1500 0 0
4 2002 1500 15 1
5 2001 1500 15 1
6 2000 1500 10 1
7 1999 1500 0 0
"Cust. #2" "Cust. Name"
# Year Premium Losses Claims.....etc
1 2005 500 50 1
2 2004 500 10 2
3 2003 500 0 0
4 2002 500 15 1
The column with the # and numbers is actually part of the data, not the
Excel row number. So when I enter a customer number to look up, I need it
to look at only the data for that customer and pull rows 1, 3, 5 and the
last row, whatever it might be.
Is this possible, or am I dreaming?? I hope that makes sense.
Thanks,
Dan