Apr 25 2019 09:37 PM
Dear All,
I would like some help with Vlookup
What I need to do is look at 2 seperate list of dates. If the dates are the same from each worksheet then I need to return to a specific cell contents of another cell.
Meaning the following.
i look at (B38:B84) on one sheet, I look at 'Incoming Finance'!B5:B348) on another sheet if the contents (dates) are the same then I want to copy the contents of incoming finance cell E5:E348 to (D38:D84)
In english this means if the two dates are the same copy the values from a horizontal offset cell to another cell in a range of cells.
More detailed is if worksheet "Income Tracking" cell B38 Date is the same as any cell within the range E5:E348 on worksheet Incoming Finance. then copy the contents of the cell (HXXX) from income tracking" worksheet to cell FXXX on "Income tracking" worksheet where cell FXXX is the same date as the the two compared dates.
Best Regards
Vonryan
Apr 25 2019 11:04 PM
Apr 26 2019 01:19 AM
Apr 26 2019 01:50 AM
Apr 26 2019 02:41 AM
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
Apr 26 2019 03:00 AM
Hi and thanks for getting back to me.
In answer to your questions please find below
your labels for Columns C to E are Planned Remittance Total, Actual Weekly Running Total, and Weekly Remittance Required.
How do you calculate the amounts to be returned under each Column for each date in Column B?
These are calculated figures based on the following:
Planned Remittance is based on =ROUNDUP(C2/C7,1) which is budget / No of weeks remaining
Actual Weekly Running Total is based on ='Incoming Finance'!E7. This a manual added value that I am trying to automate with the Vlookup
Weekly Remittance Required is based on =C38 which is derived from a =ROUNDUP(C2/C7,1)
What I want to achieve is a formula via Vlookup or some other method that will look at the dates on both worksheets Incoming Finance and Income Tracking Schedule (US$) and where the dates match, ie Saturday 30th March 2019 take the value that is given in Running Total (US$): column F of Incoming Finance and place it in column D of the Income Tracking Schedule (US$) worksheet at exactly the same date. In this example this would be cell D38.
I am requesting a method of doing this for all cells in column D for the Income Tracking Schedule (US$) worksheet.
I hope this clarifies things for you, if not then please ask for more information.
I appreciate all your time and effort thus far.
Regards
Vonryan
No identical labels exist in the Incoming Finance Sheet. Please manually enter your desired results under each Column and explain how you calculated each of them so that I can decipher the formulas applicable thereto.
Apr 26 2019 03:22 AM
SolutionApr 26 2019 03:30 AM
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
Apr 26 2019 03:33 AM
Apr 26 2019 03:42 AM
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
Apr 26 2019 07:26 PM
Hi there,
If you are interested, maybe you can help me with another issue as I am a real beginner with Excel if you want.
On the Income Tracking Schedule (US$) I have added a deviation (+/-) column in which I calculate the deviation of what has been remitted in foreign currency and what should have been remitted. The formula is =D38-C38, very simple, but my issue is I want to automatically do this for each Saturday instead of manually dragging the formula down in the column every Saturday to update the spreadsheet.
What I would like to do is check if the date in column B is in the past, if it is then apply the formula to the cell of that date which will calculate the deviation otherwise leave the cell blank.
I cannot find a funtion that will do this.
I have attached the revised spreadsheet.
Looking forward to your assistance.
Vonryan
Apr 26 2019 09:25 PM
Apr 26 2019 09:44 PM
Hi there@Twifoo
Once again, perfect just what I needed.
Thank you so much. Saved me hours trying to figure it out.
Best Regards
Vonryan
Apr 27 2019 09:01 PM
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 Sub
This 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
Apr 26 2019 03:22 AM
Solution