Formula to retrieve last cell in a row with data then go left 1 or 2?

[BrisAdrian]

Hello,

Is there a formula I can put into a cell that will use another cell as a reference, find a match for that number on another sheet, go to the last cell with data in that row, but then return the data from cells to the left of that, ie: -1 or -2.

Sheet1:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]reference data (12345)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Cell that will contain formula and return value based on reference (12345)[/TD]
[/TR]
</tbody>[/TABLE]

Sheet2:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]label 1[/TD]
[TD]Date 1[/TD]
[TD]Date 2[/TD]
[TD]Date 3[/TD]
[TD]Date 4[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]1/1/01[/TD]
[TD]1/1/02[/TD]
[TD]1/1/03[/TD]
[TD]1/1/04[/TD]
[/TR]
</tbody>[/TABLE]


So that if I had no data in "Date 4" the formula would return what is in Date 3.
I've been trying to use -1 and so on with Index/Match but I don't fully grasp it.

In the end I would like to provide employees with a history of their test results like so:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]1/1/4[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]1/1/3[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]1/1/2[/TD]
[TD]11[/TD]
[/TR]
</tbody>[/TABLE]

The reason I require this is so that I can give people an easy to read printout and to produce a graph for them to easier grasp.

I'm sorry I cant upload an example. Every file hosting site is blocked on the work PC's and I wont be home for 2 more weeks.

Thank you kindly for any help!

-Adrian

 

[svendiamond]

You can use VBA for all this stuff. What exactly do you want to do?

find a match for that number on another sheet, go to the last cell with data in that row, but then return the data from cells to the left of that, ie: -1 or -2.

So, you want to find a value in Column A on Sheet2, and return all the values from that row? Or just the one farthest to the right?

 

[BrisAdrian]

Hi svendiamon, Thanks for replying.

On one sheet I have the data as:
Name | ID | Date | Result | Date | Result (Date/result continue over and over)

On my main sheet I need to be able to enter a persons ID and then have a list populated in descending order:
Date | Result (newest)
Date | Result (older)
Date | Result (oldest)

I am starting to think that I do need VBA for this, I got a formula to return the most resent result, but because the data range is so large A1 to CO1200, it was crashing the sheet when I entered it and telling me Excel had run out of resources.

I'm a medic by trade and the idea behind this is that I can keep a database of various health markers for my guys, then given them an easy to understand snapshot which includes a graph of how certain markers are trending (cholesterol, BMI) etc. So I have really basic EXCEL skills (VLOOKUP).

I appreciate any help you can send my way. I could even email you a stripped down version of the database I'm building if that would make helping me out easier.

Thanks again!

EDIT: a BOX.com link to a stripped version of my spreadsheet https://app.box.com/s/e7l2ljjwpfmxwnh45o4rnzn57pcnut8c

 

[BrisAdrian]

I think this link should work for an upload of my spreadsheet:

https://app.box.com/s/e7l2ljjwpfmxwnh45o4rnzn57pcnut8c

 

[svendiamond]

It says the file has been removed, so I couldn't download it. Upload it again? You can easily accomplish your desired results using VBA.

 

[BrisAdrian]

Hi Sven,

I've uploaded it again:
https://app.box.com/s/6e91emeyruqlkhgkdmmqlvrc8kvpwisu

Could you please let me know the VBA for this, way above my head.

Thanks again for your help.

 

[svendiamond]

Try this:

Sub findThing()
On Error Resume Next
Dim lookupValue As String, foundMatch, searchRange As Range
Dim lastColumn, lastRow As Integer


Sheets.Add.Name = "Results"


With Sheets("Results")
    .Cells.Delete
    .Range("A1").Value = "Date"
    .Range("B1").Value = "Results"
End With


With Sheets("Master")
        lookupValue = InputBox("Enter value")
    Set searchRange = .Range("A1:A100")
    Set foundMatch = searchRange.Find(lookupValue)
        matchRow = foundMatch.Row
        lastColumn = .Cells(matchRow, Columns.Count).End(xlToLeft).Column
End With


For testCounter = 20 To lastColumn Step 2
    firstBlank = Sheets("Results").Range("A" & Rows.Count).End(xlUp).Row + 1
        With Sheets("Master")
            If IsDate(.Cells(matchRow, testCounter)) Then
                Range(.Cells(matchRow, testCounter), .Cells(matchRow, testCounter + 1)).Copy _
                    Destination:=Sheets("Results").Range("A" & firstBlank)
            End If
        End With
Next


MsgBox "Complete"


End Sub

You can remove the red line after the Results sheet has been added.