|
|
|
|||||||
| Excel Questions All Excel/VBA questions - formulas, macros, pivot tables, general help, etc. Please post to this forum in English only. |
 |
|
|
Thread Tools | Display Modes |
May 11th, 2002, 02:03 AM
|
#1 |
|
Board Regular
Join Date: Mar 2002
Posts: 217
|
Hi,
The function below does not return what I need and I can't work it out. ,=IF(ISNA(-VLOOKUP(E21,Stocktake!$A$1:$I$65000,9,FALSE)),-T213,-VLOOKUP(E21,Stocktake!$A$1:$I$65000,9,FALSE)-T21) When E21 is blank or it can't fine any data in Stocktake!$A$1:$I$65000 it needs to return the a negative value of T213. If I have 5 in T213 then it should return -5. Your help is very much appreciated Andonny |
|
|
|
May 11th, 2002, 02:09 AM
|
#2 |
|
MrExcel MVP
Join Date: Apr 2002
Location: Vancouver BC , Canada
Posts: 6,259
|
What does it return in those cases ?
|
|
|
|
|
May 11th, 2002, 02:20 AM
|
#3 | |
|
MrExcel MVP
Join Date: Feb 2002
Location: The Hague
Posts: 50,317
|
Quote:
=IF(COUNTIF(Stocktake!$A$1:$A$65000,E21),-VLOOKUP(E21,Stocktake!$A$1:$I$65000,9,0)-T21,-T213) Caveat. If you want to have a negative result even if T213 already has a negative value, then you could also use: =-IF(COUNTIF(Stocktake!$A$1:$A$65000,E21),VLOOKUP(E21,Stocktake!$A$1:$I$65000,9,0)-T21,ABS(T213)) Note. I used 0 instead of FALSE (they mean same thing) in VLOOKUP. Aladin |
|
|
|
|
|
May 11th, 2002, 02:40 AM
|
#4 |
|
MrExcel MVP
Join Date: Apr 2002
Location: Vancouver BC , Canada
Posts: 6,259
|
Code:
=IF(ISNA(-VLOOKUP(E21,StockTake!$A:$I000,9,FALSE)),-INDEX(T:T,213,T:T),-VLOOKUP(E21,StockTake!$A:$I000,9,FALSE)-INDEX(T:T,21,T:T)) |
|
|
|
|
May 11th, 2002, 03:23 AM
|
#5 |
|
MrExcel MVP
Join Date: Apr 2002
Location: Vancouver BC , Canada
Posts: 6,259
|
Very Nice Code Aladin
|
|
|
|
|
| Bookmarks |
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
