Bright01

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.

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.

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?

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

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.

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.

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 ?

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

Thanks

Rob H

ASKER

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.

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?

ASKER

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.

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.

ASKER

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.

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 Dim KeyCells As Range

Set KeyCells = Range("A5")

If Not Application.Intersect(KeyC

Is Nothing Then

If Cells("a5").Value = IsText Then

Cells("e5").Value = "Y"

Else

Cells("e5").Value = "N"

End If

End Sub

ASKER

Are we missing an End If or an End Else statement?

B.

B.

Yes another end if before the end sub

ASKER

ASKER CERTIFIED SOLUTION

membership

Create an account to see this answer

Signing up is free. No credit card required.

ASKER

Rorya,

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

B.

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

B.

put Yr formula in ds