Excel comments

[Kathleane]

Here's the scenario:
I create a row of client information. I attach a comment to the first cell in that row which contains the client's name. I copy and paste that row into other areas of the worksheet and other worksheets. The 1st cell does not change, however other cells in that row will have different information.

When I change the comment attached to that first cell I want the comment to be updated for every cell that contains that client's name.

Is this possible and if so how?

Thanks for any input.

I'm running Excel 2000.

[rraehal]

Set the cell you do not want to have change to the value of the original cell using an absolute reference.

For example:
If the comment is in cell A1 and you want that comment to appear on a different worksheet in Cell B2 the refreence would look like this:
='Worksheet1'!$A$1

If you wnated the information on the same workshhet in another cell it would look like this:
=$A$1

If you change the information on the original worksheet in Cell A1, all refrences in you workbook will change as well.

[Kathleane]

Thanks for your quick response. Hope you don't mind brainstorming this with me.

How would that work using the scenario below:

When a client makes an appointment I search for the last appointment they made (and it takes a bit of time to make sure that I pick up the LAST appointment so that the comment attached to their name is the most current). Then I copy and paste that row into either the same worksheet or another worksheet (worksheets are named according to the month and year).

I'm thinking that with your suggestion it would take more time to write the reference each time I copy and paste the row. Maybe a macro could do this?

If I could search backwards from the current date then I would know that the 1st time I hit that client's name is their last appointment and then I wouldn't need to go through all these gymnastics. Is there a way to do that?

Sorry, I'm pretty much a newbie at this but am trying to muddle through. I'm learning VBA so if the solution lies there and you talk to me like I'm five years old I could probably implement it.

[rraehal]

I am not sure how to implement something like this in VBA.

One thing you could do is make a master worksheet. Put the patients information - name, last visit date, etc... - in the main worksheet. Update that information each time they come to the office. You could locate the information on one sheet and the update the rest of your workbook as you do now.

It might be benficial to do a google search to see if there is a program that will suit your needs. You could also use MS Access and import you existing data into tables. You could then use access to write a program that could simplify your process.

[mobo57]

Short of a VB script, the only way to copy comments is to select the cell(s), right click mouse and slect copy, go to where you want to paste, if multiple places hold down the ctrl key and left click each cell, leave the cursor over the last cell and then right click again then select paste special and then select all to get the contents of the cell and the comments. On the menu it is edit>copy, then edit>paste special.

With this you will copy everything to the next cells. If you are semi coordinated with a mouse you can do the above in just a few seconds.

Unfortunately the comments are not data in the cell so they cannot be linked or referenced like the cells can.

In the past I have set up some pretty complicated spreadsheets for business. What I have discovered is that a lot of times Access is the better program to use. Kind of hard for a newbee, but once you get going you will really appreciate what it can do for you, expecially when you can set up a data base that looks just like a custom made, professionally written program.

[Kathleane]

Thank you all for your advice. Sounds like it's time for me to check out Access! I'm sure I'll be glad I did.