Repurposing data without pivot tables... possible?

[Hambone70]

Hello! I'm still trying to find a solution to a problem with our work deconflictor and schedule. I have simplified the desired output.

We manually input our time off in the attached 2016 Deconflictor file. I need this data to be carried over automatically into the 2016 Weekly Schedule file. I have manually filled in the first two days of the Schedule as an example.

The only names and reasons we do not want carried over from the Deconflictor to the Schedule are when the reason cells are marked "Inactive".

Any help appreciated!

[mikeTRON]

It is possible with Arrays, but honestly I would still use a pivot table in the background then if you want the same format you can use vlookups or index formulas off of the pivot.

Yikes, also dont use merged cells, as they are garbage and make copying and pasting formulas and such a HUGE pain.
It is definitely a better practice to use ONE cell for a formula instead of FIVE for the cell with BARBARA in the name.

Let me work up something really quick and I will attach it.

[ChemistB]

The number of merged cells in your Weekly Schedule file make it much more difficult. There was no reason to merge B:F, G:K, L:P, etc. I will play with this a bit but it needs a lot of work.

[Hambone70]

Thanks!

This is a small part of the weekly schedule, hence the narrow columns and merged cells. It would be very difficult to format the entire weekly schedule without using merged cells.

Perhaps I could use an intermediate worksheet without merged cells to collect the data, then pass it to the worksheet with merged cells?

[mikeTRON]

There is literally NO reason for you to merge 5 cells together when ONE cell would work BETTER.

Attached is TWO potential solutions for you, and I strongly prefer the Pivot solution, atleast using a pivot as a background aggregater then whatever Summary layout you want with lookups to the pivots.

[mikeTRON]

It would be very difficult to format the entire weekly schedule without using merged cells.

Again we aren't being difficult just to be difficult but merged cells bring a lot of consequences and almost NO benefit in your example.
I disliked your merged cells so much I created a new simple summary example without them

[JohnTopley]

Interjecting here: this "problem" has been around for months (I have has several attempts at it) and consistently made the points repeated in this post about merged cells and formatting generally. But to no avail ( ... so far!!).

It would be very difficult to format the entire weekly schedule without using merged cells.

Why? how can 1 cell take more "space" than 5 merged cells?

Why is Sheet1 less acceptable than "1-5 Jan" ?

[Hambone70]

OK. Thanks for the comments. I'm not ignoring the "no-merged-cells" suggestion. The schedule would be very difficult to design without merged cells.

The reason for the narrow columns and merged cells is that the weekly schedule has a large amount of input data, and much of this data requires different column widths in different rows to accommodate the required data. The attached file shows the current weekly schedule format with the data stripped out. Most non-blue cells will have data in them, and it would be unwieldy to not allow a variety of column widths.

We build a week at a time, and archive the weekly schedules at the end of each month. Naming the worksheet for the current week populates the worksheet with the 5 days (cell M1 gets the week from the worksheet name, cells B2, AI2, BP2, CW2, ED2 get their individual days from M1. When we need to make a new week, we CTRL-drag the current week's worksheet, rename it for the current week, and each individual day's date automatically populates. We also have a daily schedule, which auto-populates from the weekly schedule. This all works fine. All I want to do is to link the deconflictor to the weekly schedule to auto-populate the employees who have time off.

Thanks again for your time!

[ChemistB]

here's my shot at it. I created unique ARRAYED formulas in B4,C4,D4,and F4 and then copied them down. Then selected the entire range B4:E14 and copied and pasted it to G4, L4, Q4 and V4

So in B4
=IFERROR(INDEX('2016 DeconflictorA.xlsx'!Names, SMALL(IF(ISTEXT(INDEX('2016 DeconflictorA.xlsx'!DataSet,,MATCH(B$1,'2016 DeconflictorA.xlsx'!Dates,0)+COLUMNS($B$4:$B$4)-1)), ROW($B$3:$B$28)-2), ROWS($B$4:$B4))),"")

In C4
=IFERROR(INDEX('2016 DeconflictorA.xlsx'!DataSet, SMALL(IF(ISTEXT(INDEX('2016 DeconflictorA.xlsx'!DataSet,,MATCH(B$1,'2016 DeconflictorA.xlsx'!Dates,0)+COLUMNS($B$4:$B$4)-1)), ROW($B$3:$B$28)-2), ROWS($B$4:$B4)),MATCH(B$1,'2016 DeconflictorA.xlsx'!Dates,0)+COLUMNS($B$4:$B$4)-1),"")

In D4
=IFERROR(INDEX('2016 DeconflictorA.xlsx'!Names, SMALL(IF(ISTEXT(INDEX('2016 DeconflictorA.xlsx'!DataSet,,MATCH(B$1,'2016 DeconflictorA.xlsx'!Dates,0)+COLUMNS($B$4:$C$4)-1)), ROW($B$3:$B$28)-2), ROWS($B$4:$B4))),"")

In E4
=IFERROR(INDEX('2016 DeconflictorA.xlsx'!DataSet, SMALL(IF(ISTEXT(INDEX('2016 DeconflictorA.xlsx'!DataSet,,MATCH(B$1,'2016 DeconflictorA.xlsx'!Dates,0)+COLUMNS($B$4:$C$4)-1)), ROW($B$3:$B$28)-2), ROWS($B$4:$B4)),MATCH(B$1,'2016 DeconflictorA.xlsx'!Dates,0)+COLUMNS($B$4:$C$4)-1),"")

Where DataSet is =Data!$O$3:$IV$28 in Deconflictor

[Hambone70]

Fantastic! That works beautifully. I'm dissecting it in order to learn how it works.

I only have two more requirements. First, the weekly schedule needs to ignore any employee with "Indirect" in the reason column.

Also, the weekly schedule needs to look at the corresponding 5-day period in the deconflictor. So, when I CTRL-drag the 1-5 JAN worksheet to the right and rename it 8-12 JAN, it extracts the 8-12 JAN data from the deconflictor.

Thanks for the help!

[jaslake]

Hi Hambone70

Sorry...must have missed your requirements.

[Hambone70]

Jaslake - thank you very much for that. There's some serious VBA going on there! However, I don't think it quite meets our requirement. We need the link between the deconflictor to be 'live'. Any change in the deconflictor is immediately reflected in the appropriate weekly schedule.

ChemistB - your solution is almost there, with two alterations. First, any reason marked as 'Inactive' should not be transferred to the weekly schedule. All other reasons should be reflected. Second, the deconflictor needs to be reflected in the appropriate weekly schedule. So the weekly worksheet 1-5 Jan looks at 1-5 Jan on the deconflictor, worksheet 8-12 Jan looks at 8-12 Jan on the deconflictor, etc. We build the month a week at a time, and when the month is over, we archive that month. As in the attached schedule shell, naming the tab of the weekly worksheet automatically populates the dates on the 5 days of that week. Could some kind of lookup reference the days, as to extract the corresponding data from the deconflictor?

Thanks to everyone for their time!

[Hambone70]

Any help? ChemistB's solution is almost there.

I have authorization from work to purchase commercial credits to help finish this project, if anyone is interested.

Thanks!

[Hambone70]

Hmmm... no takers.

Any recommendations on where I can source online paid Excel assistance?

Thanks!

[ThePlanner]

Just saw this solution to naming the worksheets in another post this morning. Put this in cell a1
=MID(CELL("filename",A1),SEARCH("]",CELL("filename",A1))+1, SEARCH("]",CELL("filename",A1))-SEARCH("[",CELL("filename",A1))-1)

then you can change the schedule names with a text formula, which I will go away and try too figure out. Does the format have to be as you described above i.e. " 1-5 Jan" etc

edit:
On closer look at your sheet you already had that and the date formulas that I was going to look at.. I'll just watch for a while

[oeldere]

I realize you want a solution to what you perceive as the easiest route, but because you are unfamiliar with other functions and features of Excel, you do not seem open to re-thinking your problem.

As already told, there is no need to use merged cells, and they will cause a lot of trouble sooner or later. In your case sooner.

Why not all data of all dates for 1 month in 1 sheet. (instead of making 1 workbook with 4 sheets).

Other suggestions:
Avoid inter-workbook links wherever possible: they are slow, easily broken, and not always easy to find and fix.

[JohnTopley]

Contact "Commercial Services" ( top tab on FORUM)

[Hambone70]

OK... I'm very close to having this working!

The last step is to delete the names and reasons for the names that do not have reasons, as per the attached sample workbook.

[JohnTopley]

In D2

=IFERROR(INDEX(A$2:A$9,SMALL(IF($B$2:$B$9<>"",ROW($A$2:$A$9)-ROW($A$2)+1,""),ROWS($A$2:A2))),"")

Enter with Ctrl+Shift+Enter

copy across and down

[Hambone70]

Thank you, John.

Project complete!