| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF formula
Hi All
I am looking for a formula that does not used fixed cells to give me the result Formula that works at the moment is =if(B20=Terms!A2,Invoices!H20+Terms!B2) Contents in B20 and H20 will change each month so can not use the formula - I have tried using =if(b:b=Terms!A:A,Invoices!h:h+terms!b:b) but returns FALSE i.e. Invoices Terms Col B Col H Col A Col B Bov007 31/12/08 Bov007 45 Formula should give 14/2/08 Any suggestions would be grealty appreciated |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF formula
Peter;213285 Wrote: Hi All I am looking for a formula that does not used fixed cells to give me the result Formula that works at the moment is =if(B20=Terms!A2,Invoices!H20+Terms!B2) Contents in B20 and H20 will change each month so can not use the formula - I have tried using =if(b:b=Terms!A:A,Invoices!h:h+terms!b:b) but returns FALSE i.e. Invoices Terms Col B Col H Col A Col B Bov007 31/12/08 Bov007 45 Formula should give 14/2/08 Any suggestions would be grealty appreciated I don't understand what you mean. The idea of a formula is to evaluate when cell's content change. What do you want to do ? -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)* & allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=58535 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF formula
=H20 + VLOOKUP(B20,Terms!A:B,2,False)
HTH, Bernie MS Excel MVP "Peter" wrote in message ... Hi All I am looking for a formula that does not used fixed cells to give me the result Formula that works at the moment is =if(B20=Terms!A2,Invoices!H20+Terms!B2) Contents in B20 and H20 will change each month so can not use the formula - I have tried using =if(b:b=Terms!A:A,Invoices!h:h+terms!b:b) but returns FALSE i.e. Invoices Terms Col B Col H Col A Col B Bov007 31/12/08 Bov007 45 Formula should give 14/2/08 Any suggestions would be grealty appreciated |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF formula
Hi Bernie
Thanks for that - a great help (not very good at the lookups) - Next thing and i'm sure it's probably straightforward is that if False then =h20+30 "Bernie Deitrick" wrote: =H20 + VLOOKUP(B20,Terms!A:B,2,False) HTH, Bernie MS Excel MVP "Peter" wrote in message ... Hi All I am looking for a formula that does not used fixed cells to give me the result Formula that works at the moment is =if(B20=Terms!A2,Invoices!H20+Terms!B2) Contents in B20 and H20 will change each month so can not use the formula - I have tried using =if(b:b=Terms!A:A,Invoices!h:h+terms!b:b) but returns FALSE i.e. Invoices Terms Col B Col H Col A Col B Bov007 31/12/08 Bov007 45 Formula should give 14/2/08 Any suggestions would be grealty appreciated |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF formula
Peter,
This will use 30 is the item is not found in the terms list: =H20 + IF(ISERROR(VLOOKUP(B20,Terms!A:B,2,False)),30,VLOO KUP(B20,Terms!A:B,2,False)) HTH, Bernie MS Excel MVP "Peter" wrote in message ... Hi Bernie Thanks for that - a great help (not very good at the lookups) - Next thing and i'm sure it's probably straightforward is that if False then =h20+30 "Bernie Deitrick" wrote: =H20 + VLOOKUP(B20,Terms!A:B,2,False) HTH, Bernie MS Excel MVP "Peter" wrote in message ... Hi All I am looking for a formula that does not used fixed cells to give me the result Formula that works at the moment is =if(B20=Terms!A2,Invoices!H20+Terms!B2) Contents in B20 and H20 will change each month so can not use the formula - I have tried using =if(b:b=Terms!A:A,Invoices!h:h+terms!b:b) but returns FALSE i.e. Invoices Terms Col B Col H Col A Col B Bov007 31/12/08 Bov007 45 Formula should give 14/2/08 Any suggestions would be grealty appreciated |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF formula
Supberb - thanks for your help and promptness Bernie.
"Bernie Deitrick" wrote: Peter, This will use 30 is the item is not found in the terms list: =H20 + IF(ISERROR(VLOOKUP(B20,Terms!A:B,2,False)),30,VLOO KUP(B20,Terms!A:B,2,False)) HTH, Bernie MS Excel MVP "Peter" wrote in message ... Hi Bernie Thanks for that - a great help (not very good at the lookups) - Next thing and i'm sure it's probably straightforward is that if False then =h20+30 "Bernie Deitrick" wrote: =H20 + VLOOKUP(B20,Terms!A:B,2,False) HTH, Bernie MS Excel MVP "Peter" wrote in message ... Hi All I am looking for a formula that does not used fixed cells to give me the result Formula that works at the moment is =if(B20=Terms!A2,Invoices!H20+Terms!B2) Contents in B20 and H20 will change each month so can not use the formula - I have tried using =if(b:b=Terms!A:A,Invoices!h:h+terms!b:b) but returns FALSE i.e. Invoices Terms Col B Col H Col A Col B Bov007 31/12/08 Bov007 45 Formula should give 14/2/08 Any suggestions would be grealty appreciated |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF formula
Supberb - thanks for your help and promptness Bernie.
You're quite welcome. Bernie |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
