Forum Discussion
How to resolve formula discrepancy Excel/SharePoint
Good afternoon, could someone help me? The formula =IF(ISERROR(INDEX(Form1!F:F, ROW()-90)), "", IF(INDEX(Form1!F:F, ROW()-90) = "", "", INDEX(Form1!F:F, ROW()-90))) works in offline Excel, but in SharePoint/Excel Online it shows an error. It appears there is an error in the formula. I am using Portuguese in both versions.
5 Replies
- SergeiBaklanDiamond Contributor
Most probably offline Excel is not recent one (i.e. not 365 or 2021, etc). With modern Excel at row #90 we have INDEX(F:F, 0), Excel tries to return entire column F staring from the cell in row 90, we have not enough space for that, thus error.
- m_tarlerBronze Contributor
That's a great point but I don't think it is the issue. Only row #90 would be an issue and that would be a SPILL error, which I would think the OP would have called out specifically:
- SergeiBaklanDiamond Contributor
We could only make guesses, what exactly happened is not clear. Which version of "offline" Excel, is it Windows or Mac. Which locale is used locally (Portuguese is language added to Office or locale), which regional settings are used with Excel for web and SharePoint (default US or they were changed on another one). Which exactly formula is used in each case. It's given in English, Portuguese one will be
=SE(É.ERRO(ÍNDICE(Form1!F:F; LIN()-90)); ""; SE(ÍNDICE(Form1!F:F; LIN()-90) = ""; ""; ÍNDICE(Form1!F:F; LIN()-90)))
- m_tarlerBronze Contributor
As already mentioned by Kidd_Ip, the problem may (or is likely) the syntax for the online uses semi-colons instead of commas. That said, can I comment on the formula itself:
=IF( ISERROR(INDEX(Form1!F:F, ROW()-90)), "", IF( INDEX(Form1!F:F, ROW()-90) = "", "", INDEX(Form1!F:F, ROW()-90) ) )
so the 1st part checks if the INDEX is an error and print "" if so
2nd part checks if INDEX returns "" and print "" if so
3rd part returns the answer from INDEX
so why even do the 2nd part because if it will return "" anyway then just skip to part 3
as for the ISERROR why not just use IFERROR so it could look like this:
=IFERROR( INDEX(Form1!F:F, ROW()-90), "")
and that said, I'm not a fan of using hardcoded values and that 90 could be a problem. For example you or someone else a year or 2 from now decide the first 10 rows aren't needed and you just Delete those rows. Then all the data shift up by 10 rows and now instead of 90 it should be 80. So nothing is perfect but I would suggest an option might be to use the cell $F$90 because when those rows are deleted that cell $F$90 will automatically be adjusted to $F$80 so something like this:
=IFERROR( INDEX(Form1!F:F, ROW()-ROW($F$90) ), "")
note the column F doesn't matter but the '$' does so if you copy or drag down that cell reference doesn't change.
How about using explicit ranges and Portuguese syntax:
=SE(ÉERRO(ÍNDICE(Form1!F1:F200; LIN()-90)); ""; SE(ÍNDICE(Form1!F1:F200; LIN()-90)=""; ""; ÍNDICE(Form1!F1:F200; LIN()-90)))