formula for single entry for every row

J

jv

Good day to all,

I have summary sheet report for monthly expenses in our
department. The account charges code are line in columns,
while the breakdown expenses are in the rows. There area
about 25 accounts & 97 rows of diff.expenses.


A B C D E F G
Descr. #50 #190 #195 #315 #360 #475 ->
1 Contracts 33,793
2 Elect'l. 14,202
3 Repairs 6,800
4 Kitch.Equipt. 2,700
5 Machinery 2,900

The amount charge should only appear "once" in every row
in a specific column and not to be allowed to make any
other entry on other columns. In short, 1 column charge in
every row only.

Is there a way in excel to make this possible?

For your kind assistance.

Thanks and regards.

jv
 
J

Jeff Jacobson

You could simply add a =COUNT(B2:Z2) OR =COUNTA(B2:Z2)
within an IF function to alert you of multiple entries in
a given row. =IF(COUNT(B2:Z2)>1,"ERROR","") this IF
function used for each row would alert you by displaying
the text ERROR in any row containing multiple entries.
 
M

Mark Graesser

You could use Data Validation. Assuming the number is to be entered within B3 through Z3

Data>Validation>Settings>Allow:Custom>Formula:=COUNTA($B3:$Z3)<=

select the group af cells in the first row of your table. Apply the above, adjusting the cell references for you table. Then you can fill down to apply the validation to the rest of you table

Good Luck
Mark Graesse
(e-mail address removed)

----- jv wrote: ----


Good day to all

I have summary sheet report for monthly expenses in our
department. The account charges code are line in columns,
while the breakdown expenses are in the rows. There area
about 25 accounts & 97 rows of diff.expenses


A B C D E F G
Descr. #50 #190 #195 #315 #360 #475 -
1 Contracts 33,793
2 Elect'l. 14,20
3 Repairs 6,80
4 Kitch.Equipt. 2,700
5 Machinery 2,90

The amount charge should only appear "once" in every row
in a specific column and not to be allowed to make any
other entry on other columns. In short, 1 column charge in
every row only

Is there a way in excel to make this possible

For your kind assistance

Thanks and regards

j
 
J

jv

Mark,

Thats a very powerful tip, no need for me to spend time
rechecking my work.

Thanks a lot.


jv

-----Original Message-----
You could use Data Validation. Assuming the number is to
Click to expand...
be entered within B3 through Z3:
Data>Validation>Settings>Allow:Custom>Formula:=COUNTA ($B3:$Z3)<=1

select the group af cells in the first row of your
Click to expand...
table. Apply the above, adjusting the cell references for
you table. Then you can fill down to apply the validation
to the rest of you table.
 
J

jv

Jeff,

Its a great way to recheck my work without having a second
thought of that a mistake exist somewhere.

Thanks, it helps a lot.

jv
 
Top