Combine Multiple Cell Formulas to Single Cell

[OscartheDog]

Hi

So sorry to bother everyone! I'm struggling to find a multiple formula in a single cell to work something out. This is the example:

This spreadsheet is to book multiple rooms. The first question is asking if the room type will be the same for all of them so:

Cell E1 has a Form Control tick box, i.e. tick if the box if the room type is the same for all dates
Cell F1 is asking "If Yes, Select Location"

F1 contains a drop down with 3 options - "Single Room", "Double Room", "Other"

If F1 is "Single Room" or "Double Room" I want cell G1 to remain blank, however if it states "Other" I want it say "If Other, State Here:" (i.e. enter into H1) which it does.

Now this is where it gets interesting!

As this will be automatically submitted via email once complete, I need to ensure it's identifying all the issues and highlighting them before the email is sent. I have this set up on VBA, it's just trying to find the right way to get the correct information.

So, I1 is TRUE or FALSE depending on whether or not the box has been ticked.

If it's been ticked but F1 is blank it states "Issue"
If it's been ticked and F1 is either "Single Room", "Double Room" AND H1 is blank it states "Fine"
If it's been ticked and F1 is either "Single Room", "Double Room" BUT H1 ISN'T blank it states "Issue - Text to Remove"

If it's been ticked and F1 is "Other", G1 shows asking to state reason in H1 so:
If F1 is "Other" AND H1 ISN'T blank it states "Fine"
If F1 is "Other" BUT H1 IS blank it states "Issue - Info Needed"

And just to add more to it, if E1 tick box isn't selected but there is text in F1 and/or H1 it states "Issue - Text to Remove"

I have these all working in 3 separate formulas - it's just trying to find the right way to combine it into 1 formula next to TRUE/FALSE to enable VBA to recognise if an Alert Box is needed before continuing.

I hope this makes sense but please let me know if not - it was difficult to try and find the right way to explain this!

Thank you!

 

[ExcelGzh]

What you needed is some nested IF statements. The below table shows the various logic paths you have described.

Template - Tender Estimate.xlsm
	E	F	G	H	I	J	K
1	TRUE						Issue
2	TRUE	Single Room					Fine
3	TRUE	Single Room		not blank			Issue - Text to Remove
4	TRUE	Double Room					Fine
5	TRUE	Double Room		not blank			Issue - Text to Remove
6	TRUE	Other		not blank			Fine
7	TRUE	Other					Issue - Info Needed
8	FALSE	not blank					Issue - Text to Remove
9	FALSE			not blank			Issue - Text to Remove
File Analysis

Cell Formulas
Range	Formula
K1:K9	K1	=IF(E1,IF(LEN(F1)=0,"Issue",IF(OR(F1="Single Room",F1="Double Room"),IF(LEN(H1)=0,"Fine","Issue - Text to Remove"),IF(LEN(H1)>0,"Fine","Issue - Info Needed"))),IF(OR(LEN(F1)>0,LEN(H1)>0),"Issue - Text to Remove",""))