Getting information from staff rota

I'm not quite sure what a rota is, but it sounds like a schedule.

You might want to look into the use of an advanced filter macro that copies relevant information from a data source tab, depending on any given criteria.

Another option might be to use a helper column to test each row for True/False given any set of parameters such as date, name, title, etc and then use a button that filters by the True to only display what's relevant.

Hi,

Difficult to be specific without seeing your workbook but don't ignore Pivot Tables which may be relevant in your case.

Otherwise daffodill11 has given you a succinct precis of the way forward.

Here's an example of each I've made for users in the past.

This uses the filter-in-place macro:
Parts List - Autosort Macro.xlsm


This allows is pulling from another tab and printing by date range:
adv filter macro - finished.xlsm

To attach a file, go to 'Go Advanced' next to the Submit button. Then on the new page scroll down to attachments, upload from source, and then insert in line.

Depending on how your source formatting is done, HLOOKUP, VLOOKUP, or INDEX(..MATCH( could all be potential solutions as well.

Actually, we can bypass the whole filter.

Instead, plop this bad boy into C13:

=INDEX($A$2:$A$5,MATCH("*"&$B13&"*",OFFSET($A$2:$A$5,0,MATCH(C$12,$B$1:$H$1,0)),0),0)

And copy down and over as needed. You will just need to change B13 and B14 to E and LS respectively.

To have your date ranges update, instead make C12 =TODAY() and then D12 = C12+1, E12 =D12+1, etc and then upon opening the workbook you'd just need to hit F9 to refresh all formulas to update to current date + whatever stretch of days you want to look at.

Edit: Added my example

Rota is English for Schedule.xlsx

Here's a nifty tip you can use whenever you run into formulas you don't quite understand:

You can select portions of a formula within the Formula Bar and hit F9 to have it calculate just that piece. (Just be sure to exit the cell with Escape instead of Enter.)

The formula I used breaks down like this:

=INDEX($A$2:$A$5,MATCH("*"&$B13&"*",OFFSET($A$2:$A$5,0,MATCH(C$12,$B$1:$H$1,0)),0),0)

=INDEX(what table of data,what row #, what column #) so that if we used INDEX(A1:C3,2,3) it would output whatever is in B3 - the 2nd row and 3rd column of A1:C3

In your table, we're just referencing a single column, so the parameters are INDEX(A2:A5,something,0)

Onto that something:

Let's start with the deepest MATCH. The match looks for the date in top row and returns a number that notates how many columns over it went to find it.

Next - OFFSET. We need to look for the matches of E and L in a single column, but that column is different depending on the date. We start with a base range of A2:A5, and we offset that range by the date match to B1:H1. OFFSET's parameters are (range, # of rows, # of columns). We aren't shifting the range up and down, but we are shifting columns. This means when it finds a match for date three columns over, it changes the A2:A5 to D2:D5.

Last, we get to the code match. We're using wildcards * to look for any variation of what we're looking for. Similarly if A2 = bob123, and we did MATCH("*"&"BOB"&"*",a1:a5,0) it would return a 2, because it still sees the something+bob+something. Our match is looking for the code in the offset range, and it returns the row it found it on.


We put it all together and we get INDEX(list of names,row # code match,0) which returns the right name.


Using what I said initially, go into the formula in C13 and highlight just =INDEX($A$2:$A$5,MATCH("*"&$B13&"*",OFFSET($A$2:$A$5,0, MATCH(C$12,$B$1:$H$1,0) ),0),0) and hit F9.

Now highlight =INDEX($A$2:$A$5,MATCH("*"&$B13&"*", OFFSET($A$2:$A$5,0,10) ,0),0)

Again on =INDEX($A$2:$A$5, MATCH("*"&$B13&"*",{"off";"ls";"e";"off"},0) ,0)


=INDEX( $A$2:$A$5 ,3,0)

The final result is the 3rd row option of {"Mike";"Claire";"Sally";"Sue"}

EDIT - It works if I change if from an array to a column! I'm going to keep working through your explanation for a bit now

Hey Daffodil, thanks so much for the explanation. I'm just going through it at tonight, I just tried to use MATCH function myself but it returns an N/A.

When I evaluate the formula it say's that the cell being evaluated contains a constant...? I'm not too sure what this means, apparently (according to some on the net) it means that I have tried to enter information on a cell that has been formatted to Text or something, but mine definitely isn't, the value is definitely in the array and there are no spaces or whatnot following the information.

I've tried to look up the value 'mars' in the array E6:G8 in this example.

MatchFunction.xlsx

MATCH works for a single column or row only.

The use of OFFSET identifies the column to use.

Essentially, one MATCH picks the right column, and then the second MATCH finds the right row.

Ok, I'll take a crack at it when you post it.