Function similar to INDIRECT() ?

[creynolds722]

Hi!
Long drawn out question ahead, please bear with. Two paragraphs of setup to get you in the mindset and then the problem/question. Alternate suggestions appreciated. No macros available.

I have a time worked pseudo calendar. Headers in A1:E1 of "Day" "Start time" "End time" "Lunch" "Worked".
A2 just has the word "Week1", B2:E2 are blank. A3:A7 has dates, 3/11/13 through 3/15/13. B3:B7 and C3:C7 has times such as 08:50:00 AM and 05:00:00 PM. D3:D7 has an optional lunch time taken, either blank or a time such as 00:30:00. E3:E7 has total time worked as in (((C3<B3)+C3-B3)-D3). That gives 08:10:00, the comparison is to account for ranges that pass midnight(none do, but just in case). A8 has the word Total, B8 and C8 are blank, D8 has the sum of lunch times such as 01:01:04, E8 has the sum of time worked such as 40:20:17. The next 7 rows are a repeat of the last, Week2, dates of Monday-Friday, Totals etc etc etc. All this is good and proper.

I have a table off to the side that tabulates the totals. G12 is blank, H12 says "Lunch/Week", I12 says "Worked/Week". G13 says "Week1", H13 says 01:01:04 (from before), I13 says 40:20:17 (from before). The next row is Week2 , time, time, etc. The times are found by this formula: =IF(OFFSET($E$1,(ROW(E1))*7,0)=0,"",OFFSET($E$1,(ROW(E1))*7,0)) . This formula says that if the cell it's looking at is blank, print a blank, otherwise take the next 7th value from the first table and display it because my total times are every 7 rows. All is well and good, working fine.

PROBLEM/QUESTION:
I am now getting to the point that 10 weeks are being populated and I don't want to scroll to the bottom of the list to put in new times. So, I have flipped my table such that week1 is at the bottom and week 9 is at the top, and next week I will physically xcopy paste the cells down 7 rows and start a new week. I don't want to alter the 2nd table every time I add a section to the first table, though. If I kept the 2nd table as is, it will be reversed, showing week9's time as week1, etc. I can't figure out how to set up the offset such that it starts at the bottom and goes up.

I have a function in cell G9 that gets the last used row in the first table: =("E" & COUNTIF(E1:E1000,"<>")) . That spits out E64 is the currently last used row, which now that I flipped the table contains the Totals from week1. I think the solution will have something to do with an INDIRECT(G9) perhaps, since it changes with the last populated row, the 2nd table will change with it. I can't get it to work though.

Thanks for any help and sorry about such a long winded question!

[martindwilson]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[creynolds722]

Hi MartinDWilson, thanks for the quick reply. Here is a before and after of my sheets, the before works perfectly, just have to scroll down for new weeks. The after has the weeks in the newly desired order, but the recap table has the times reversed because I haven't figured out a formula to do it backwards.

The extra bits to the right of week 9 on both sheets are the times I'll need to come in to hit 40 hours :D just ignore those.

No data is really private here so I included it all.

[martindwilson]

=IF(ROWS($E$1:E1)>COUNTA(E:E)/7-1,"",OFFSET(INDEX(E:E,1),COUNTA(E:E)-1*ROWS($E$1:E1)*7,,1))
will give the same result as
=OFFSET($E$1,(ROW(E1))*7,0)
but in reverse order

[creynolds722]

Thanks so much MartinDWilson. It's not hitting exactly the right rows but I think I can tweek it from here. The first total should be from E64 but it's hitting E45 and doing every 7 before that.
Thanks again!