I started to create a horizontal log file that will have 3 values per log entry and unlimited log entries for the 'reporting period'. I abandoned this and returned to a vertical approach... I was really just doing this to learn.
If anyone finds it interesting, I'm glad.
If any of you more advanced excel gurus have a better approach to populating the column headers I would be your humble student!
I entered the following in the first row, column A through HZ (to allow for 3 years of weekly log entries)
=IFERROR(IF(COLUMN()=1,adminReportStartDate,IF(OR(COLUMN()=2,COLUMN()=3),"",IF((COLUMN()-1)/3=ROUND((COLUMN()-1)/3,0),IF(OFFSET(A1,0,-3,1,1)+7<=adminReportDate,OFFSET(A1,0,-3,1,1)+7,""),""))),"")
A sample of the file can be downloaded here.
http://cid-06d2dcf988984b17.office.live.com/view.aspx/.Public/Sample%20Horizontal%20Log.xlsx
Casey
If anyone finds it interesting, I'm glad.
If any of you more advanced excel gurus have a better approach to populating the column headers I would be your humble student!
I entered the following in the first row, column A through HZ (to allow for 3 years of weekly log entries)
=IFERROR(IF(COLUMN()=1,adminReportStartDate,IF(OR(COLUMN()=2,COLUMN()=3),"",IF((COLUMN()-1)/3=ROUND((COLUMN()-1)/3,0),IF(OFFSET(A1,0,-3,1,1)+7<=adminReportDate,OFFSET(A1,0,-3,1,1)+7,""),""))),"")
A sample of the file can be downloaded here.
http://cid-06d2dcf988984b17.office.live.com/view.aspx/.Public/Sample%20Horizontal%20Log.xlsx
Casey