PDA

View Full Version : Macro to calculate values based on a column



sindhuja
01-23-2008, 06:29 PM
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

lynnnow
01-24-2008, 05:02 AM
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...

sindhuja
01-24-2008, 09:09 PM
Hi,

I am unable to see the attachment.
Not sure why ?

Regards,
Sindhuja

sindhuja
01-24-2008, 09:32 PM
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

lynnnow
01-24-2008, 10:36 PM
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

sindhuja
01-25-2008, 07:47 PM
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

lynnnow
01-25-2008, 11:45 PM
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?

sindhuja
01-27-2008, 05:54 AM
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.