changing formulas to values so that they will be recognized by Find and Replace

[akeane]

Hello,

Here is my dilemma:

I have a list of patient names in Column A on Sheet 1 that will be updated over time.

I have linked Column A on Sheet 2 to Column A on Sheet 1 so that any changes made to the list on Sheet 1 will automatically show up on Sheet 2.

However, when I need to search for a patient name on Sheet 2, I cannot use the "Find" feature because it is reading the formula (='Sheet1'!A1), and not the value (Patient Name).

I thought about using a second column on Sheet 2 to convert the formulas to values, but this column is not automatically updated when a new entry is made (i.e., an new patient is added to the list on Sheet 1).

Any suggestions?

I appreciate any tips you can offer!

Thanks,

Angela

[swatsp0p]

When you use the FIND feature, I am assuming you are referring to the Edit>Find (CTRL+F) tool.

If that is correct, you can set the Options to Look in: Values. This will then FIND the name that is returned by the formula, not the formula itself.

To do this, press CTRL+F, enter the name to look for in the Find What box, then click on Options>>. The Find and Replace box will enlarge to display several options. Click the down arrow in the Look in: box and choose Values. Click Find Next.

Does this work for you?

Bruce

[akeane]

Yes - thank you so much for your help! I must admit I feel a bit sheepish about there being such an easy and straightforward solution!

[swatsp0p]

Angela: I'm glad this worked for you. Thanks for the feedback, it is always appreciated. No need to feel sheepish, as Excel has thousands of 'hidden' ways to do things. I'm learning new stuff every day!

Cheers!

Bruce