Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=510633 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto update daily report problem
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto update daily report problem
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 ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=510633 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto update daily report problem
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(workda y(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(She et2!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 -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=510633 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto update daily report problem
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(workda y(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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using "if" equation that requires auto update | Excel Discussion (Misc queries) | |||
auto update of copy of workbook | Excel Worksheet Functions | |||
update links - problem | Excel Discussion (Misc queries) | |||
Update Links - Problem | Links and Linking in Excel | |||
problem in retrieving data from excel report | Excel Discussion (Misc queries) |