Making Individual Schedules Based on Master Matrix

I1 = Alfie

I2 =01-01

J2 =INDEX($A$1:$E$7,MATCH($H$1,$A$1:$A$7,0),MATCH($H2,$A$1:$E$1,0)) and drag down.

You get better help on your question if you add a small excel file, without confidential information.

Please also add manualy the expected result in your file.

To Attach a File:

1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

In your sheet you can't see in row 2 which data it is I2 and AN2 are both 1.

which is from september and which is from oktober?

with Pivot Query.

now you can filter on the name in column A (in sheet Oeldere)

See the attached file.

Power querry is probably an add in for Excel 2013 (but is available in Excel 2016).

See if this suits your question.

I googled for Power query excel 2013 and found this link

https://www.microsoft.com/nl-NL/down....aspx?id=39379

Power Query is an add in that needs to be installed to use this functionality.

Have you Power Quary add in installed?

if you have power query I believe you can use that also on multiple worksheets (but I am not sure).

But if not, you can make such a sheet and combine it for all data in a master sheet.

Selemat,

you may try to use array formula,

it look like what oeldere's workbook, but array formula may cause the lag.

in A2, paste this array formula,

Formula:

Please Login or Register  to view this content.

it change the table cell that not blank to row number (B5, C5, B6, "",F3 > 5,5,6,"",3) and use SMALL function to sort the row (3,5,5,6,"") and use INDEX to display the name. (Kel, Ash, Ash, Fish,"")

in B2, paste this array formula,

Formula:

Please Login or Register  to view this content.

similar to above but the small(array,K) is change by repeat of current name (1,2,3,1,2,1,2,3,4,5,...)

in C2 paste this formula,

Formula:

Please Login or Register  to view this content.

normal index function, (=index([array], match(),match())

PS: array formula is need to confirm by Ctrl + Shift + Enter instead of Enter only

If not understand, please follow below step:

1, Paste the formula to the cell
2, Double click on the cell
3, press Ctrl + Shift + Enter
It show { sign in front of formula. (ie {=index(.....)

a bit of out of topic...

If you want to sort the table, I encourage you to copy and paste to new sheet first.

To avoid lag due to recalculation of formula.

Not to confuse matters more, but here's another array formula solution.

In the summary sheet C1, next to the Name put the start month from the data sheet ... 9 in this case.

Then array enter (Ctrl + Shift + Enter) this in A2 and fill down @45 rows. It returns the relevant dates.

Formula:

Please Login or Register  to view this content.

Then for the locations array enter this in B2 and fill down the same.

Formula:

Please Login or Register  to view this content.

There is also a drop down in B1.

I can't say without seeing the workbook in question.

Are you array entering the formulas?

Edit Twice now you've mentioned changing all the references (BoreWorker's and mine) and it still doesn't work. You also mentioned #REF! errors. Are changing the sheet references as well?

Maybe the sheet name problem?

Did your sheet name got space between?
(ie Pro and Cons)

then the formula should be
'Pro and Cons'!A1

instead of

Pro and Cons!A1