Oct 20 2021 04:20 AM
Hi,
I am not a data person but need to create the report. I think the solution to problem will be simple but i dont know it.
I have simple ms form data in excel sheet. where i have created a pivot table for reporting.
When a new response is submitted. The formula applied to a month column is not working and i am getting random error data in that column. although the week column formula is working accurately for the same data. The formula's are Month =MONTH([@[Completion time]]) and Week =WEEKNUM([@[Completion time]])
Please see the attached screen shot and provide a solution.
Oct 20 2021 05:01 AM
In your screenshot, G36 does not contain a formula but the fixed value 36...
Oct 20 2021 05:07 AM
Oct 20 2021 08:04 AM
1) In File > Options > Proofing > AutoCorrect Options... > AutoFormat As You Type tab, make sure that the check box "Fill formulas in tables to create calculated columns" is ticked.
2) Clear the colum n in the table (except for the header of course), then enter the formula =MONTH([@[Completion time]]) in the cell below the header. Excel should automatically fill the entire table column.
Oct 20 2021 08:11 AM
Oct 20 2021 08:57 AM
Do you mean that Excel spontaneously enters the fixed number 36 in a column with formulas?
Oct 21 2021 01:29 AM
@Hans Vogelaar No, the value keeps changing and its not a fix number. As you can see the value has changed from 36 to 53.
Oct 21 2021 04:57 AM
That is weird. Could you attach a small sample workbook demonstrating the problem?
Oct 23 2021 04:19 AM
I'd copy correct formula, select Month column and Del everything, paste formula into any cell again.