Excel 2010: Automatic Date Filing

Hi,

I am currently putting together a database. One of the functions i would like to include is a formula that date orders each data line i input. So when i put a line into a field it automatically allocates it to the appropriate line. Any help would be much appreciated.

Dan

Dear Daniel,




Welcome to Excel forum


can u make it bit clear?if possible with example.

Hi, thanks for the reply. In my database I have seperated each week of the year so I can view my transactions week by week. So rather than input all the data manually, what i need is a way to input the data row in one place and for it to be moved to the appropriate week in accordance with the delivery date. So i wouldnt have to scroll down to the week-section to input the data.

Thanks for your time

Dan

hi
in you case i suppose when you want to enter a transaction, you would be making a entry in the week you want. in that case suppose every time u get new entry u enter in A1 then in B1 you could have =IF(A1<>"",NOW(),0)
what it would do is it wld get the date when u made entry. then run a simple macro to copy the value to the week column when u enter a value and it would keep updating it

Hi Vikas,

Thanks for the response, that looks like a very promising option. What macro should i use to copy my data entry into the specific week? I understand what you have initally put which would allocate the time/date of entry to the line. But now i need to know how to sort it by appropriate date order.

Thank you for your time.

Dan

here is the code, u can edit based on your spreadsheet
u can crate a button and add this macro
and run when u input any new entry in top clumn, so when u change the entry at different dates then u need to click macro button u created

on the other hand if u donot put values in the same week then with each new entry u can enter date where u want it to go in stead of formula from previoous post

Sub datecopy()

Dim i As Integer


'GIVE 'i' one plus STARTING FROM ROW NUMBER FROM WHERE YOUR DATE BEGINS AND END TO WHEN DATE ENDS(so if your date starts from row 10 then i wld start from 11
'cells(4,3) would be the cell where we get current date when we input new entry
'cells(i,1) and cells(i-1,1) are the consecutive dates that you have

For i = 3 To 50
If Cells(4, 3).Value > Cells(i - 1, 1).Value Then If Cells(4, 3) <= Cells(i, 1) Then Cells(i, 2).Value = Cells(4, 4).Value
Next

End Sub


P.S. please click start but for me if I was helpful

Hi,

I hope you had a good weekend?

I am putting together the formulas now. To be honest im pretty unclear as to how to input the macro formula you have suggested above. It looks like a good idea and im up for using it. But i dont know how to put it in any help would be much appreciated.

Dan

hi,

its simple. go to file menu of excel, go to options, click cutomize ribbon, in right panel check developer, that would add an xtra developer tab in excel
then go to developer tab, click insert in developer tab and wld show different buttons, select a button and create anywher ein excel sheet by sizing the button, as u create a button a new window will come up asking about macro, give a name to macro and click new
that will give you window to enter macro, once u enter the code that I gave, just manipulate some terms based on my explanation in previous post for your columns and rows.
so after you enter the code (which should b in a format mentioned above) you just have to press the button that u made to get your formula working

Hi, I am attempting to fill in the macro field (with no success as of yet). Unfortunately it is something that i have very little experience in so i am having to teach it myself as i go. I am inserting an image of a test data sheet. If you could help me with coding the macro for it i would be really grateful. What i need is the code to copy/paste into the new macro field in between the lines; Sub datecatagory() and endsub.
exceldata1.png

Please see attached the image of my sheet. What i need is so when you press the "Enter Data" button at cell F4, the row in the input field -cells B4:E4- would be copied to the apprpriate week based on their date. I have initally just put 3 weeks in , but this will be for the full year.

Thanks

Dan