Forum Discussion
Vlookup
- A simple VLOOKUP like this in D38 is all you need:
=VLOOKUP(B38,
'Incoming Finance'!B$5:M$348,
5,0)
Note that the col_index_num argument of 5 is hard-coded because there is no identical column label in the Incoming Finance Sheet against which the corresponding column label in the Income Tracking Schedule Sheet may be matched.
hI,
For a further explanation in the file you will see Incoming Finance worksheet with dates in column B.
You will also see on worksheet Income Tracking Schedule (US$) dates in column B. these are the Saturdays of each week of the project.
So for every Saturday on the Income Tracking Schedule (US$) i would like to retrieve the Running Total (US$): value in Column F on the incoming Finance worksheet for tat Saturday and place it in the Income Tracking Schedule (US$) column D for that particular Saturday.
Hope you can still help.
Regards
Ian
=VLOOKUP(B38,
'Incoming Finance'!B$5:M$348,
5,0)
Note that the col_index_num argument of 5 is hard-coded because there is no identical column label in the Incoming Finance Sheet against which the corresponding column label in the Income Tracking Schedule Sheet may be matched.
Hi Twifoo
That seems to have worked for cell B38, and it does return the correct value.
So I modified your Vlookup statement to =VLOOKUP(B38:B84,'Incoming Finance'!B$5:M$348,5,0) and all the cells now show the current value.
I tried this formula before and did not get the correct answer. You have shown me that the hard coded value of 5 in the formula includes the original cell as the first cell, i.e 1 Column B of Incoming Finance.
I used 4 as I did not count the original column (B) as part of the offset.
You have been a great help and I thank you.
Best Regards
Vonryan
Thank you
Vonryan
- You’re welcome!
Hi Twifoo,
Are you up for a new challenge. I am coming direct to you as you have been a great help so far. Your futher assistance would be greatfully appreciated.
I am venturing into VBA as it appears that this is the only way I can achieve what I want.
Ok here goes.
On the Incoming Finance work sheet there are a lot of rows that have very little data in them. This is OK as all entries on this sheet are manual. What i want to do is hide rows if the date is in the past and the column E is empty for that date.
I tried creating the following in VBA
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Activate
If Not Application.Intersect(Range("A1"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "Hide": Rows("5:10").EntireRow.Hidden = True
Case Is = "Unhide": Rows("5:10").EntireRow.Hidden = False
End Select
End If
End SubThis works when I type Hide or Unhide in cell A1.
I want to modify this to say the following:
If Cell (B5) <= TODAY() and Cell (E5) <>0 then Row("5").EntireRow.Hidden = True
But I want to do this for all rows in the worksheet.
Are you willing to take on this challenge.
Best Regards
Vonryan
Hi Twifoo
That seems to have worked for cell B38, and it does return the correct value.
Now how do I get the Vlookup to do it for all Saturdays in column B of the Income Tracking Schedule (US$) work sheet.
I appreciate all your help so far.
Thank you
Vonryan
- I inadvertently forgot to mention earlier. You just need to copy the formula in D38, down the rows.