Formula to identify if a dataset contains gaps

[The_Snook]

Morning all,

My first post of 2021 so best wishes to all.

I'm pulling together a template and I'd like to ensure that the data is input as one dataset, i.e. doesn't include any gaps. I currently have an error check built in that ensures all cells in a row are populated. How can I check that all rows are populated in order? NB - Not all rows in the template may be required/expected to contain data, i.e. gaps at the bottom are permitted.

Attached is an example of the data I'm working with.

Any queries, just shout.

Thanks in advance,

Snook

[AliGW]

I think you are overcomplicating it. Try this:

=IF(COUNTBLANK(H5:J5)>0,"Error - All cells must be completed","OK")

[Glenn Kennedy]

In K5, copied down:

=IF(COUNTA(H5:$H$24)=0,"",IF(H5<>"","OK","Error"))

[AliGW]

Or this:

=IF(ROW()>LOOKUP(2,1/($H$5:$H$24<>""),ROW($H$5:$H$24)),"",IF(COUNTBLANK(H5:J5)>0,"Error - All cells must be completed","OK"))

@Glenn

Not sure about yours:

Excel 2016 (Windows) 32 bit

	H	I	J	K	L
4	Bidder Name	Bid 1	Bid 2	AliGW	Glenn
5	Bidder 1	£ 922.00	£ 737.00	OK	OK
6	Bidder 2	£ 672.00	£ 306.00	OK	OK
7	Bidder 3	£ 865.00	£ 405.00	OK	OK
8	Bidder 4	£ 801.00	£ 547.00	OK	OK
9	Bidder 5		£ 225.00	Error - All cells must be completed	OK
10	Bidder 6	£ 566.00	£ 115.00	OK	OK
11	Bidder 7	£ 957.00	£ 557.00	OK	OK
12	Bidder 8	£ 652.00	£ 456.00	OK	OK
13	Bidder 9	£ 615.00	£ 588.00	OK	OK
14	Bidder 10	£ 944.00	£ 778.00	OK	OK
15	Bidder 11	£ 972.00	£ 549.00	OK	OK
16	Bidder 12	£ 370.00	£ 353.00	OK	OK
17				Error - All cells must be completed	Error
18	Bidder 14	£ 235.00	£ 361.00	OK	OK
19	Bidder 15	£ 163.00	£ 154.00	OK	OK
20	Bidder 16	£ 391.00	£ 290.00	OK	OK
21	Bidder 17	£ 305.00	£ 445.00	OK	OK
22
23
24

Sheet: Sheet1

[Pete_UK]

Or, you could allow data to be entered into a Raw_data sheet like that shown in your Bad example, and then get rid of the blanks yourself in another sheet, which is the one that drives your template, so the User is none the wiser.

Hope this helps.

Pete

[The_Snook]

Thanks all, much appreciated as always.

Ali your 2nd solution worked a treat, thanks.

@Glenn - Close but no cigar.
@Pete - Time permitting I'd have made it more foolproof/robust but as is usually the case this needs turning round and firing out the door asap.

[Bo_Ry]

Another one

K5
=IF(COUNTA(H5:J$24),IF(COUNTA(H5:J5)=3,"OK","Error - All cells must be completed"),"")

[The_Snook]

Interesting solution, I like it.

Cheers Bo_Ry