cwbasset
New Member
- Joined
- Sep 28, 2003
- Messages
- 21
- Office Version
- 365
- Platform
- Windows
I am sure that there is an easy answer for this, but I apparently am not using the correct key search words to find it.
I have a proven conditional sumproduct formula that returns the column value from another spreadsheet: =SUMPRODUCT(('[2021 monthly.xlsx]vlookup'!$C$5:$O$29)*('[2021 monthly.xlsx]vlookup'!$C$3:$O$3=J$4))
However, when I use it on the newer 2022 monthly.xlsx spreadsheet, I receive #VALUE! errors.
I believe the difference must be in the formulas used pulling in data to the range in the original spreadsheets. In the spreadsheet that works, the cell formulas within the sumproduct range use the simple formula of =Jan!$W5.
In the updated spreadsheet, I am using the following formula: =IFERROR(INDEX(Jan!$A$5:$BC$35,MATCH(vlookup!$A5,Jan!$A$5:$A$35,0),MATCH(vlookup!C$1,Jan!$A$4:$BC$4,0)),"")
When I try to evaluate the formula, both ranges used in the formula seem to be fine, it is just at the end when everything comes up with errors. I'm stumped!
Any suggestions would be GREATLY appreciated!
I have a proven conditional sumproduct formula that returns the column value from another spreadsheet: =SUMPRODUCT(('[2021 monthly.xlsx]vlookup'!$C$5:$O$29)*('[2021 monthly.xlsx]vlookup'!$C$3:$O$3=J$4))
However, when I use it on the newer 2022 monthly.xlsx spreadsheet, I receive #VALUE! errors.
I believe the difference must be in the formulas used pulling in data to the range in the original spreadsheets. In the spreadsheet that works, the cell formulas within the sumproduct range use the simple formula of =Jan!$W5.
In the updated spreadsheet, I am using the following formula: =IFERROR(INDEX(Jan!$A$5:$BC$35,MATCH(vlookup!$A5,Jan!$A$5:$A$35,0),MATCH(vlookup!C$1,Jan!$A$4:$BC$4,0)),"")
When I try to evaluate the formula, both ranges used in the formula seem to be fine, it is just at the end when everything comes up with errors. I'm stumped!
Any suggestions would be GREATLY appreciated!