I have a simple question. I have a worksheet that has a list of item
to be purchased that is referenced from another sheet. =Sheet1!E10
What i want to do is keep the link but add text after the formula.

My end result should be =Sheet1!E10 & " add some kind of note"

How can I set up a macro to add text to the formula and allow the user
to keep the link to sheet1?

You have a couple options:

1) Just add a comment to the cell

From the Excel Main Menu:
<insert<comment

or
2) Use this technique:
=Sheet1!E10+N("enter your comment here")

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"rpick60" wrote in message
...
I have a simple question. I have a worksheet that has a list of item
to be purchased that is referenced from another sheet. =Sheet1!E10
What i want to do is keep the link but add text after the formula.

My end result should be =Sheet1!E10 & " add some kind of note"

How can I set up a macro to add text to the formula and allow the user
to keep the link to sheet1?

The problem is that the comments to do not carried over to the 3rd and
4th sheet. I am using lists to sort by vender, material and due date.

Once I get an updated list I still wan to keep the "note"

On Nov 25, 7:38 pm, "Ron Coderre"
wrote:
You have a couple options:

1) Just add a comment to the cell

From the Excel Main Menu:
<insert<comment

or
2) Use this technique:
=Sheet1!E10+N("enter your comment here")

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"rpick60" wrote in message

...



I have a simple question. I have a worksheet that has a list of item
to be purchased that is referenced from another sheet. =Sheet1!E10
What i want to do is keep the link but add text after the formula.

My end result should be =Sheet1!E10 & " add some kind of note"

How can I set up a macro to add text to the formula and allow the user
to keep the link to sheet1?- Hide quoted text -

- Show quoted text -

On Nov 25, 7:25 pm, rpick60 wrote:
I have a simple question. I have a worksheet that has a list of item
to be purchased that is referenced from another sheet. =Sheet1!E10
What i want to do is keep the link but add text after the formula.

My end result should be =Sheet1!E10 & " add some kind of note"

How can I set up a macro to add text to the formula and allow the user
to keep the link to sheet1?

It looks like you need to simply concatenate the result of the
existing formula with some additional text. Try adding this code to a
Module:

Public Sub AddTextToFormula()
txt = InputBox("Enter text to add to formula:")
Set rng = Selection
rng.Formula = rng.Formula & " & " & Chr(34) & Chr(32) & txt &
Chr(34)
End Sub

Then run AddTextToFormula as a macro. Note that this operates on the
current selected range, so be sure to select the cell of interest
before running the macro.

Cheers,
-Basilisk96

On Nov 25, 7:52 pm, Basilisk96 wrote:
On Nov 25, 7:25 pm, rpick60 wrote:

I have a simple question. I have a worksheet that has a list of item
to be purchased that is referenced from another sheet. =Sheet1!E10
What i want to do is keep the link but add text after the formula.

My end result should be =Sheet1!E10 & " add some kind of note"

How can I set up a macro to add text to the formula and allow the user
to keep the link to sheet1?

It looks like you need to simply concatenate the result of the
existing formula with some additional text. Try adding this code to a
Module:

Public Sub AddTextToFormula()
txt = InputBox("Enter text to add to formula:")
Set rng = Selection
rng.Formula = rng.Formula & " & " & Chr(34) & Chr(32) & txt &
Chr(34)
End Sub

Then run AddTextToFormula as a macro. Note that this operates on the
current selected range, so be sure to select the cell of interest
before running the macro.

Cheers,
-Basilisk96

Thank but hat work on a range of cells, I need it to work on a active
cell

On Nov 25, 8:00 pm, rpick60 wrote:
On Nov 25, 7:52 pm, Basilisk96 wrote:



On Nov 25, 7:25 pm, rpick60 wrote:

I have a simple question. I have a worksheet that has a list of item
to be purchased that is referenced from another sheet. =Sheet1!E10
What i want to do is keep the link but add text after the formula.

My end result should be =Sheet1!E10 & " add some kind of note"

How can I set up a macro to add text to the formula and allow the user
to keep the link to sheet1?

It looks like you need to simply concatenate the result of the
existing formula with some additional text. Try adding this code to a
Module:

Public Sub AddTextToFormula()
txt = InputBox("Enter text to add to formula:")
Set rng = Selection
rng.Formula = rng.Formula & " & " & Chr(34) & Chr(32) & txt &
Chr(34)
End Sub

Then run AddTextToFormula as a macro. Note that this operates on the
current selected range, so be sure to select the cell of interest
before running the macro.

Cheers,
-Basilisk96

Thank but hat work on a range of cells, I need it to work on a active
cell

You're right. Then just use:

Public Sub AddTextToFormula()
txt = InputBox("Enter text to add to formula:")
ActiveCell.Formula = ActiveCell.Formula & " & " & Chr(34) &
Chr(32) & txt & Chr(34)
End Sub

Cheers,
-Basilisk96

On Nov 25, 8:03 pm, Basilisk96 wrote:
On Nov 25, 8:00 pm, rpick60 wrote:





On Nov 25, 7:52 pm, Basilisk96 wrote:

On Nov 25, 7:25 pm, rpick60 wrote:

I have a simple question. I have a worksheet that has a list of item
to be purchased that is referenced from another sheet. =Sheet1!E10
What i want to do is keep the link but add text after the formula.

My end result should be =Sheet1!E10 & " add some kind of note"

How can I set up a macro to add text to the formula and allow the user
to keep the link to sheet1?

It looks like you need to simply concatenate the result of the
existing formula with some additional text. Try adding this code to a
Module:

Public Sub AddTextToFormula()
txt = InputBox("Enter text to add to formula:")
Set rng = Selection
rng.Formula = rng.Formula & " & " & Chr(34) & Chr(32) & txt &
Chr(34)
End Sub

Then run AddTextToFormula as a macro. Note that this operates on the
current selected range, so be sure to select the cell of interest
before running the macro.

Cheers,
-Basilisk96

Thank but hat work on a range of cells, I need it to work on a active
cell

You're right. Then just use:

Public Sub AddTextToFormula()
txt = InputBox("Enter text to add to formula:")
ActiveCell.Formula = ActiveCell.Formula & " & " & Chr(34) &
Chr(32) & txt & Chr(34)
End Sub

Cheers,
-Basilisk96- Hide quoted text -

- Show quoted text -

I get an error on this line compile error syntax error

ActiveCell.Formula = ActiveCell.Formula & " & " & Chr(34) &

On Nov 25, 8:09 pm, rpick60 wrote:
On Nov 25, 8:03 pm, Basilisk96 wrote:





On Nov 25, 8:00 pm, rpick60 wrote:

On Nov 25, 7:52 pm, Basilisk96 wrote:

On Nov 25, 7:25 pm, rpick60 wrote:

I have a simple question. I have a worksheet that has a list of item
to be purchased that is referenced from another sheet. =Sheet1!E10
What i want to do is keep the link but add text after the formula.

My end result should be =Sheet1!E10 & " add some kind of note"

How can I set up a macro to add text to the formula and allow the user
to keep the link to sheet1?

It looks like you need to simply concatenate the result of the
existing formula with some additional text. Try adding this code to a
Module:

Public Sub AddTextToFormula()
txt = InputBox("Enter text to add to formula:")
Set rng = Selection
rng.Formula = rng.Formula & " & " & Chr(34) & Chr(32) & txt &
Chr(34)
End Sub

Then run AddTextToFormula as a macro. Note that this operates on the
current selected range, so be sure to select the cell of interest
before running the macro.

Cheers,
-Basilisk96

Thank but hat work on a range of cells, I need it to work on a active
cell

You're right. Then just use:

Public Sub AddTextToFormula()
txt = InputBox("Enter text to add to formula:")
ActiveCell.Formula = ActiveCell.Formula & " & " & Chr(34) &
Chr(32) & txt & Chr(34)
End Sub

Cheers,
-Basilisk96- Hide quoted text -

- Show quoted text -

I get an error on this line compile error syntax error

ActiveCell.Formula = ActiveCell.Formula & " & " & Chr(34) &- Hide quoted text -

- Show quoted text -

Sorry i copied it wrong. it works great. thanks for the help

On Nov 25, 8:09 pm, rpick60 wrote:
On Nov 25, 8:03 pm, Basilisk96 wrote:



On Nov 25, 8:00 pm, rpick60 wrote:

On Nov 25, 7:52 pm, Basilisk96 wrote:

On Nov 25, 7:25 pm, rpick60 wrote:

I have a simple question. I have a worksheet that has a list of item
to be purchased that is referenced from another sheet. =Sheet1!E10
What i want to do is keep the link but add text after the formula.

My end result should be =Sheet1!E10 & " add some kind of note"

How can I set up a macro to add text to the formula and allow the user
to keep the link to sheet1?

It looks like you need to simply concatenate the result of the
existing formula with some additional text. Try adding this code to a
Module:

Public Sub AddTextToFormula()
txt = InputBox("Enter text to add to formula:")
Set rng = Selection
rng.Formula = rng.Formula & " & " & Chr(34) & Chr(32) & txt &
Chr(34)
End Sub

Then run AddTextToFormula as a macro. Note that this operates on the
current selected range, so be sure to select the cell of interest
before running the macro.

Cheers,
-Basilisk96

Thank but hat work on a range of cells, I need it to work on a active
cell

You're right. Then just use:

Public Sub AddTextToFormula()
txt = InputBox("Enter text to add to formula:")
ActiveCell.Formula = ActiveCell.Formula & " & " & Chr(34) &
Chr(32) & txt & Chr(34)
End Sub

Cheers,
-Basilisk96- Hide quoted text -

- Show quoted text -

I get an error on this line compile error syntax error

ActiveCell.Formula = ActiveCell.Formula & " & " & Chr(34) &


So do I :))
....unless I tack on the rest of the statement from the line that
follows:
Chr(32) & txt & Chr(34)

The posts unfortunately break up lines into shorter ones, hence your
error.

But just to be 100% safe (with my tongue in cheek), you can use this
version:
ActiveCell.Formula = _
ActiveCell.Formula & _
" & " & _
Chr(34) & _
Chr(32) & _
txt & _
Chr(34)

Cheers,
-Basilisk96