Bob & Frank,
Sorry for my novice brain with Excel.
1. 1 Folder and within this folder I have:
2. Workbooks (8 total)
3. (1 of 8) is Startday, A1 changed to: Startday =11/01/04
4. (OK)
5. Workbook (2 of 8) is Cash Payout
6. This workbook has 12 worksheets Ex: Nov (04) Dec (04) Jan (05) etc.
7. Nov (04) worksheet was a paste special from Startday workbook:
8. A5 ='C:\My Documents\Excel Templates (Master Copies)\[(PS) Start
Date_Master.xls]Startday'!$A$1
9. OK: Now I see the date: A5 =11/1
10. A6 =IF(A5="","",A5+1) now I see A6 =11/2
11. I drag the fill bar to A35 now I see A35 =12/31 Stopped here because
some months have 31 days.
12. OK
13. I copied this worksheet and named it Dec (04) This is identical to Nov
(04)
14. Now in A5 I placed the formula:
15: A5 now is: Startday =--("2004" & "-"
&LEFT(MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255),3) &"-1")
16. Did I take the breaks out correctly ????
17: Then for every worksheet I place the formula above to change to the
current date ??
Thank you both,
Myrna
Bob Phillips said:
Myrna,
No the breaks I was referring to are line breaks, which you won't see as a
character, but in the formula bar it will span 3 lines. The & in Frank's
formula are necessary for it to work.
--
HTH
RP
(remove nothere from the email address if mailing direct)
Myrna said:
Bob,
I pasted the formula to note pad. Brought it on one line.
Are the Breaks the &????
Bob Phillips said:
You should not change Filename, it is just telling Cell function what
attribute it wants.
Frank's formula, as appeared in the NG, spanned 3 lines. Make sure that you
get it all onto the one line, remove the breaks.
--
HTH
RP
(remove nothere from the email address if mailing direct)
Frank,
Had troubles with your formula.
Copied Nov A34
Paste Special to Dec sheet in A5
This was the link: ='Nov (04)'!$A$34
Place a +1 on end: ='Nov (04)'!$A$34+1
Since I copied Nov worksheet to make Dec (identical format)
The Date and year 12/1 and all the data changes below.
Question: Is this the hard way of doing it????
Great Wed showed up.
Dragged my fill down and worked great.
:
Hi
try the following formula in cell A1:
=--("2004" & "-" &
LEFT(MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255),3) &
"-1")
--
Regards
Frank Kabel
Frankfurt, Germany
Hi,
I have a seperate file with the current day set.
I created a folder for Payroll
I created a file called Payroll
My November (04) worksheet has the start day inserted.
A1 = Start_Day inserted correctly.
A2 cell has =IF(A1="",""a1+1)
Great I dragged A2 to A34
Worked perfect.
There is some month with 31 days this is why I stopped here.
I copied this spreadsheet and made a workbook. Now called Dec (04)
How do link the start day with this new month for Dec.
Myrna
