Complicated Reverse-order lookups

[Sir Babydum GBE]

Good morning.

I cant work out how to do this set of formulae. It's a bit complicated to explain so I've attached a mocked up worksheet.

On the sheet you see a table containing Dates (Column B) , classrooms (Row 5), Assignment Codes (Row 6), leaders, assistants and scores (Body). Columns that are greyed out are 'assistants' columns. As you see, classrooms 1D and 1U do not have assistants.

So, in row 2 I have typed values but I actually need the formulae to return the results shown.

To explain: Suppose D2 is a validation list and i select "Craig"...
So in F2 i need a formula to search for "Craig" from the bottom of the list up and return the date from Column B and the assignment code from Row 6.
In F4 The formula will look for the next occurrence of "Craig" and again return the date and assignment code. Same again for the 3rd occasion of "Craig"
However...
When it comes to searching for his last 3 scores, I need to ignore the columns where he may appear as an assistant (Columns G, J, O & R) but search for his last three scored appearances and return the score which is found in the cell to the right of his name.

Hope you can help - thanks in advance

BD

[Bob Phillips]

Whilst I am fully aware of who you are Sir BD, may I humbly suggest a different approach?

If you capture the data in a different format, much more tabular than cross-tab, like the image below, it is a simple matter to show the results in a pivot table

[Sir Babydum GBE]

Hello again Mr X!

Actually, as fab as your suggestion would normally be, there is a reason why the info is formatted in the way it currently is. Save for a total re-design of a very large spreadsheet, the example I provided is a very basic copy of a much nicer front end. The UI is built to allow scheduling of students. As a student is selected his history appears on the top of the screen which allows the user to assess whether an assignment is appropriate for a given student.

The tabular format is how I'd normally build the sheet, but the way it is easier for the user. I actually already have the formulae that call the correct results, but i have gone a very long way around it with a history list of each student on a very large sheet, and my spreadsheet is huge and getting hard to send. (there are about 700 'helper' formulae on this hidden sheet, and i figured if I were clever enough to get just 6 formulae to do all that... But i'm not clever enough.

True, I have a brain the size of a planet. But the part that deals with Excel Formula was damaged in a freak snowing accident on the Andes.

[Bob Phillips]

I think that format is challenging.

Do you have Excel 2010, if so can you install Power Query, then we could probably use that to unformat your formatted data to a tabular style, then pivot that.

[Bob Phillips]

Here is some code to do it all for you, creates a table from your summary, creates a pivot and adds a slicer.

Make sure you get rid of that merged cell about more data before running it, it messes it up


Option Explicit

Public Sub CreateView()
Const SHEET_TABULAR As String = "Tabular Data"
Const SHEET_PIVOT As String = "Pivot View"
Const PIVOT_LEAD As String = "pvtLeadSummary"
Const CELL_START_TABLE As String = "B6"
Dim this As Worksheet
Dim wsTab As Worksheet, wsPivot As Worksheet
Dim rowStart As Long, rowLast As Long, rowCount As Long
Dim colStart As Long, colLast As Long, colCount As Long
Dim rowNext As Long
Dim i As Long, ii As Long

Application.EnableEvents = False
Application.ScreenUpdating = False

With ThisWorkbook

Set this = ActiveSheet

Application.DisplayAlerts = False
On Error Resume Next
.Worksheets(SHEET_TABULAR).Delete
.Worksheets(SHEET_PIVOT).Delete
On Error GoTo 0
Application.DisplayAlerts = True
Set wsTab = .Worksheets.Add(after:=.Worksheets(.Worksheets.Count))
wsTab.Name = SHEET_TABULAR
wsTab.Range("A1:E1").Value = Array("Date", "Class", "Lead", "Assistant", "Score")
rowNext = 1

With this

rowStart = .Range(CELL_START_TABLE).Row
rowLast = .Range(CELL_START_TABLE).End(xlDown).Row
rowCount = rowLast - rowStart + 1
colStart = .Range(CELL_START_TABLE).Column
colLast = .Range(CELL_START_TABLE).End(xlToRight).Column
colCount = colLast - colStart + 1
For i = rowStart + 1 To rowLast

ii = colStart
Do While ii < colLast

rowNext = rowNext + 1
.Cells(i, colStart).Copy wsTab.Cells(rowNext, "A")
.Cells(rowStart, ii + 1).Copy wsTab.Cells(rowNext, "B")
.Cells(i, ii + 1).Copy wsTab.Cells(rowNext, "C")
.Cells(i, ii + 2).Copy wsTab.Cells(rowNext, "E")
If .Cells(rowStart, ii + 3).Value Like "A*" Then

.Cells(i, ii + 3).Copy wsTab.Cells(rowNext, "D")
ii = ii + 3
Else

ii = ii + 2
End If
Loop
Next i
End With

Set wsPivot = .Worksheets.Add(after:=.Worksheets(.Worksheets.Count))
wsPivot.Name = SHEET_PIVOT
Call PivotBuild(wsTab.UsedRange.Address(False, False, xlR1C1, True), SHEET_PIVOT, PIVOT_LEAD)
End With

Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Private Function PivotBuild( _
ByVal SourceData As String, _
ByRef PivotSheetName As String, _
ByVal PivotTablename) As Boolean
Dim pvtCache As PivotCache
Dim pvtTable As PivotTable
Dim slcrCache As SlicerCache
Dim slcr As Slicer
Dim startPivot As String

Set pvtCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:=SourceData, _
Version:=xlPivotTableVersion14)

startPivot = "'" & PivotSheetName & "'!R1C1"
Set pvtTable = pvtCache.CreatePivotTable(TableDestination:=startPivot, _
TableName:=PivotTablename)

With pvtTable

With .PivotFields("Date")

.Orientation = xlRowField
.Position = 1
End With

With .PivotFields("Class")

.Orientation = xlRowField
.Position = 2
End With

With .PivotFields("Lead")

.Orientation = xlRowField
.Position = 3
End With

With .PivotFields("Assistant")

.Orientation = xlRowField
.Position = 3
End With

.AddDataField .PivotFields("Score"), "Score ", xlSum

.HasAutoFormat = False
.InGridDropZones = True
.ShowDrillIndicators = False
.RowAxisLayout xlTabularRow

.PivotFields("Date").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
.PivotFields("Class").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
.PivotFields("Lead").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
.PivotFields("Assistant").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
.PivotFields("Score").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)

.PivotFields("Date").AutoSort xlDescending, "Date"
End With

Set slcrCache = ActiveWorkbook.SlicerCaches.Add(ActiveSheet.PivotTables("pvtLeadSummary"), "Lead")
Set slcr = slcrCache.Slicers.Add(ActiveSheet, , "slcrLead", "Lead", 207, 549.75, 144, 198.75)

slcrCache.ClearManualFilter
End Function

[Aflatoon]

Do you mean like this?

[Sir Babydum GBE]

Thank you both. Mr X: probably should have said that the sheet needs to be compatible with Excel 365 for iPad, and therefore must be totally formula driven. I know that this is "VBA" Express, but its also my first stop for any excel question...

(It's mostly managed on PC Excel, but the functionality has to be there for mobile tweaking). So i feel guilty that you've done all the code work for me. On the point of the format looking challenging: the supplied example doesn't look the same, it has the same structure but a lot of though has gone into the colour and spacing and we felt (believe it or not) that as a scheduling tool for a non-excel-type to use, it was the best option.

On your other point, I have office 365 so I guess that Power Query would be something worth getting my teeth into - but i'm assuming its not currently compatible with the mobile version.

So while we're at it: does anyone know if Microsoft plan to bring more functionality into the mobile versions (for example, VB)?

Aflatoon - thank you too - and yes, that does the trick.

[Bob Phillips]

Well you have a good formula solution provided by Rory, so you are good to go. I did start trying to get a formula solution, but gave up when I decided I could knock up the code in 5 mins, naturally lazy. So feel not guilty, you know I enjoy a bit of coding.

I can't see MS adding VB to the mobile versions, they are wedded to HTML5 and JavaScript (until the next fad comes along), and are not backing classic VB at all.

BTW, I thought that you had moved from Wales, and that you weren't Welsh anyway, so is that flag just a relic? (a bit like their Rugby team :rotlaugh:)