PDA

View Full Version : Chnage formula to ONLY allow column D and K



Pete
06-10-2008, 07:27 AM
Hi

How would i change the following formula so that it only allows columns D and K same worksheet.

It works for column D prefect, but i want to add column K to the below.

=IF('Deal Selection'!$D$9:$D$32=TRUE,IF(ISERROR((Revenue_Other_Markets_EU_NWS - Purchase_NWS_Other_Markets_EU)),0,(Revenue_Other_Markets_EU_NWS - Purchase_NWS_Other_Markets_EU)),0)

RonMcK
06-10-2008, 09:19 AM
Pete,

Does this do the trick for youi?

=IF(OR('Deal Selection'!$D$9:$D$32=TRUE,'Deal Selection'!$K$9:$K$32=TRUE), _
IF(ISERROR((Revenue_Other_Markets_EU_NWS - Purchase_NWS_Other_Markets_EU)),0, _
(Revenue_Other_Markets_EU_NWS - Purchase_NWS_Other_Markets_EU)),0)

or, do you need:

=IF(AND('Deal Selection'!$D$9:$D$32=TRUE,'Deal Selection'!$K$9:$K$32=TRUE), _
IF(ISERROR((Revenue_Other_Markets_EU_NWS - Purchase_NWS_Other_Markets_EU)),0, _
(Revenue_Other_Markets_EU_NWS - Purchase_NWS_Other_Markets_EU)),0)


Cheers!

Bob Phillips
06-10-2008, 02:17 PM
You have presented this formula or variations on it several times now so I have to ask about it.

Are you sure this is working, before you amend it that is? This part of the formula

'Deal Selection'!$D$9:$D$32=TRUE

checks just the first cell for TRUE, so fails if it is false. Unless you are using sophisticated block array formulae, and if you are constructing and implementing such formulae I would expect you to know how to change it as you require, it doesn't work.

RonMcK
06-12-2008, 09:07 AM
Bob,

Could it be that Pete planned to copy the cell (with its formula) down the column for all the rows used?

Just wondering,