Column formula is not working

Copper Contributor

Hi, 

 

Report dataReport dataI 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.  

8 Replies

@hassan41 

In your screenshot, G36 does not contain a formula but the fixed value 36...

Hi Hans, Thanks for response. that were i am facing an issue. i want the every new response/data to automatically get the column formula. i have implemented it the same way. the week column formula is working for every new entry and the month column one isn,t.

@hassan41 

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.

Thanks. Point 1 was already on place.
did the same thing as you advised in point 2 and submitted a new response and still received the same error.

@hassan41 

Do you mean that Excel spontaneously enters the fixed number 36 in a column with formulas?

@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. 

Capture.PNG

@hassan41 

That is weird. Could you attach a small sample workbook demonstrating the problem?

@hassan41 

I'd copy correct formula, select Month column and Del everything, paste formula into any cell again.