Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatic Date if data entered in sheet
I have a book with 32 sheets. One for each day (31 days) and a monthly roll-up sheet. In my monthly roll-up sheet i would like to have an automatic "date range" in cell A2. The date range will always begin with the first day of the month but it will end with whatever date (sheet) is the last updated. I mean, if I have data on all my sheets from June 01 to June 25 but June 26 - June 30 are blank (and the 31st sheet is blank too), the date range should say "June 01 - June 25" and each day it will get updated when data is entered into a sheet Does that make sense?? Any ideas will be greatly appreciate it! Cheers, Mayte |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatic Date if data entered in sheet
Assuming you fire from the Summarysheet. Sub backsardssheet() For i = Sheets.Count To 2 Step -1 If Sheets(i).Range("d2") 0 Then Exit For End If Next range("a2")= "June 1 - " & Sheets(i).Name End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... Change the d2 to a cell you want to check on each sheet. However, I see no reason not to have all on ONE sheet with a date column and use datafilterautofilter to see the individual days. Lots of advantages. Sub backsardssheet() For i = Sheets.Count To 2 Step -1 If Sheets(i).Range("d2") 0 Then Exit For End If Next MsgBox "June 1 - " & Sheets(i).Name End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Mayte" wrote in message ... I have a book with 32 sheets. One for each day (31 days) and a monthly roll-up sheet. In my monthly roll-up sheet i would like to have an automatic "date range" in cell A2. The date range will always begin with the first day of the month but it will end with whatever date (sheet) is the last updated. I mean, if I have data on all my sheets from June 01 to June 25 but June 26 - June 30 are blank (and the 31st sheet is blank too), the date range should say "June 01 - June 25" and each day it will get updated when data is entered into a sheet Does that make sense?? Any ideas will be greatly appreciate it! Cheers, Mayte |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatic Date if data entered in sheet
Not sure how to tell if a sheet is empty on purpose or because it has not been used. However, this would provide an as of type date, based on the current date. =TEXT(NOW(), "mmm") & " 01 To " & TEXT(NOW(), "d") "Mayte" wrote in message ... I have a book with 32 sheets. One for each day (31 days) and a monthly roll-up sheet. In my monthly roll-up sheet i would like to have an automatic "date range" in cell A2. The date range will always begin with the first day of the month but it will end with whatever date (sheet) is the last updated. I mean, if I have data on all my sheets from June 01 to June 25 but June 26 - June 30 are blank (and the 31st sheet is blank too), the date range should say "June 01 - June 25" and each day it will get updated when data is entered into a sheet Does that make sense?? Any ideas will be greatly appreciate it! Cheers, Mayte |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatic Date if data entered in sheet
thank you both !!! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatic Date if data entered in sheet
TRY the autofilter -- Don Guillett Microsoft MVP Excel SalesAid Software "Mayte" wrote in message ... thank you both !!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using a Macro in Excel 2004 to move entered data from one sheet toanother and space between rows when next data is entered? | Excel Programming | |||
Macro to record date on sheet data is entered in a cell ? | Excel Programming | |||
excel, automatic date and time when info gets entered | Excel Worksheet Functions | |||
can entered data in sheet 1 be automatically pasted in sheet 2 | Excel Discussion (Misc queries) | |||
Validating entered data as date | Excel Programming |