Find and replace question

[devens8765]

Dear Members,

I have an interesting question related to the functionality of th
"replace" command. I have an Excel sheet that has links to anothe
Excel sheet in a separate file (or workbook). It is probably easier t
refer to the Excel sheet with links as the master sheet and the Exce
sheet to be read-in as the feeder sheet to avoid any confusion. I woul
like to replace some of the values in the master sheet with alternativ
values. However, it can't seem to find the values as the "look in
option in replace has only "formulas" as the option, but I can "find
the values by changing the "look in" option to "values" in the "find
command.

MY question is: is it possible to replace values in my master sheet?

Thanks for any help in advance.

Dav

 

[Dave Peterson]

Excel only shows Formula in the Edit|Replace dialog.

But choosing Formulas will change the cells with constants (well, if they match)
as well as the strings in the formula.

I'm guessing that excel doesn't offer Values as an option is so that you don't
think that if you have this formula in a cell:

="asdf"&"ghi"&"jklm"
that evaluates to
asdfghijklm

that you can successfully change asdfgh to QWERTY in that formula.

Imagine that you had
=a1&b1&c1
Then it would be very difficult to know how to change that formula correctly so
that the value changed to what you wanted.

 

[devens8765]

Thanks for your reply.

I've tried replacing the values that I want in the master spreadshee
but it doesn't find any of them using "look in" formulas. But if I do
Edit|Find and "look in" values it finds the correct values. The code i
the master spreadsheet actualy contains links, i.e. the path to th
feeder sheet cells, not formulas.

Does anyone have any suggestions?

Any help is much appreciated.

Dav

 

[Dave Peterson]

Can you post what the cell contains and what you're trying to change?

And are you sure you set the "match case" and "match entire cell contents" the
way that they need to be set?

And cleared the formatting (if you're using xl2002+).

 

[Bruno Campanini]

Dear Members,

I have an interesting question related to the functionality of the
"replace" command. I have an Excel sheet that has links to another
Excel sheet in a separate file (or workbook). It is probably easier to
refer to the Excel sheet with links as the master sheet and the Excel
sheet to be read-in as the feeder sheet to avoid any confusion. I would
like to replace some of the values in the master sheet with alternative
values. However, it can't seem to find the values as the "look in"
option in replace has only "formulas" as the option, but I can "find"
the values by changing the "look in" option to "values" in the "find"
command.

MY question is: is it possible to replace values in my master sheet?

Thanks for any help in advance.

Dave

In S23 in Master sheet you have the formula:
='C:\Document\Excel\XLS\[Classifica.xls]Classifica'!B49
(link to the external Workbook - the Feeder)
which shows Dave in the very same cell.

You want to search for Dave in the range in order
to find it in S23 and then to replace it with Bruno and
to destroy the above formula.

Is it correct?

Bruno

 

[devens8765]

Yes, Bruno this is exactly what I want to do.

How is this done?

Thanks for any help in advance.

Dave

 

[Bruno Campanini]

Yes, Bruno this is exactly what I want to do.

How is this done?

Application.Index([v105:v110], Application.Match("w",
[v105:v110], 0)) = "EEE"

Where:
[V105;110] is your range
"w" the string your are searching for
"EEE" the string that will replace "w"

Ciao
Bruno

 

[devens8765]

Thanks Bruno.

Is it possible to use the Edit|Replace command to do this, i.e. what i
the "Find what" and "Replace with" parts?

I'm actually interested in replacing values with a formatted cell
which is blank and filled in green. Is this possible do to? I though
this might be possible using Edit|Replace|Options command using Forma
- but I had the problem of not finding the actual values because of th
links.

Many thanks once again for any help.

Dav

 

[devens8765]

Does anyone know how to find and replace values in a master sheet that
has links to a feeder sheet with a formatted cell containing nothing
apart from the cell being filled in green (or some other colour)? All
details contained in the thread.

Many thanks for any help?


Dave