look to see if data is in 2 or more cells out of three

[Joe Eaton]

I have 3 cells which will have a number in (cost) one is GBP, the next is US Dollars and the third will be Euro
This is so i can do a calc to convert to GBP
But i am looking for a formula to show an error message like "Only put a cost in one cell"
I can do this over 2 cells with a formula but can't work out how to do so over three (and maybe 4 in the future)

 

[RobP]

Hi,

maybe something like this can help :

Book1
	A	B	C	D	E	F
1	X-Rate
2	1	GBP			Conversion:	3.32
3	0.76	Dollar
4	0.83	Euro	4
Sheet1

Cell Formulas
Range	Formula
F2	F2	=IF(COUNTIF(C2:C4,">0")>1,"Only put cost in one cell",SUMPRODUCT(A2:A4,C2:C4))

 

[etaf]

how about
data validation - and use
=COUNTA($A1:$D1)=1

where $A1:$D1 is the range of cells

 

[HighAndWilder]

I have 3 cells which will have a number in (cost) one is GBP, the next is US Dollars and the third will be Euro
This is so i can do a calc to convert to GBP
But i am looking for a formula to show an error message like "Only put a cost in one cell"
I can do this over 2 cells with a formula but can't work out how to do so over three (and maybe 4 in the future)

COUNTA will count non blank cells.

VBA to extract info from one tab in several workbooks.xlsm
	B	C	D	E	F	G	H
1
2		12
3				Only put a cost in one cell.
4		33
5
6
7
Sheet1

Cell Formulas
Range	Formula
E3	E3	=IF(COUNTA(C2:C4)>1,"Only put a cost in one cell.","")

 

[Joe Eaton]

To be a little clearer, the user will only have a cost which will be either be in GBP US dollar or Euro. My formula is just to show if by error the user adds in a number in 2 or more cells.

 

[RobP]

In this case I think @HighAndWilder (or @etaf has provided what you need.

If you want to integrate that into your conversion formula, then you can use mine above.

thanks for the feedback.

cheers
Rob

 

[Joe Eaton]

COUNTA will count non blank cells.

VBA to extract info from one tab in several workbooks.xlsm
	B	C	D	E	F	G	H
1
2		12
3				Only put a cost in one cell.
4		33
5
6
7
Sheet1

Cell Formulas
Range	Formula
E3	E3	=IF(COUNTA(C2:C4)>1,"Only put a cost in one cell.","")

This is perfect thank you.

COUNTA will count non blank cells.

VBA to extract info from one tab in several workbooks.xlsm
	B	C	D	E	F	G	H
1
2		12
3				Only put a cost in one cell.
4		33
5
6
7
Sheet1

Cell Formulas
Range	Formula
E3	E3	=IF(COUNTA(C2:C4)>1,"Only put a cost in one cell.","")

how about
data validation - and use
=COUNTA($A1:$D1)=1

where $A1:$D1 is the range of cell

 

[Joe Eaton]

how about
data validation - and use
=COUNTA($A1:$D1)=1

where $A1:$D1 is the range of cells

Hi yes this does work but for my brain i can adapt HighandWilders easier for me to add the exchange rates. thanks any way

 

[etaf]

datavalidation will work and only allow 1 value to be entered and bring up and error message - which you can custom to add an input message - so you can have a note appears as to what to do and also an error message if they try to put a 2nd value in
and STOPS 2 or more values and people just ignoring the message

Hi yes this does work but for my brain i can adapt HighandWilders easier for me to add the exchange rates. thanks any way

if you do need to go that way - maybe ADD conditional formatting to colour the cells to make it very clear