Link to home
Create AccountLog in
Avatar of Bright01
Bright01Flag for United States of America

asked on

Putting a value into a cell via a formula

EE Pros.,

I am looking for the appropriate formula that meets the following conditions:

If cell A5 has text in it, then insert the following text into cell E5  "Y"
If cell A5 does not contain anything (i.e. is blank) then insert the following text into cell E5 "N".  

Here's the tricky part.  The formula cannot reside in E5, it has to be in D5.

Help!  And thanks in advance.

B.
Avatar of oleggold
oleggold
Flag of United States of America image

put ee=ds
put Yr formula in ds
Hmm...I'd be interested in seeing what you're trying to accomplish.  The coding you're asking for probably isn't the best method of handling this situation. I usually put if statements in the cell being affected: [In cell E5]: =IF(A5="","N","")

Is there a reason the formula has to go in D5?
Avatar of Bright01

ASKER

Yes!   The reason is that the cell marked either Y or N (for yes or no) designates if a particular value should be represented in a graph.  If I put the formula in the cell, it doesn't recognize the value, it recognizes the formula instead.

When I try this with your formula; I get the formula to show only.

b.
Really?  That's strange.  

Quick question (not insulting your intelligence), are you making sure there's an "=" at the beginning of the forumla?  The formula should only show if the "=" is missing.  Otherwise, you should see a desired answer.

May I see a screen capture of your excel file (or the excel file itself) of this not working?  I may be able to code it in.
Which version of Excel are you using ?
Avatar of Rory Archibald
A formula cannot put a value into a cell other than the one that contains it. For certain chart types, returning #N/A rather than "" will usually prevent display on a chart but otherwise you will need code.
If E5 is formatted as text, it will show the formula rather than the result.

Thanks
Rob H
So you guys are saying the only way for a cell to populate another cell is through a macro that monitors a cell and copies/pastes it into the necessary cell?

B.
I did ask you which version of Excel you were using...
That sounds right. But there should be a way to do this that will produce the desired result.  Could you provide a copy of the workbook?
Running Excel 2010.
Ok.  I myself am using Excel 2007, hence am not able to give a confirmed answer.

However, you could try a few things:

1.  As Rob has suggested above, put your formula in cell E5
=if(len(a5)=0,"N","Y")

Then go to cell formatting and format the cell E5 as General.

2. Either press Ctrl+~ (that is press the Control key and while keeping it pressed press and release the tilde key, which is the key below the ESC key on your keyboard).  This is a toggle key combination, pressing it a second time reverses the action.
Yes, you need code (a worksheet_change event most likely). A formula solution will not work.
Rorya,

Is it a simple 4 to 5 line macro?  Can you provide a quick sample?  If it's more then that, no worries.........

B.
Try this one out:

Private Sub Worksheet_Change(ByVal target As Range)

    Dim KeyCells As Range
    Set KeyCells = Range("A5")
    If Not Application.Intersect(KeyCells, Range(target.Address)) _
        Is Nothing Then
   
    If Cells("a5").Value = IsText Then
    Cells("e5").Value = "Y"
   
    Else
    Cells("e5").Value = "N"
   
    End If
End Sub
Are we missing an End If or an End Else statement?

B.
Yes another end if before the end sub
Rorya,

I get an error code.  Here is the WS.

B.
Putting-text-into-a-cell.xlsm
ASKER CERTIFIED SOLUTION
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Rorya,

Thank you.  Well done.  Appreciate the lesson and code.

B.