Formula to enter text in one cell based on text in another?

April 21, 2019 at 09:49:21
Specs: Mac el Capitan, 16Gig RAM and a decent Mac Mini processor...
Attempting to create an Excel formula to enter text into one cell based on text in another…

Using Mac Office/Excel 201l; running Mac OSX el capitan

Tried using the IF command; and a few other variations on that sequence. So many cannot recall all of them just now… But no matter

Example

Spread sheet with assorted columns :(A) Date; (B) money in. (C) money out, (D) balance remaining – calculated using an excel formula.

No problems with that part… it all works fine

Two more columns (E) and (F) are the problem area…

Column (E) contains text referring to (B) money in, and/or (C) money out. The text describes the source of money in (B) – or - destination/expenditure in (C)

Columns (F) describes how money was received (B) or paid (C).

e.g. Row 9085 shows £18.45 paid to “Supermarket” (no quotes)

Wish to enter “Sw” (no quotes) automatically in adjacent cell F9085; and thereafter whenever column (E) contains “Supermarket” (no quotes) “Sw” (no quotes) should appear in the adjacent cell in column (F).

The following example may help show more clearly?


  
9082      A        B          C         D           E            F
9083  04-03-18   158.57             68,886.85    Bill Gates     gift
9083  05-03-18             75.00    66,810.85    TASK            DD
9085  05-03-18             18.45    66,792.40    Supermarket     Sw
9086  05-03-18             20.00    66,772.40    PO              Sw

In the above example the first entry is fictional gift from a certain Bill Gates...; the other three entries are money paid out - variously.

I’ve trawled the web and found innumerable sites seeming to show how to do this – but none appear to work…?

Additional question; re’ the quotation marks used in Excel formulae. Are they the standard “ “ as on my Apple keyboard, or do I have to use the single apostrophe “ (twice); or is there another character not included on my standard Apple keyboard?

Excel is world unto itself...

Hopefully the well "gnown gurus ' n gnomes" here can assist (please...)

Possibly there will be a supplementary CFH (Call For Help) once I've gotten across the basic situation above. That will be a development of the above - if I can't work it out for myself.

Thank you all en-avance...

trvlr

message edited by trvlr


See More: Formula to enter text in one cell based on text in another?

Report •

#1
April 21, 2019 at 13:06:48
Maybe I'm missing something, but it appears that a standard IF function should get you the results you need, with just a bit more information.

However, you've only given one example of your desired output, so that's all I can address at this time. If the list of "abbreviations" for various outlays is long, there may be a better way than an IF function, but we would need to know the details.

The basic syntax for a simple IF function is as follows:

=IF(logical_test, value_if_true, value_if_false)

=IF(E9085="Supermarket", "Sw", Not sure what you want if E9085 doesn't contain Supermarket)

As far as the quotes, they should be the standard double quotes produced by a keyboard:

"text_goes_here"

However, some of the quotes that you posted won't work. Notice the slant: “ “

The quotes around this phrase appear to be the correct ones though.

"gnown gurus ' n gnomes"

I'm not sure why you have 2 different styles in your post.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Report •

#2
April 21, 2019 at 14:56:53
Hi DD:

Yup I tried the standard IF option - and using your example (similar to one of my efforts):

=IF(E9085=''Supermarket'', ''Sw'', '' '')

I still get the formula displayed - not the desired Sw.

If omit the space between the last pair of double quotes (the negative option) I get that formula instead of the desired Sw.

I have tried using assorted words in place of Supermarket; the actual preferred being Waitrose.

I composed my initial post using Word and then used copy 'n paste to put it here.

Thus for this line in my initial post:

Are they the standard “ “ as on my Apple keyboard,

I used the "upper/shift" keyboard set of quotetion marks - the characters (double set as used at start and end of a quotation) have the curved style. But while they seem to have reproduced here as sloping in my initial post, they won't this time...; instead reproducing here as straight up/down style...They appear as straight up/down characters " " ...

In the samples below Aaa was using two singles (in effect the apostrophe x 2); and Bbb using the standard (double) default quotation marks x 1.

'' Aaaa '' - the quotation marks being two '

" Bbbb " - the quotation marks being a single "

For my latest test (as above) I used the single ' twice each time.

To include the negative response in the formula isn't essential - as there isn't one; but I guess it's useful/wiser to set that negative to leave a blank entry?

When this initial how to is resolved, I would like to have several options/abbreviations - but each would have to depend on a different defining word. E.G Supermarket - Sw; Gov-trf - payment; and so on.

But if I can get the single Supermarket one to work that may be all I really require;. the rest is more like icing on the cake...

I am wondering if there is difference between Windows MS Office/Excel and Mac MS Office/Excel syntax for the IF formulae?

Although when I trawled (Google of course) I did include a specific reference to Mac Office/Excel 2011 - which is the Mac version I have.

message edited by trvlr


Report •

#3
April 21, 2019 at 17:40:03
Addendum...

I dun a little more look see specifically to the formatting of the required column F...

It seems I had set column F to Text... Resetting it to General allows the IF formula to work.

General setting has no specific format; whereas Text dictates that it displays what is typed in as text.

I tested the IF formula on another spread sheet and used a columns there (for the formula result) which had not been (pre)formatted... Finding it worked there set me hunting...

I will continue with this thread a little further if I may; with a view to multiple options - which will outline shortly.

But thus far many thanks DD; you did confirm my initial efforts as being on track.

Meanwhile... Time for hot choccy and bed... it's très tard ici.


Report •

Related Solutions

#4
April 21, 2019 at 17:59:22
OK, let's start with something sort of unrelated, but important:

=IF(E9085=''Supermarket'', ''Sw'', '' '') vs. =IF(E9085=''Supermarket'', ''Sw'', '''')

The first formula will return a space if the logical test is FALSE, the second will return a null string, basically an empty cell. In many cases this will not make a difference, but in certain situations, depending on what is being done with the result of the formula. The "best practice" is: when you want there to be nothing in the cell, use "" because a space (" ") is not nothing.

re: I still get the formula displayed - not the desired Sw.

Well, you certainly didn't mention this in your OP. ;-) I was under the impression that you were getting a result, just not the result you wanted.

There are 2 reasons (at least) that I can think of to explain the showing of the formula as opposed to a result. (BTW...I don't have a Mac, so I'm basing this on Windows experience)

1 - The cell is formatted as Text. It should be formatted as General, which is usually the default.
2 - The sheet itself is set to display formulas. In WIndows, the option is on the Formula ribbon, cleverly worded as Show Formulas. If the option is Green it's set to On.

re: I composed my initial post using Word and then used copy 'n paste to put it here.

That trips a lot of people up. Some folks have been know to write a formula in Word and then wonder why it doesn't work in Excel. When they copy and paste it here, it jumps right out at us, only because we've seen it so many times before.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#5
April 22, 2019 at 15:44:42
Hi Again DD (et alii...)

Developing my sample Excel sheet/formula a little further.

But first... many thanks for the explanation/clarification re’ “ “ and “”

And apologies for lack of clarity as in

re: I still get the formula displayed - not the desired Sw.

Well, you certainly didn't mention this in your OP. ;-) I was under the impression that you were getting a result, just not the result you wanted.

Easy on occasion to miss a wee fine point of clarity. Having writ more than a few how to and answered a few CFH here (and elsewhere) I know how often a simple yet important snippet can be left out inadvertently.

As you’ll have noted I did manage to twig the cell format being set to Text not General as the cause of the initial problem as posted…

I am now trying to modify and/or extend the basic IF formula.

To show what I’m after I have extended my initial sample Excel sheet a little with some extra rows/cells, and included it here.

9082      A             B         C          D            E            F
9083  04-03-18       158.57             68,886.85    Bill Gates       gift
9084  05-03-18                 75.00    66,810.85    TASK             DD
9085  05-03-18                 18.45    66,792.40    Supermarket      Sw
9086  05-03-18                 20.00    66,772.40    PO               Sw
9087  06-03-18                                       TASK             DD
9088  06-03-18                                       Cats             Sw
9089  07-03-18                                       TASK             DD

What I’m aiming for is to “automate” the column F entry to DD for all cells in column E which contain TASK.

If I use:

=IF(E9084="TASK", "DD", "")

and then extend that formula down the table (by the usual method of dragging the bottom right corner of the formula cell down) to encompass many cells below (e.g. E9084 onwards, to say E1000)

it immediately wipes all entries present in the F cells below F9084 which do not qualify according to the reference text (TASK) in the associated E cell; leaves those cells blank. If there is other data already in those F cells I wish to preserve it.

Is it possible to create a version of this formula which allows it to be extended to subsequent cells in column F so that it does not do anything if the reference text in E (TASK in this example) is not present. That it only acts “when” the reference TASK is present.

Does the above make sense; can you see/follow what I’m aiming for?

Yup... I’m aware that formulae created in Word won’t work in Excel. Bin there got the T-shirt…

In my initial post I copied my sample Excel sheet straight from Excel into my post here and discovered of course that it lost all formatting. It just appeared as several lines of data, each with its own cell number of course. I had to go digging for your and/or Mike M’s posts to find the link to how to post vba etc.. I wasn’t fully aware of the those last two “icons” top left of text box, never having had to use anything they allow (post data…) ‘til now.

Finding the how to amongst the long list of how to was a seemingly endless journey; hence the hunt for your and Mike M's posts...

One lives ‘n learns…

Addendum:

I know I could/can extend the formula down the F column ahead of entries various in E; and then when I enter text other than TASK (in my example) in the E cell(s), the F cells below are still governed by the formula.

message edited by trvlr


Report •

#6
April 22, 2019 at 20:21:34
I'm not exactly sure where to start this answer. There are more than few points to address and if your response #5 was a stand-alone question, life would be a breeze. However, earlier in this thread you asked a different question:

"Wish to enter “Sw” (no quotes) automatically in adjacent cell F9085; and thereafter whenever column (E) contains “Supermarket” (no quotes) “Sw” (no quotes) should appear in the adjacent cell in column (F)."

I believe we solved that one (sort of) with:

=IF(E9085="Supermarket", "Sw","")

If E9085 contains "Supermarket" then display "Sw", if not, display an empty cell.

Now you are asking to see DD in Column F if the cell in Column E contains "TASK", which you accomplished by using a single formula which indeed gives you what you want, but unfortunately wreaks havoc with whatever was in the cell prior to you using the "new" formula.

What you are running into is the fact that an Excel cell can only contain one thing at a time. It can contain a number, it can contain a text string or it can contain a formula. (There may a few other things that a cell can contain, but that's enough examples for the purpose of this exercise.)

I look at it this way:

If I enter the text string DD in A1, then A1 contains DD. DD is in A1.

If I type DD in B1 and then enter the formula =B1 in A1, then A1 will display DD, but it doesn't contain DD. It contains a formula that returns the value that is contained in a different cell.

Therefore, when you have =IF(E9085="Supermarket", "Sw","") in a cell and it displays Sw, the Sw isn't really there. The formula is. Now when you drag =IF(E9084="TASK", "DD", "") down into that cell, you are replacing one formula with another and you'll never see Sw in that cell again.

OK, so how do we address that issue? We use a Nested IF.

Remember the syntax of a basic IF function?

=IF(logical_test, value_if_true, value_if_false)

Well, the use of the term value is a tad misleading. What if we replaced the value_if_false with another IF function instead of a value? As long as we are careful with our syntax, we can return 2 different values:

=IF(logical_test, value_if_true, IF(logical_test, value_if_true, value_if_false))

=IF(E9085="Supermarket","SW",IF(E9085="Task","DD",""))

Need more choices?

=IF(logical_test, value_if_true, IF(logical_test, value_if_true, IF(logical_test, value_if_true, value_if_false)))


Further, we can nest all sorts of stuff:

=IF(A1="NYC",SUM(B1:B10),IF(A1="Boston","Go Sox!",AVERAGE(12,52,12,1,2)))

OK, so we can keep nesting IF's until we have an IF for all of your criteria (Supermarket, TASK, PO, Cats, etc. but that is going to get kind of long and cumbersome.

In this case, I would consider using a VLOOKUP Table instead.

Pick a location for your lookup table of Column E values and the expected result in Column F.

I'll use D1:E4

         D                 E
1      TASK                DD
2      Supermarket         Sw
3      PO                  Mail   
4      Cats                Meow

Now, in column F, instead of the long Nested IF, try this:

=VLOOKUP(E9085,$D1:$E4,2,0)

You are telling the VLOOKUP function to "lookup" the value that is in E9085 in the first column of the VLOOKUP table (Column D) and return the corresponding value from the 2nd column (Column E)

(I specifically did not use A1:B4 because I want to make sure that you know that the VLOOKUP formula does not use worksheet column numbers but instead uses the columns within the lookup table. I didn't want you to confuse Column B with Column 2. Column B is indeed the 2nd column of the sheet, but it might not be the 2nd column of the lookup table.)

OK. I'm fried. Let me know what you think about all this.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#7
April 23, 2019 at 06:21:33
mmm I can understand "being fried" at this point... I was similarly so when I posted my intital CFH...

I did consider the multiple IF formula and like you concluded it could be a "little long"...; although there usually only a few "regular" entries that occur and thus that might be a viable path...

The nesting approach I have to study more carefully. This I will do via on-line tutorials so as not to overburden, nor abuse, you and your time. I will come back re' that once I have "done some more homework".

Similarly VLOOKUP is complete ?? to me just now; need time to digest that method. So again a little self study would be wise before coming back to gain clarity there.

I regularly follow Excel posts here and usually store references to those you and Mike M (amongst others too) contribute. They're most helpful and one can/to which doth learn a lot from them.

I did consider a simple "workaround/"fudge" of sorts... That was to format the entire column ("F "in my example) with say the Supermarket formula, probably included another similar store (e.g Store) option as well. I tested that approach on a dummy sheet and discovered that one can manually overwrite an existing formatted cell - leaving those below it intact ; i.e. the formula is still active below the cell which has been overwritten (manually).

As I say - it's a fudge... and whilst I accept a fudge at times, I prefer to know how to do whatever without recourse to them (unless the situation, time etc. demands otherwise).

Homework/self-study later today, this evening; and then likely I will be back for clarification...

I'm presuming your in N.Am - although DerbyDad doth sort of imply the UK...?

Again many thanks for advice and more thus far!


Report •

#8
April 23, 2019 at 06:43:04

Report •

#9
April 23, 2019 at 07:20:36
Another Nesting option that should be a little shorter and is based on your example data where it appears that "Sw" is a valid result for multiple inputs...

       E               F
1     TASK             DD
2     Supermarket      Sw
3     PO               Sw
4     TASK             DD
5     Cats             Sw
6     TASK             DD

=IF(OR(E1="Supermarket",E1="PO",E1="Cats"),"Sw","DD")

The logical_test is an OR function. As long as any of the OR arguments are TRUE, the OR will be TRUE and the value_if_true will be returned: Sw.

Since the only other input that your example shows is TASK, we don't really have to test for that value because, by default, if the E cell doesn't contain any of the 3 values referenced by the OR, it must contain TASK and the value_if_false will be returned: DD

Again, this suggestion is based solely on the example data that you posted. The main point is that you don't need a separate logical_test for every single input if multiple inputs should produce the same output. All you need is a logical_test that will return TRUE (or FALSE) when you need it to.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Report •

#10
May 4, 2019 at 08:09:26
Hi DD...

I'm going with the nested IF option for now. It does cover a list of possible entries in the E column nicely.

I shall play with the vlookup system shortly.

Understand whence DerbyDad03; thank you for the youtube.

I note that in the sopabox derby (as in your posted youtube) there are a lot of girls involved too; not just boys...

In my younger days - just after WW2 - soapbox cars were the preserve of boys; I don't ever recall ever seeing any girl in one. Mind you I don't recall any racing events either. We just made a car - more like a set of boards siting on whatever wheels we could find at the time. These "car(rt)s" were more like a trolley with a steerable front pair (if we were able to) of wheels... . But they fun to play with...


Report •

#11
May 4, 2019 at 20:18:58
Derby racing is definitely a non-gender-specific sport. And if you take a female racer lightly, it is at your own peril. Some of the most competitive racers I know, including my daughters, are female.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

Ask Question