Auto update daily report problem

[SteveG]

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 I1100 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

 

[vezerid]

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

 

[SteveG]

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

 

[SteveG]

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

 

[vezerid]

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

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


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

1. You can use VLOOKUP() inside the OFFSET function

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

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

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

But whether you choose either depends on your needs.

Regards

Kostis