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" <(E-Mail Removed)> wrote in message
news:c0736935-6257-48fc-ac78-(E-Mail Removed)...
>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" <ronREMOVETHIScode...@bigfoot.com>
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" <rpic...@yahoo.com> wrote in message
>
> news:c0736935-6257-48fc-ac78-(E-Mail Removed)...
>
>
>
> >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 <rpic...@yahoo.com> 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 <basilis...@gmail.com> wrote:
> On Nov 25, 7:25 pm, rpick60 <rpic...@yahoo.com> 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 <rpic...@yahoo.com> wrote:
> On Nov 25, 7:52 pm, Basilisk96 <basilis...@gmail.com> wrote:
>
>
>
> > On Nov 25, 7:25 pm, rpick60 <rpic...@yahoo.com> 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 <basilis...@gmail.com> wrote:
> On Nov 25, 8:00 pm, rpick60 <rpic...@yahoo.com> wrote:
>
>
>
>
>
> > On Nov 25, 7:52 pm, Basilisk96 <basilis...@gmail.com> wrote:
>
> > > On Nov 25, 7:25 pm, rpick60 <rpic...@yahoo.com> 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 <rpic...@yahoo.com> wrote:
> On Nov 25, 8:03 pm, Basilisk96 <basilis...@gmail.com> wrote:
>
>
>
>
>
> > On Nov 25, 8:00 pm, rpick60 <rpic...@yahoo.com> wrote:
>
> > > On Nov 25, 7:52 pm, Basilisk96 <basilis...@gmail.com> wrote:
>
> > > > On Nov 25, 7:25 pm, rpick60 <rpic...@yahoo.com> 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 <rpic...@yahoo.com> wrote:
> On Nov 25, 8:03 pm, Basilisk96 <basilis...@gmail.com> wrote:
>
>
>
> > On Nov 25, 8:00 pm, rpick60 <rpic...@yahoo.com> wrote:
>
> > > On Nov 25, 7:52 pm, Basilisk96 <basilis...@gmail.com> wrote:
>
> > > > On Nov 25, 7:25 pm, rpick60 <rpic...@yahoo.com> 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

On Nov 25, 8:12 pm, rpick60 <rpic...@yahoo.com> wrote:
> On Nov 25, 8:09 pm, rpick60 <rpic...@yahoo.com> wrote:
>
>
>
>
>
> > On Nov 25, 8:03 pm, Basilisk96 <basilis...@gmail.com> wrote:
>
> > > On Nov 25, 8:00 pm, rpick60 <rpic...@yahoo.com> wrote:
>
> > > > On Nov 25, 7:52 pm, Basilisk96 <basilis...@gmail.com> wrote:
>
> > > > > On Nov 25, 7:25 pm, rpick60 <rpic...@yahoo.com> 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- Hide quoted text -
>
> - Show quoted text -

Ok now that works how to I put the message on the next line. (alt +
enter)
I have added the .wraptext but i want to start the note underneath the
link.