Entering a "result" text value if a range of adjacent cells contains any text

[davestephsamem]

Hi,
I have a sheet that contains a timetable for a school - 16 class columns and 33 lesson rows. This "master" timetable is then sent to individual tabs for each teacher's individual timetable.
This results in a timetable that has one cell in each of the 16 rows with text that corresponds to their lesson title: they can't be in two places at once.
On occasions when there is no text within a row the teachers are "free" - I would like to have a mechanism where these "frees" are indicated. I was thinking of a formula in column 17 that does the job of "if there is any text in (e.g.) A1:P1 return nothing, otherwise return "free"". I sort of managed it - but it spills...

Thanks

Dave

 

[etaf]

=COUNTBLANK(A1:P1)
=IF(COUNTBLANK(A1:P1)>0,"Free","")

Various.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
2																	Free
3	1	1	1	1	1	1	1		1	1	1	1	1	1	1	1	Free
Sheet15

Cell Formulas
Range	Formula
Q1:Q3	Q1	=IF(COUNTBLANK(A1:P1)>0,"Free","")

 

[davestephsamem]

Hi, thanks for this. I may have not explained myself very well - this almost works but I'm looking for a solution that only gives me "free" on "row 5" of this example...

 

[etaf]

your example goes from column D to Column T
is that the most colums ?

in my example just change to
=IF(COUNTA(A1:P1)>0,"","Free")
OR for you image
=IF(COUNTA(D1:T1)>0,"","Free")

Various.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	english1	english2	english3	english4	english5	english6	english7	english8	english9	english10	english11	english12	english13	english14	english15	english16			16
2																	Free		0
3	english1																		1
Sheet15

Cell Formulas
Range	Formula
Q1:Q3	Q1	=IF(COUNTA(A1:P1)>0,"","Free")
S1:S3	S1	=COUNTA(A1:P1)

FYI
Note: Images are difficult to see , and also requires that I input all the data myself, which is very time consuming.

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.

 

[davestephsamem]

Hi again - I'm really grateful for your input on this. I've uploaded a mini-sheet - the formula is not quite doing what I need it to: I wonder if it's because the cells are linked to another sheet?

Copy of Copy of Copy of Copy of Copy of 2023 timetable 8.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1			KS3 /KS4/KS5	7	7	7	8BR	8Mc	8B	9Bu	9Mc	9HH	10BR	10S	10Cr	11A	11B	Post 16	Post 16	Post 16
2		Monday	1	English
3			2					English
4			3									English
5			4										English
6			5										RE
7			6
8			7										English
Daisy Brown

Cell Formulas
Range	Formula
D2:T2	D2	=IF(Master!F$3="DB","English","")
U2:U8	U2	=IF(COUNTA(D2:T2)>0,"","Free")
D3:T3	D3	=IF(Master!F$5="DB","English","")
D4:T4	D4	=IF(Master!F$7="DB","English","")
D5:T5	D5	=IF(Master!F$9="DB","English","")
D6:T6	D6	=IF(Master!F$11="DB","RE","")
D7:T7	D7	=IF(Master!F$13="DB","English","")
D8:T8	D8	=IF(Master!F$15="DB","English","")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:T34	Cell	does not contain a blank value	text	NO
D2:T34	Cell Value	contains ""*""	text	NO

 

[etaf]

yes, it because it has a formula in
so you could use
=COUNTIF(D2:T2,"")
IF(COUNTIF(D2:T2,"")=17, "free","")

the 17 are the number of columns in the range D to T

should be able to do something with blanks but not formulas

anyway try

IF(COUNTIF(D2:T2,"")=17, "free","")

 

[davestephsamem]

Excellent, that does the job! Many thanks.

 

[etaf]

you are welcome,
i have not looked at simplifying it so that you do not need to know the exact number of columns
But of that now works ok, i wont do any more work, unless its a problem in the future as you apply it