formula for chronologically aligning days of the week

[allgeef]

Excel 2007

I have a workbook with 8 tabs. The first 7 tabs are the days of the week Mon thru Sun, for workers to record hours of work. There are rows for up to 25 workers, but any given day there will be anywhere from 2 to 25 workers. The 8th tab is an invoice tab to send to clients. The invoice tab has formulas to draw names and hours from the 7 daily tabs.

My question is there a formula or manner that regardless of how many workers are working on Monday, the Monday names and hours would populate the invoice tab first. This would proceed accordingly through the week in that each day would automatically fill the invoice page rows prior to the next day starting to fill rows. If there are 9 on Monday, 5 on Tuesday, 15 on Wednesday, etc. all names and hours would fill the invoice rows in order of Mon thru Sun regardless of the number of workers on each day.

Thank you for your consideration.

[bentleybob]

It would be fairly easy if you can post the file. But to clarify: if the same person is on the list for more than one day, does the name appear multiple times, or do you also need to consolidate them?

[allgeef]

Yes, the same person may appear on multiple days and would have to be recognized on each day appeared. If that response helps I'll look for what the answer might be, otherwise if more info is needed I will attempt to post the workbook. Thanks for the help.

[bentleybob]

As I understand it, you don't want duplicates on Tab 8. That just adds a bit of complication, but is not impossible. Yes, would help if you attach the file.

[allgeef]

bentlybob,

There are more tabs on the attached file than the 8 I have the question on. The seven day tabs are clearly marked by day. The tab intended for the names and numbers to go to is called "tsec labor". The names and hours listed are simply random entries by me to attempt to find a solution. The other tabs are part of the complete system, but not involved in this question. Thank you again for considering my question.

[bentleybob]

Attached is a partial solution. It pulls in information as needed, but this version shows multiple records (i.e., the same name appearing more than once) as separate lines. I wanted you to see this before going any further in case this isn't what you had in mind. Sorry, but I think we're up against the forum file size limit so I had to delete the other tabs to get this to you.

You will note that I added some helper formulas, highlighted in bright yellow, including two lookup tables in rows 38-39 of the tsec labor tab. You could probably get around those, but it would be much easier to simply move them out of the way. You will also have to modify some of your formulas to deal with blanks. And I didn't address your page breaks in the tsec tab, but those should be easy to deal with.

[allgeef]

This is really nice. This work alone will save us an enormous amount of time. The helper formulas should be not issue, in that they could be hidden or moved.

A wish list would include:
1. An automatic one row space between days on the "TSEC labor" lines. That is Tuesday would begin with a blank row between Monday and Tuesday.
2. There are times that days are skipped, as in working on Monday, Tuesday, skipping Wednesday and Thursday, then back to work on Friday. It seems like the present setup will not recognize days following a skipped day. Is there a means of getting around this?
3. Thanks for addressing the page break challenge. We did not know how to deal with a break and imagined having to merge all the pages together to make something work.
4. Eventually we will try to do the same thing will material, but that will be another issue in the future. Material would include the same item (example an "insulator") listed on different days but only recorded once on the material page with the quantity being increased as more are added.
5. We will ultimately duplicate your work on the other companies. As you could see on the first file I uploaded there are four separate, yet related companies. My guess is we would simply mirror what you did here for the other companies. Would I be correct in that conclusion?

Thanks again for the wonderful service.

[bentleybob]

#1: I'm sure it's possible, but I would wait until everything else works before making formatting changes.
#2: Yes, that would be a problem but there undoubtedly is a workaround.
#3: I would normally recommend you put it all together and use Print features to make the formating work. It's much simpler, cleaner, and avoids issues of having to address each and every page break. Plus, if your workload expands, you don't have to manually add new pages; just copy formulas down.
#s 4 and 5: I would think you could use the same logic for both of these.

[bentleybob]

The attached addresses #s 2 and 3 (although I only copied the formulas down through the third page). Please note the new formulas in the table at B38:O39.

[allgeef]

I have begun working through your newest additions; this is going to be a wonderful tool.

I must say, I liked your earlier idea of "I would normally recommend you put it all together and use Print features to make the formatting work. It's much simpler, cleaner, and avoids issues of having to address each and every page break. Plus, if your workload expands, you don't have to manually add new pages; just copy formulas down." Each of these points is clear and will make the total workbook (with so many tabs) much easier to work with.

I have copied your formulas down to the bottom of the pages, populated various names, inserted different hours, and randomly skipped days; the program works just as I had envisioned.

If point #1 is still a possibility, of auto skipping a row between days, that would be nice but not an absolute necessity.

I am going forward with incorporating these formulas into the entire workbook. In case I encounter any issues can we keep this dialogue open until the program is complete and ready to launch?

[bentleybob]

Glad it is working as expected. I will think about how best to implement #1. Shouldn't be too difficult, but might require another lookup table.

Yes, just post to this thread if you have further questions.

Regards.
bb

[bentleybob]

Ok, this includes #1. The green highlighted areas have the changes. There is also a new table at AD38:AJ40 which, of course, you can move out of the way. I did note that if you don't have any entries on Monday, you get a blank line at the top, but I figured you could probably live with that.

Enjoy.
bb

[allgeef]

bentleybob,

We are about ready to launch our new workbook. I intended to upload the final product so you could see the end result of the workbook you assisted in, but the book is just over 2MB. Thank you much for the advice and help. This book is going to prove of great help to us.

Regards,
Gary