Struggling with an easy formula and looking for some help

Rashie

Board Regular
Joined
Jun 5, 2015
Messages
55
Hey Gang,

Would someone do me a solid and give me a hand with this?

I'm trying to get as much of a sheet to fill out as possible when pasting a value to speed things up in my team. One thing I can't get to work is the below in cell B25.

=IF(LEFT(C25)="S","Value1","Value2")

What this formula currently does is look if the first letter in an adjacent cell, if the first letter is S the value in B25 will change to "Value1" which is great. Problem is I want a third option.

1. If C25 cell value begins with S = Value1
2. If C25 does not begin with S = Value2
3. If C25 is blank = B25 should also be blank.

Is there a way I can keep the cell blank without the use of conditional formatting within my formula until a user has inputted a value?

Thanks everyone
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Try using a Nested IF:
Code:
[COLOR=#333333]=IF(C25="","",IF(LEFT(C25,1)="S","Value1","Value2"))[/COLOR]
 
Last edited:
Upvote 0
Hey Joe,

Thanks for the response and looking at that, I'm always in awe when I visit here...
Although this works it doesn't autofill with "value1" or "value2" until I've filled out data in the row underneath?
Calculation is set to automatic so I'm not sure what the problem is.

Any ideas?

Thanks,

Rich
 
Upvote 0
Although this works it doesn't autofill with "value1" or "value2" until I've filled out data in the row underneath?
Sounds to me like the row reference in your formula may be one row off.
Double-check your formula and make sure that you have written your formula to reference the right rows in the right places.

If it still does not work, lay out an example for us (letting us know your exact formula, and what is in each cell being referenced by the formula).
 
Upvote 0
Matt,

You response looks pretty much identical to what I posted in my first response, up in post #2.
 
Upvote 0
Hey Both,

Thanks for your input too Matt - but I've gone with Joe's answer. I'm sure you won't be crushed by that!

Joe - No idea why it wasn't working. When conditional formatting was applied to the sheet it was fine, odd but thanks all the same.

Rich
 
Upvote 0
Yes, I was inattentive.

Your formula:
=IF(C25="","",IF(LEFT(C25,1)="S","Value1","Value2"))

My formula:
=IF(C25="","",IF((LEFT(C25,1)="S"),"Value1","Value2"))

I'm sorry.
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top