han77729
New member
- Joined
- Dec 12, 2012
- Messages
- 31
- Reaction score
- 0
- Points
- 0
Jeff,
The revised version works perfectly. Where can I view the hard coded formula/commands you've put in place?
Thanks for all your support.
Han
The revised version works perfectly. Where can I view the hard coded formula/commands you've put in place?
Thanks for all your support.
Han
Also, see if the issue is still happening on the attached file. View attachment 1553.
Apparently some weird stuff can happen to formulas where the column headers contain dates. So I put a 'helper' column in above the column headers, to see if it makes any difference.
EDIT: I just changed my date settings to US format, and this approach seems to work fine.
The issue is that for some obscure reason, if you enter say 1/1/2013 into a table column header, Excel stores it as a string, not a date. If you then tell excel "Apply the date format of MMM-YY to this", then Excel displays 'Jan-13' (which is what we want) but when it converts the string to a date using any of the date-related functions, Excel thinks that this number for some reason relates to the 13th of January 2013.
Way around it is just to have some helper cells one row above. Because they are not table column headers, they are stored as dates, not strings.
Note that you don't need these helper cells in the summary tab, because that summary table doesn't contain any formulas. Also note that no-one will see this extra helper row, because the month-by-month breakdown is only required to be visible in the summary sheet...i.e. you should hide columns T onwards in the data input sheets, like in this version. View attachment 1554