 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / October 2006Linking Sheets
Wasn’t sure what to put in as the subject. Sheet 1 is my compiled data sheet. I have 12 other sheets with each representing that months data. Sheet 1 is linked to each months sheet picking up all the totals for that month and putting them in a table. Eg. Sheet 1 represents period 1 Sheet 2 represents period 2 and so on In sheet 1 I have a number of lines referring to the end of period or has the end of month date Example This data represents ……………. to the end of period 3 of FY 06-07 Comparison data for period 3 FY 05-06 or This data ………. to 30 September 06 Instead of having to go through and manually change all these dates and period numbers in sheet 1 at the end of each month I want to know how I can have these lines automatically update at the end of each period. Some thoughts to get this phrase done up auto: > or "This data ………. to 30 September 06" Assuming the phrase is to pick up data in A2 and then concat it with the last day of the current month, then something like: ="This data " & A2 & " to " & TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1,"dd mmmm yy")  SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > Wasn’t sure what to put in as the subject. > [quoted text clipped - 18 lines] > period numbers in sheet 1 at the end of each month I want to know how I can > have these lines automatically update at the end of each period. Thank you Max. That worked fine. Any ideas on how to have the period number update. I'm sorry as i am quite a novice when it comes to excel. > Some thoughts to get this phrase done up auto: > > or "This data ………. to 30 September 06" [quoted text clipped - 26 lines] > > period numbers in sheet 1 at the end of each month I want to know how I can > > have these lines automatically update at the end of each period. > Thank you Max. That worked fine. Glad to hear that. > Any ideas on how to have the period number update. One way to get the period # for this kind of line auto: >> .. "Comparison data for period 3 FY 05-06" ="Comparison data for period "&VLOOKUP(TEXT(TODAY(),"m")+0,{1,1;4,2;7,3;10,4},2,0)&" FY 05-06" I've assumed period 1 = Jan - Mar, 2 = Apr - Jun, 3 = Jul to Sep, 4 = Oct SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- Correction to earlier formula. It should be: ="Comparison data for period "&VLOOKUP(TEXT(TODAY(),"m")+0,{1,1;4,2;7,3;10,4},2)&" FY 05-06" SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- Last line was left inadvertently incomplete. It should read as: > I've assumed period 1 = Jan - Mar, 2 = Apr - Jun, 3 = Jul to Sep, 4 = Oct - Dec SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- Hi Max, sorry for the delay in getting back to you, i got a bit busy with other stuff and now there are a million other posts that you are probably busy with. That formula is doing what i want it to do and as hard as i study it i can't see how you have done it. I did attempt to change the formula myself but all i did was mess it up because i had no idea what i was doing. My sheets work on periods per month. For this example we can say they are pay periods. I am working on the Australian financial year calendar. eg. Pay Period 2 (July) would cover the dates 12/7/2006 and 26/7/2006 Pay Period 4 (August) would cover dates 9/8/2006 and 23/8/2006 Pay Period 6 (September) would cover dates 6/9/2006 and 20/9/2006 Pay Period 8 (October) would cover dates 4/10/2006 and 18/10/2006 Pay Period 11(November) would cover dates 1/11/2006 and 15/11/2006 and 29/11/2006 (the period jumps from 8 to 11 because there are 3 pays in that month and so it goes on right up to period 26 (June 2007) I hope this makes sense Max. Thank you so much for your assistance. Just quickly, why does the formula put that little box in front of the period number? > Last line was left inadvertently incomplete. > It should read as: > > I've assumed period 1 = Jan - Mar, 2 = Apr - Jun, 3 = Jul to Sep, 4 = Oct - Dec Hi Lynda In case Max has missed this, as he would normally be on line before now. > Just quickly, why does the formula put that little box in front of the > period number? It's probably an Alt+Enter or line feed that has been inserted in the formula to wrap it in your email client. In the formula bar, if the text is wrapped onto 2 lines, just delete at the end of line 1 to bring it onto a single line and the character should disappear. To deal with your period dates, set yourself up a table such as 11/07/2006 0 26/07/2006 2 23/08/2006 4 . . 26/06/2007 26 Name this table as Dates using Insert>Name>Define Then amend Max's formula to ="Comparison data for period "&VLOOKUP(TODAY(),dates,2)&" FY 06-07" For me it returned Comparison data for Period 8 FY 06-07 SignatureRegards Roger Govier > Hi Max, sorry for the delay in getting back to you, i got a bit busy > with [quoted text clipped - 32 lines] >> > I've assumed period 1 = Jan - Mar, 2 = Apr - Jun, 3 = Jul to Sep, 4 >> > = Oct - Dec Roger, many thanks for the help ! Lynda, you're in good hands with Roger's response SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- Thank you Roger that worked. Thank you also Max for fixing the first part of my problem. Keep up the good work guys your assistance is invaluable for dummies like me. > Roger, many thanks for the help ! > > Lynda, you're in good hands with Roger's response Lynda You're very welcome. Thanks for the feedback, but all credit is due to Max, who, I'm sure ,is very appreciative of your comments. SignatureRegards Roger Govier > Thank you Roger that worked. > [quoted text clipped - 6 lines] >> >> Lynda, you're in good hands with Roger's response |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.