SOLVED

Formulas work for me but not for everyone

Copper Contributor

I have created a spreadsheet for keeping statistics for the job. There are many sheets and many formulas that retrieve data from other sheets and many formulas with several criteria. It works just fine for me, all the formulas work. The problem is that with some colleagues the formulas will not update. The problem applies to some cells, but not all. In the cells in question, it will only say 0. With some of the colleagues, the problem cells will eventually start to update if they have the spreadsheet up for an hour or so. But only a cell here and a cell there.

 

Not everyone has the same language settings on the PC, so I suspect that it may have something to do with the matter, for example in sumifs formulas where date to-from is one of several criteria. But - there are other sheets in the spreadsheet that use similar formulas where it updates without a problem with my colleagues. Also sumifs with to-from date as one of several criteria that pulls data from the same sheet as the formulas I'm having trouble with. So if the language setting was a problem because of the date format, it shouldn't have worked here either.

 

So I don't understand what is causing this! I can share the spreadsheet if anyone wants to try to figure it out which is a mystery to me.

 

The first screenshot shows cells that do not update with any of my colleagues. The second screenshot shows example cells where it seems to work for everyone. Both are similar formulas with similar criteria, such as date.

 

Stina_Loedemel_0-1670512665474.pngStina_Loedemel_1-1670512727391.png

 

2 Replies
best response confirmed by SergeiBaklan (MVP)
Solution

@Stina_Loedemel 

Literal date values in a quoted string are bound to cause problems with different date formats. It's better to refer to cells with dates.

For example, enter the first of each month in column A:

01.01.2022 for Jan in (probably) A10

01.02.2022 for Feb in A11

01.03.2022 for Mar in A12

etc.

Apply the custom number format mmm to these cells, so that you will see Jan, Feb, Mar, ...

In the formula, replace ">=01.01.2022" with ">="&$A10 and "<=31.01.2022" with "<="&EOMONTH($A10;0)

The formulas should then work with different date settings.

 

Also, if you want to enter the formulas in row 10 and fill down, you should use $T$6:$T$504 etc. Using $ will "fix" the ranges when you fill down.

It worked. Thank you so much!!
1 best response

Accepted Solutions
best response confirmed by SergeiBaklan (MVP)
Solution

@Stina_Loedemel 

Literal date values in a quoted string are bound to cause problems with different date formats. It's better to refer to cells with dates.

For example, enter the first of each month in column A:

01.01.2022 for Jan in (probably) A10

01.02.2022 for Feb in A11

01.03.2022 for Mar in A12

etc.

Apply the custom number format mmm to these cells, so that you will see Jan, Feb, Mar, ...

In the formula, replace ">=01.01.2022" with ">="&$A10 and "<=31.01.2022" with "<="&EOMONTH($A10;0)

The formulas should then work with different date settings.

 

Also, if you want to enter the formulas in row 10 and fill down, you should use $T$6:$T$504 etc. Using $ will "fix" the ranges when you fill down.

View solution in original post