Auto update daily report problem

Hi All,

I have a workbook that has a RollUp sheet that pulls in data for each workday of the month. The sheets that RollUp pulls from are named Day1, Day 2, Day3 up to the last business day. The headers in the RollUp are assigned the names Day, 1, Day 2 etc.. as well. I then have a sheet that pulls certain data from the Rollup sheet data to be presented as a daily report. So for Day 1, it would pull from A1:H100 in the RollUp sheet, Day 2 pull from range I1:P100 and so on through to the last business day. The row references are always constant but the columns shift for each new day. Currently, the report have basic formulas like =RollUp!A1 for each piece of data to be retrived from RollUp that have to be changed each day to accomodate the shift. What I am looking to do is to write a formula or code that would make the formulas shift over the correct number of columns automatically when the day changes. I thought of creating a table that would return what the workdays were in date format and then using a conditional IF, COUNT with maybe INDIRECT to have the column reference shift but can't seem to get throuugh it. If an example sheet would help, I could provide one. Any ideas are much appreciated!

Thanks,

Steve

Steve,

INDIRECT() seems to be what you are looking for, possibly in
conjunction with VLOOKUP(). For example you could have a master table
Day, From, To and entries like Day1, A, H.

Then you could do something like:

=INDIRECT(VLOOKUP(day, table, 2, 0)&"1:"&VLOOKUP(day, table, 3,
0)&"100")

In a similar spirit you can consider OFFSET(), if all columns are in
the same sheet. In this case VLOOKUP() can supply the offset for the
column, i.e. 3rd argument of OFFSET().

Does this help?

Kostis Vezerides

Kostis,

Sounds like you an I are thinking along the same lines. Table with lookup, indirect or offset to change the . I'll play around with it and give it a shot and see how it works.

Thanks for your help!

Steve

Kostis,

With some effort and ideas from you, I think I've got my solution. In a table to identify today's value (Day 1, Day 2....) is used this formula in A2 where A1 is the last day of the previous month. Copied down, it gives me each working day of the month excluding the holiday in cell J5. I set up a table for each month's holidays to make it easier to reference.

=IF(ISERROR(workday(A1,1,$J$5)),"",IF(MONTH(workday(A1,1))<>4,"",workday(A1,1,$J$5)))

In column B I assigned the # 1-23.

In my report formulas, I used,

=CHOOSE(VLOOKUP(TODAY(),A2:B24,2,FALSE),OFFSET(Sheet2!A1,1,4),OFFSET(Sheet2!A1,1,4),OFFSET(Sheet2!A1,1,4),OFFSET(Sheet2!A1,1,4),OFFSET(Sheet2!A1,1,4),OFFSET(Sheet2!A1,1,4),OFFSET(Sheet2!A1,1,4),OFFSET(Sheet2!A1,1,4))

Of course, this is for an example so all the OFFSETS are the same so I could test it quickly. The CHOOSE will be able to support the 23 days of changes.

Since I have to set up a new copy of this workbook for each month, by simply changing the date in A1, my report formulas don't need to be adjusted.

Not sure if this is the most efficient way but it seems to work. Thanks again for helping me get a focus on what direction I needed to go in.

Steve

Steve,

Glad to know it is working. I am addressing your musing if there could
be a more efficient way.

It seems to me you want to produce the valid working dates in column
A:A. Your formula seems to suspect you might get an error using workday
(why? - I don't see a plausible reason) and then produce a sequence of
workdays IF it is April. My guess here is that you are supplying the
number every month to exclude days of the next month. If so, your test
could be

=3DIF(ISERROR(workday(A1,1,$J$5)),"",IF(MONTH(workday(A1,1))<>MONTH($A$1),"=
",workday(A1=AD,1,$J$5)))


Regarding your CHOOSE(), and not knowing your exact intentions, I can
suggest the following:

1=2E You can use VLOOKUP() inside the OFFSET function

=3DOFFSET(Sheet2!A1,0,VLOOKUP(TODAY(),A2:B24,2,FALSE))

2=2E You can avoid the tabulation/vlookup function altogether using the
inverse function of WORKDAY, which is NETWORKDAYS. Thus you could have
something like

=3DOFFSET(Sheet2!A1,0,NETWORKDAYS(Sheet2!A1,TODAY(),$J$5))

But whether you choose either depends on your needs.

Regards

Kostis