-
Macro to calculate values based on a column
Hi All,
I have a sheet with numerous data.
i need to calculate the no of days till today with respect to the specific column.
For ex, i have a column named "Date of Entry" with a value 12/31/2007. Now another colum named "Difference" where i have to calculate the difference between the todays date and the 12/31/2007. Date of entry colum will be of different dates.
i also need to pick up the values like Items greater than or equal to 30 days, 20 days, 10 days, 5 days, day 2 items and day one items. These values to be stored in a concerned columns in Sheet 2.
Is this possible using macros.. Also attached the sample file for reference.
Any help will be highly appreciated !!!
Regards,
Sindhuja
-
Have you tried =COUNTIF(Sheet1!$F$2:$F$21,Sheet2!D2)? For the difference between the days, you can still use the formula you have used. I find this a little lighter on the workbook.
I've attached your workbook with the formula...
-
Hi,
I am unable to see the attachment.
Not sure why ?
Regards,
Sindhuja
-
Hi Lynn,
Thanks for the help !
Works fine. Just want to add up some informations..
The rows in sheet 1 is not a constant.Varies from day to day.
Everyday i need to append the values in sheet 2 [counts from sheet 1]
Can this be done using macros. If it by macros willl be highly helpful !!!!
Regards,
Sindhuja
-
Hi Sindhuja,
I've given the range for example, you can extend the range to blank cells for now and append sheet 1 with your data. The formulae in sheet 2 will take care of the calculation.
HTH
Lincoln
-
Hi Lynn,
I am ok with that.
The data in Sheet 1 varies. For ex in day 1, calcuations done on sheet 2 and saved. Now on day 2 i will delete datas in sheet 1 and give new values.
Will this activity affect the data in sheet2. If not i want to produce the day 2 calculations in the next row of Sheet 2...likewise appending calculations daily in the sheet2.
Thanks,
Sindhuja
-
If you delete the data from sheet1 then the values of sheet2 will be reset. However, the data you provided has an "entry date" column. Is this the date you are talking about? If not, you can add another column to mention the actual date you are entering the records and we can go further from there, wot say?
-
We use "date of entry" column for our calculation stored in sheet 2 based on that column. want to save the datas daily in sheet 2. Restore values atleast for a month.
if we use seperate column named actual date value in that will be todays date. I am not sure how a new column insertion will help us out.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules