IF and IFS help please

**maximus84** · 12-11-2020, 02:14 AM

Hello. I've completed 2 Excel courses on Udemy and partway through an advanced course.

I am attempting to automate a text result based on selected options.

I appreciate any help please as I am banging my head against a wall with no Google/Youtube luck.
Document attached

**Column B will always have a cell entry

1.

Column A is the manually entered output.

Based on selected options for columns B to I
I need an automated output K2 to be identical to A2

My formula is utilising IF and IFS.
I am stuck on if there is no answer in the cell.

TRUE = Before 1 Jan 2014
FALSE = After 1 Jan 2014
Not TRUE or FALSE (cell is empty) – I need A2 to only show a space “ “ as an answer.

Can anyone please help me?

2.
My other issue is spacing. You will see in my formula I use &””& in between each formula.
If C2 is entered as 4 years.
Excel will display 1st year4years

If I enter &” ”&
Excel will display 1st year 4years

If C2 is blank with no entry. With B2 entry and D2 entry with &” ”& then the displayed result will be
1st year After 1 Jan 2014 – so a double spacing between year and After.

Can anyone please help me?

Or even show me a better way or formulas I can use to get an automated column A answer based B to I.

Advanced appreciation and thank you for any help

**Pepe Le Mokko** · 12-11-2020, 03:04 AM

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

As you are new I will add it for you this time

https://www.mrexcel.com/board/thread...lease.1155698/

**Glenn Kennedy** · 12-11-2020, 09:17 AM

In K2, copied down:

Formula:

Please Login or Register  to view this content.

Notes.

In your original there were trailing spaces in A2 and A3. Removed.

There are two differences remaining.

1. "Apprentice " is not present in column A and "year/stage" has no spacing. In column B it is year / stage. That accounts fully for 13 character difference.

2. Column A has "Under 18" whereas column I has "Under 18 years"

Are these material to your needs?