Multiple ISBLANK

[EmmaTM]

Hi - I have the following in a spreadsheet:

In column J so far I have =IF(ISBLANK(B79),TEXTJOIN(,,"PROJECT CODE NOT LISTED: ",AB79),IF(ISBLANK(F79),TEXTJOIN(,,"RESOURCE CODE MISSING: ",AB79),""))

So if B or F is blank the detail is added into AB and this updates the notes.
I also need to add to this if AC= LATE TIMESHEET that needs to update J with that note.
If B&F are blank & AC is late timesheet I need the note to display all 3 options

Do I need to seperate AB and have a note for project and another column for resource code detail if B&F are blank?

Thank you

 

[Sufiyan97]

Try

=IF(AND(B79="",F79="",AC79="Late Timesheet"),"PROJECT CODE NOT LISTED: "&AB79&" RESOURCE CODE MISSING: "&AB79&" Late Timesheet",IF(B79="","PROJECT CODE NOT LISTED: "&AB79,IF(F79="","RESOURCE CODE MISSING: "&AB79,"")))

 

[Peter_SSs]

If your question has not been answered by the above formula, please supply some new sample data that includes enough rows to show examples of all the possible combinations of BLANKS/NON-BLANKS/LATE TIMESHEET etc and manually input the desired results in column J.

Additionally, it would help us help you better if we could easily use your sample data for testing. We can't do that from an image, but can if you provide the sample data and expected results with XL2BB
With XL2BB, as with your image above, irrelevant columns can be hidden before you use XL2BB

 

[EmmaTM]

Try

=IF(AND(B79="",F79="",AC79="Late Timesheet"),"PROJECT CODE NOT LISTED: "&AB79&" RESOURCE CODE MISSING: "&AB79&" Late Timesheet",IF(B79="","PROJECT CODE NOT LISTED: "&AB79,IF(F79="","RESOURCE CODE MISSING: "&AB79,"")))

Thank you, I did this but added in columns AA where the project code is missing, AB where the resource code is missing & AC if late timesheet.
This works if B & F are both blank but if one of them is complete then the supplier note only shows with the missing project code or missing resource code and not the late timesheet.

Thank you

 

[Peter_SSs]

So ..?

.. please supply some new sample data that includes enough rows to show examples of all the possible combinations of BLANKS/NON-BLANKS/LATE TIMESHEET etc and manually input the desired results in column J.

.. can if you provide the sample data and expected results with XL2BB

 

[EmmaTM]

I am trying but it keeps crashing my excel!

 

[EmmaTM]

I am sorry I am having real issues trying to get this on the XL2BB.

Here is some data - not sure if you can help with this as is ?

PROJECT	RESOURCE REQUESTED	TIMESHEET WEEK ENDING DATE	SUPPLIER NOTES	MISSING PROJECT CODE	MISSING RESOURCE CODE	LATE TIMESHEET
73PO-00878	LABOURER	09/12/2023
73PO-00884	CONSTRUCTION MANAGER	09/12/2023
73PO-00878	PROJECT ENGINEER	09/12/2023
73PO-00728	PLANNING ENGINEER	18/11/2023				LATE TIMESHEET
73PO-00728	PLANNING ENGINEER	25/11/2023				LATE TIMESHEET
73PO-00728	PLANNING ENGINEER	02/12/2023
		09/12/2023	PROJECT CODE NOT LISTED: 73CO-00912	73CO-00912	PROJECT MANAGER	LATE TIMESHEET
	PROJECT MANAGER	09/12/2023	PROJECT CODE NOT LISTED: 73CO-00912	73CO-00912
73PO-00560	SITE MANAGER	09/12/2023
73PO-00560	CONSTRUCTION MANAGER	09/12/2023
73PO-00560	SENIOR ENGINEER	09/12/2023
73PO-00848		09/12/2023	RESOURCE CODE MISSING: T&C CO-ORDINATOR		T&C CO-ORDINATOR
	COSS	02/12/2023	PROJECT CODE NOT LISTED: 73PO-79206	73PO-79206
730392	CONSULTANT	09/12/2023
	SYSTEMS ENGINEER	09/12/2023	PROJECT CODE NOT LISTED: 73CX-00786	73CX-00786
73PO-00884	ADMINISTRATOR	09/12/2023

Supplier notes I want to list the following detail: if project column is blank the project code is shown in missing project column, if resource is missing this is shown in missing resource column, if a late timesheet then its shown in late timesheet column.
I have 1 row where I have all 3 notes required so I want it shown as PROJECT CODE NOT LISTED: 73CO-00912 / MISSING RESOURCE CODE: PROJECT MANAGER / LATE TIMESHEET
If there is nothing missing it should be blank, if 2 of the creiteria are missing then that should be shown.

I hope I have explained that ok?

This was my original attempt, without the LATE TIMESHEET included:
=IF(ISBLANK(B13),TEXTJOIN(,,"PROJECT CODE NOT LISTED: ",AA13),IF(ISBLANK(F13),TEXTJOIN(,,"RESOURCE CODE MISSING: ",AB13),""))

Thank you

 

[Peter_SSs]

Thanks for the sample data.

I hope I have explained that ok?

Better, other than that you didn't enter in the expected results down column J.

I think then this is what you want? If not, please post the expected results as you want them, not as you have them so far.

23 12 13.xlsm
	B				F			I	J																	AA	AB	AC
5	PROJECT				RESOURCE REQUESTED			TIMESHEET WEEK ENDING DATE	SUPPLIER NOTES																	MISSING PROJECT CODE	MISSING RESOURCE CODE	LATE TIMESHEET
6	73PO-00878				LABOURER			9/12/2023
7	73PO-00884				CONSTRUCTION MANAGER			9/12/2023
8	73PO-00878				PROJECT ENGINEER			9/12/2023
9	73PO-00728				PLANNING ENGINEER			18/11/2023	LATE TIMESHEET																			LATE TIMESHEET
10	73PO-00728				PLANNING ENGINEER			25/11/2023	LATE TIMESHEET																			LATE TIMESHEET
11	73PO-00728				PLANNING ENGINEER			2/12/2023
12								9/12/2023	PROJECT CODE NOT LISTED: 73CO-00912 / MISSING RESOURCE CODE: PROJECT MANAGER / LATE TIMESHEET																	73CO-00912	PROJECT MANAGER	LATE TIMESHEET
13					PROJECT MANAGER			9/12/2023	PROJECT CODE NOT LISTED: 73CO-00912																	73CO-00912
14	73PO-00560				SITE MANAGER			9/12/2023
15	73PO-00560				CONSTRUCTION MANAGER			9/12/2023
16	73PO-00560				SENIOR ENGINEER			9/12/2023
17	73PO-00848							9/12/2023	MISSING RESOURCE CODE: T&C CO-ORDINATOR																		T&C CO-ORDINATOR
18					COSS			2/12/2023	PROJECT CODE NOT LISTED: 73PO-79206																	73PO-79206
19	730392				CONSULTANT			9/12/2023
20					SYSTEMS ENGINEER			9/12/2023	PROJECT CODE NOT LISTED: 73CX-00786																	73CX-00786
EmmaTM

Cell Formulas
Range	Formula
J6:J20	J6	=TEXTJOIN(" / ",1,IF(B6="","PROJECT CODE NOT LISTED: "&AA6,""),IF(F6="","MISSING RESOURCE CODE: "&AB6,""),IF(AC6="","",AC6))

 

[EmmaTM]

Amazing - thank you that has worked

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.