Example: Horizontal date entry formula

[cmissal]

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

 

[Hui]

A small improvement could be:

A1: =adminReportStartDate

B1: =IF(MOD(COLUMN()-1,3),"",IF(MAX($A$1:A1)+7>adminReportDate,"",MAX($A$1:A1)+7))

Copy B1 accross


That takes it from 213 to 78 characters

 

[cmissal]

Thanks Hui!


I updated the .xlsx file to include your improved approach too!