My data comes from MS Access as an export fille (.xls) wherein I have a column which contains all names redundant. I want to find the count of the Unique records only for which I have written a macro in the 'Open' event of the workbook which filters the unique records and gives the count of records, the count value in turn would be stored in the corresponding field in excel table.
The problem is that I have written a COUNT formula in another page which is referenced to the filed in the excel table in which am tracking the metrics from the access-exported file. All the rows have the same reference as the value for most of the time remains static (everyday).
But as a contigency, what if the value changes one day, say one more person is being added to the list and list count becomes previous count+1.
But as per the formula, every cell changes its value to the new value
(if previous value=n, and the new value is n+1...all cells show n+1, where as I want only those cells,the date on which n becomes n+1 to show n+1 value and the rest to be n)...
How this can be avoided..??
One solution is to PASTESPECIAL AS VALUE in every cell of corresponding field of that metric table instead of direct reference...but is there any better solution..??
Note: the metric table measures every day values....to be more narrower, it is almost like an online system wherein once the export file is saved onto the said location, the metric table would display the values as of that time...
THE SECOND PROBLEM: The data that comes from Access has some columns which contains dates. I have to count some fields based on their individual criteria (like "Yes", or "No") on a given date. Am using SUMPRODUCT to calculate them.
The problem is: the dates the comes from Access to Excel is in General format and not in Date format. The date in Excel table is not recognising the date from Access exported excel file as both the formats are different. I tried changing the date in my metric table to number by using the formula n(value), tried changing to text by using Text (date, "000000") but there is no use of it. Any help on format recognition is also most welcome.
The problem is that I have written a COUNT formula in another page which is referenced to the filed in the excel table in which am tracking the metrics from the access-exported file. All the rows have the same reference as the value for most of the time remains static (everyday).
But as a contigency, what if the value changes one day, say one more person is being added to the list and list count becomes previous count+1.
But as per the formula, every cell changes its value to the new value
(if previous value=n, and the new value is n+1...all cells show n+1, where as I want only those cells,the date on which n becomes n+1 to show n+1 value and the rest to be n)...
How this can be avoided..??
One solution is to PASTESPECIAL AS VALUE in every cell of corresponding field of that metric table instead of direct reference...but is there any better solution..??
Note: the metric table measures every day values....to be more narrower, it is almost like an online system wherein once the export file is saved onto the said location, the metric table would display the values as of that time...
THE SECOND PROBLEM: The data that comes from Access has some columns which contains dates. I have to count some fields based on their individual criteria (like "Yes", or "No") on a given date. Am using SUMPRODUCT to calculate them.
The problem is: the dates the comes from Access to Excel is in General format and not in Date format. The date in Excel table is not recognising the date from Access exported excel file as both the formats are different. I tried changing the date in my metric table to number by using the formula n(value), tried changing to text by using Text (date, "000000") but there is no use of it. Any help on format recognition is also most welcome.
Last edited:
