BorisYeltsin
New Member
- Joined
- Oct 27, 2020
- Messages
- 4
- Office Version
- 2019
- 2007
- Platform
- Windows
Hello all.
I received as spreadsheet from a client and several cells that uses formula return #Value!, while in this client PC the results are displayed normally.
Tried in different computers, all running 2007 version, but problem persisted.
My first bet was links with other files that I may not have received, but there were none.
Then I tought it could be version issues, but I tried in my home computer (2019 version) and got the same problem.
I tried setting the formula calculation to automatic, tried those option regarding compatibitily with Lotus, all without success.
As a test, I opened the spreadsheet using LibreOffice Calc and the results were displayed correctly.
Can't see an error with the all the formula itself (and, to corroborate that, there is the example of opening without problem on LibreOffice).
An example of formula resulting in error:
=IF(ISNUMBER(U$4),SUMPRODUCT(OFFSET(U41,,-(U$4-U$37),,U$4-U$37+1)+(TRANSPOSE(MMULT((ROW(INDIRECT(ADDRESS(1,1)&":"&ADDRESS(U$4-U$37+1,1)))-ROW(INDIRECT(ADDRESS(1,1)&":"&ADDRESS(1,1)))=(COLUMN(INDIRECT(ADDRESS(1,1)&":"&ADDRESS(1,U$4-U$37+1)))))+0,TRANSPOSE(OFFSET(U85,,-(U$4-U$37),,U$4-U$37+1)))))*-$D26,OFFSET(U98,,-(U$4-U$37),,U$4-U$37+1),INDEX(OFFSET(U$10,,-(U$4-U$37),,U$4-U$37+1),1,COUNTA(OFFSET(U$10,,-(U$4-U$37),,U$4-U$37+1))-COLUMN(OFFSET(U$10,,-(U$4-U$37),,U$4-U$37+1))+COLUMN(OFFSET(U$10,,-(U$4-U$37)))))/1,000,0)
Searched for similar problem, but haven't found it.
Can someone help me? Thanks in advance.
I received as spreadsheet from a client and several cells that uses formula return #Value!, while in this client PC the results are displayed normally.
Tried in different computers, all running 2007 version, but problem persisted.
My first bet was links with other files that I may not have received, but there were none.
Then I tought it could be version issues, but I tried in my home computer (2019 version) and got the same problem.
I tried setting the formula calculation to automatic, tried those option regarding compatibitily with Lotus, all without success.
As a test, I opened the spreadsheet using LibreOffice Calc and the results were displayed correctly.
Can't see an error with the all the formula itself (and, to corroborate that, there is the example of opening without problem on LibreOffice).
An example of formula resulting in error:
=IF(ISNUMBER(U$4),SUMPRODUCT(OFFSET(U41,,-(U$4-U$37),,U$4-U$37+1)+(TRANSPOSE(MMULT((ROW(INDIRECT(ADDRESS(1,1)&":"&ADDRESS(U$4-U$37+1,1)))-ROW(INDIRECT(ADDRESS(1,1)&":"&ADDRESS(1,1)))=(COLUMN(INDIRECT(ADDRESS(1,1)&":"&ADDRESS(1,U$4-U$37+1)))))+0,TRANSPOSE(OFFSET(U85,,-(U$4-U$37),,U$4-U$37+1)))))*-$D26,OFFSET(U98,,-(U$4-U$37),,U$4-U$37+1),INDEX(OFFSET(U$10,,-(U$4-U$37),,U$4-U$37+1),1,COUNTA(OFFSET(U$10,,-(U$4-U$37),,U$4-U$37+1))-COLUMN(OFFSET(U$10,,-(U$4-U$37),,U$4-U$37+1))+COLUMN(OFFSET(U$10,,-(U$4-U$37)))))/1,000,0)
Searched for similar problem, but haven't found it.
Can someone help me? Thanks in advance.