Find and replace question

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

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 wrote:
>
> 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
>
> --
> devens8765
> ------------------------------------------------------------------------
> devens8765's Profile: http://www.excelforum.com/member.php...o&userid=28616
> View this thread: http://www.excelforum.com/showthread...hreadid=482769

--

Dave Peterson

Thanks for your reply.

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

Does anyone have any suggestions?

Any help is much appreciated.

Dave

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+).



devens8765 wrote:
>
> Thanks for your reply.
>
> I've tried replacing the values that I want in the master spreadsheet
> but it doesn't find any of them using "look in" formulas. But if I do a
> Edit|Find and "look in" values it finds the correct values. The code in
> the master spreadsheet actualy contains links, i.e. the path to the
> feeder sheet cells, not formulas.
>
> Does anyone have any suggestions?
>
> Any help is much appreciated.
>
> Dave
>
> --
> devens8765
> ------------------------------------------------------------------------
> devens8765's Profile: http://www.excelforum.com/member.php...o&userid=28616
> View this thread: http://www.excelforum.com/showthread...hreadid=482769

--

Dave Peterson

"devens8765" <[email protected]> wrote
in message news:[email protected]...
>
> 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

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

How is this done?

Thanks for any help in advance.

Dave

"devens8765" <[email protected]> wrote
in message news:[email protected]...
>
> 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

Thanks Bruno.

Is it possible to use the Edit|Replace command to do this, i.e. what is 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 thought this might be possible using Edit|Replace|Options command using Format - but I had the problem of not finding the actual values because of the links.

Many thanks once again for any help.

Dave

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