| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Return value in cell above the reference
I've used the following formula to return the information I want to cell N1434: =SUMPRODUCT(--($Y$2:$Y$1404=$Y1434),--($E$2:$E$1404=$E1434),--($F$2:$F$1404=$F1434),N$2:N$1404) In this instance it returns the value from cell N773, which is correct. Now in Cell N1433 I would like to return the value in cell N772. I can't use the sumproduct function because the references can't be used again without throwing off other calculations. I tried to get the row function to go out and return N773 as an answer to something but failed miserably. Any help is appreciated. Jean -- Jean ------------------------------------------------------------------------ Jean's Profile: http://www.excelforum.com/member.php...o&userid=17585 View this thread: http://www.excelforum.com/showthread...hreadid=547093 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Return value in cell above the reference
You can use $N$1:$N$1403 as returned range:
=SUMPRODUCT(--($Y$2:$Y$1404=$Y1434),--($E$2:$E$1404=$E1434),--($F$2:$F$1404=$F1434),N$1:N$1403) -- AP "Jean" a écrit dans le message de news: ... I've used the following formula to return the information I want to cell N1434: =SUMPRODUCT(--($Y$2:$Y$1404=$Y1434),--($E$2:$E$1404=$E1434),--($F$2:$F$1404=$F1434),N$2:N$1404) In this instance it returns the value from cell N773, which is correct. Now in Cell N1433 I would like to return the value in cell N772. I can't use the sumproduct function because the references can't be used again without throwing off other calculations. I tried to get the row function to go out and return N773 as an answer to something but failed miserably. Any help is appreciated. Jean -- Jean ------------------------------------------------------------------------ Jean's Profile: http://www.excelforum.com/member.php...o&userid=17585 View this thread: http://www.excelforum.com/showthread...hreadid=547093 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Return value in cell above the reference
Try...
=INDEX(N$2:N$1404,MATCH(1,IF($Y$2:$Y$1404=$Y1434,I F($E$2:$E$1404=$E1434,I F($F$2:$F$1404=$F1434,1))),0)-1) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , Jean wrote: I've used the following formula to return the information I want to cell N1434: =SUMPRODUCT(--($Y$2:$Y$1404=$Y1434),--($E$2:$E$1404=$E1434),--($F$2:$F$1404=$F 1434),N$2:N$1404) In this instance it returns the value from cell N773, which is correct. Now in Cell N1433 I would like to return the value in cell N772. I can't use the sumproduct function because the references can't be used again without throwing off other calculations. I tried to get the row function to go out and return N773 as an answer to something but failed miserably. Any help is appreciated. Jean |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Return value in cell above the reference
Both solutions worked! Thank you so much--you are the rock stars of the excel universe! Jean -- Jean ------------------------------------------------------------------------ Jean's Profile: http://www.excelforum.com/member.php...o&userid=17585 View this thread: http://www.excelforum.com/showthread...hreadid=547093 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Reference Cell Color From Other WorkSheets | Excel Worksheet Functions | |||
| why a reference to an empty cell is not considered empty | Excel Discussion (Misc queries) | |||
| Cell Reference locking | Excel Discussion (Misc queries) | |||
| GETPIVOTDATA - return cell reference, not value | Excel Worksheet Functions | |||
| Return cell reference of lookup value | Excel Worksheet Functions | |||
Hybrid Mode
