Linking to PrevSheet using vlookup command

[Paul Naylor]

Hi . I'll start by saying i'm tying to do this in excel 97 , which were still using at work

Linking to previous worksheet. I've downloaded the following VBA code so instead of having to keep changing worksheet names in formula's I can use "PrevSheet" command , which does work, but having problems combining this with Vlookup

2 sets of data :

Sheet 1 ( Yesterday's date)
Job No ( Column A)
Previous Days Actions ( Column B)
Today's Actions ( Column C)

Sheet 2 ( Today's date)
Job No ( Column A)
Previous Days Actions ( Column B)
Today's Actions ( Column C)

Requirement: in sheet 2 ( today's) want to do a vlookup on job no. on previous sheet and if "True" display the result display the contents previous days actions ( column C) in today's column D, but if previous days actions is BLANK ( column c) , display previous days actions ( column B) and if that is BLANK display a text message "No previous actions".

Hoping someone can help.........


Function PrevSheet(RCell As Range)

Dim xIndex As Long
Application.Volatile
xIndex = RCell.Worksheet.Index
If xIndex > 1 Then _
PrevSheet = Worksheets(xIndex - 1).Range(RCell.Address)
End Function

 

[Paul Naylor]

Hi Futher to my earlier post , i've managed to get the following formula to work to display the results for 2 conditions i.e. previous actions and not on previous report , so now only need help to display results of a different cell if job no is on previous sheet , but no actions taken on previous sheet ( i.e. column c) want to display the results of the PevSheet Column B and if that BLANK display "no previous actions taken"

to display previous days actions :

=IFERROR(VLOOKUP($A2,PrevSheet($A:$c),3,0),"Not on Previous Report")

But also want to add 2 further conditions ....if job no (A2) if no the report but Previous Days Actions ( column C) is "BLANK" want to display PrevSheet ( Column B) and if that "BLANK" display "no previous actions taken"

Hoping someone can help....