This should be simple, at least I would think so, but I can't find a way to
do it. I have a workbook that imports data from another workbook, then
populates another sheet with which I produce my output. The output sheet
shows all the appropriate date but the cell reference is simply the link to
another worksheet.

For example: My sheet shows the number 100 but in the cell it reads
=otherworkseet!a1 (the source from which the data is extracted.

I want to find out how I can use find or a formula to show me every cell
that has the 100 in it without converting it to raw data. Simply using Find
does not locate any data. The result may be identical to other cells but it
comes from seperate locations. I will have a few dozen types of data I will
be trying to find in this manner and probably use replace to give the
intended effect, or if it helps me with creating a formula to look things up
automatically I can perhaps create a handy macro. For now, though, I would
be very happy with just being able to find the information more easily than
to manually identify each cell myself. I am also looking to eliminate the
human error that comes along with the manual identification.

Thanks in advance for any help or ideas you might provide!

When using EditFind make sure you have "Lookin:" option set to Formulas.


Gord Dibben MS Excel MVP

On Mon, 26 Apr 2010 08:05:01 -0700, joemc911
wrote:

This should be simple, at least I would think so, but I can't find a way to
do it. I have a workbook that imports data from another workbook, then
populates another sheet with which I produce my output. The output sheet
shows all the appropriate date but the cell reference is simply the link to
another worksheet.

For example: My sheet shows the number 100 but in the cell it reads
=otherworkseet!a1 (the source from which the data is extracted.

I want to find out how I can use find or a formula to show me every cell
that has the 100 in it without converting it to raw data. Simply using Find
does not locate any data. The result may be identical to other cells but it
comes from seperate locations. I will have a few dozen types of data I will
be trying to find in this manner and probably use replace to give the
intended effect, or if it helps me with creating a formula to look things up
automatically I can perhaps create a handy macro. For now, though, I would
be very happy with just being able to find the information more easily than
to manually identify each cell myself. I am also looking to eliminate the
human error that comes along with the manual identification.

Thanks in advance for any help or ideas you might provide!

Hi Gord. I have checked to make sure I was looking in Formulas but here's a
new twist I hadn't discovered until this prompted me to try something else.

I used Find and was able to locate every cell I wanted by looking in
"Values" but find and replace does not allow me that option, only look in
"formulas". This tells me I definitely need to find some way to identify the
value search in a macro or formula that will then allow me to change the
contents of the cell, actually the cell properties such as color, when I can
find the data value I search for.

Any more ideas that might help?

Thanks for the response!

"Gord Dibben" wrote:

When using EditFind make sure you have "Lookin:" option set to Formulas.


Gord Dibben MS Excel MVP

On Mon, 26 Apr 2010 08:05:01 -0700, joemc911
wrote:

This should be simple, at least I would think so, but I can't find a way to
do it. I have a workbook that imports data from another workbook, then
populates another sheet with which I produce my output. The output sheet
shows all the appropriate date but the cell reference is simply the link to
another worksheet.

For example: My sheet shows the number 100 but in the cell it reads
=otherworkseet!a1 (the source from which the data is extracted.

I want to find out how I can use find or a formula to show me every cell
that has the 100 in it without converting it to raw data. Simply using Find
does not locate any data. The result may be identical to other cells but it
comes from seperate locations. I will have a few dozen types of data I will
be trying to find in this manner and probably use replace to give the
intended effect, or if it helps me with creating a formula to look things up
automatically I can perhaps create a handy macro. For now, though, I would
be very happy with just being able to find the information more easily than
to manually identify each cell myself. I am also looking to eliminate the
human error that comes along with the manual identification.

Thanks in advance for any help or ideas you might provide!

.

First you couldn't find the data, now you do find the data.

I can find the data when "Lookin:" is formulas or values using 100 as an
example.

=Sheet1!A1 returns 100

Are you confident you have found all instances?

If missing some perhaps 100 is just the displayed formatted value.

If you add more decimal places is it still 100?

Do you just want to format the "Finds" to a color?

After you have found the cells, in the "Found" dialog hit CTRL + a which
selects all the "founds"

Now click on color picker to color the cells.


Gord

On Mon, 26 Apr 2010 10:25:01 -0700, joemc911
wrote:

Hi Gord. I have checked to make sure I was looking in Formulas but here's a
new twist I hadn't discovered until this prompted me to try something else.

I used Find and was able to locate every cell I wanted by looking in
"Values" but find and replace does not allow me that option, only look in
"formulas". This tells me I definitely need to find some way to identify the
value search in a macro or formula that will then allow me to change the
contents of the cell, actually the cell properties such as color, when I can
find the data value I search for.

Any more ideas that might help?

Thanks for the response!

"Gord Dibben" wrote:

When using EditFind make sure you have "Lookin:" option set to Formulas.


Gord Dibben MS Excel MVP

On Mon, 26 Apr 2010 08:05:01 -0700, joemc911
wrote:

This should be simple, at least I would think so, but I can't find a way to
do it. I have a workbook that imports data from another workbook, then
populates another sheet with which I produce my output. The output sheet
shows all the appropriate date but the cell reference is simply the link to
another worksheet.

For example: My sheet shows the number 100 but in the cell it reads
=otherworkseet!a1 (the source from which the data is extracted.

I want to find out how I can use find or a formula to show me every cell
that has the 100 in it without converting it to raw data. Simply using Find
does not locate any data. The result may be identical to other cells but it
comes from seperate locations. I will have a few dozen types of data I will
be trying to find in this manner and probably use replace to give the
intended effect, or if it helps me with creating a formula to look things up
automatically I can perhaps create a handy macro. For now, though, I would
be very happy with just being able to find the information more easily than
to manually identify each cell myself. I am also looking to eliminate the
human error that comes along with the manual identification.

Thanks in advance for any help or ideas you might provide!

.

I don't know if it's related, but I am using Excel 2003. It appears I could
perform what I want with 2007 but that's not an option available to me.

Your suggestion was definately what I had hoped for as a temporary solution
but I do want to find a way to accomplish the same thing with a keystroke or
automatically. For now using the CNTL +A does the job and helps me eliminate
the human error factor that seems to plague our output since there are so
many entries and changes. We had been doing it manually.

What I am actually trying to do is find cells containing values, a couple
dozen actually, and when found format the color of the cell background based
on the value within the cell. Say for simplicity I wanted to make every cell
with the value 100 turn red, then every cell with 150 turn blue, and so on.

As I mentioned I had not tried to use Find because I really wanted to use
the Replace function, which only allows me to do find on formula, not giving
the option of find based on value or comment. After reading your post I
tried Find and it does work and appears to identify all instances of my data.
It does not allow me to do anything with it, though, so I am still stuck
with my original problem.

I had originally asked this question of how to format cells based on value
but got no response or got responses that did not work and no reply to asking
for additional information if I did get responses.

I thought if I found at least how to find the data, perhaps in VBA, I could
use that to help me determine a formula or macro. I see lots of references
saying how to use conditional formatting for more than 4 conditions but none
really helped me with the problem I was trying to solve.

Thanks for the help!

"Gord Dibben" wrote:

First you couldn't find the data, now you do find the data.

I can find the data when "Lookin:" is formulas or values using 100 as an
example.

=Sheet1!A1 returns 100

Are you confident you have found all instances?

If missing some perhaps 100 is just the displayed formatted value.

If you add more decimal places is it still 100?

Do you just want to format the "Finds" to a color?

After you have found the cells, in the "Found" dialog hit CTRL + a which
selects all the "founds"

Now click on color picker to color the cells.


Gord

On Mon, 26 Apr 2010 10:25:01 -0700, joemc911
wrote:

Hi Gord. I have checked to make sure I was looking in Formulas but here's a
new twist I hadn't discovered until this prompted me to try something else.

I used Find and was able to locate every cell I wanted by looking in
"Values" but find and replace does not allow me that option, only look in
"formulas". This tells me I definitely need to find some way to identify the
value search in a macro or formula that will then allow me to change the
contents of the cell, actually the cell properties such as color, when I can
find the data value I search for.

Any more ideas that might help?

Thanks for the response!

"Gord Dibben" wrote:

When using EditFind make sure you have "Lookin:" option set to Formulas.


Gord Dibben MS Excel MVP

On Mon, 26 Apr 2010 08:05:01 -0700, joemc911
wrote:

This should be simple, at least I would think so, but I can't find a way to
do it. I have a workbook that imports data from another workbook, then
populates another sheet with which I produce my output. The output sheet
shows all the appropriate date but the cell reference is simply the link to
another worksheet.

For example: My sheet shows the number 100 but in the cell it reads
=otherworkseet!a1 (the source from which the data is extracted.

I want to find out how I can use find or a formula to show me every cell
that has the 100 in it without converting it to raw data. Simply using Find
does not locate any data. The result may be identical to other cells but it
comes from seperate locations. I will have a few dozen types of data I will
be trying to find in this manner and probably use replace to give the
intended effect, or if it helps me with creating a formula to look things up
automatically I can perhaps create a handy macro. For now, though, I would
be very happy with just being able to find the information more easily than
to manually identify each cell myself. I am also looking to eliminate the
human error that comes along with the manual identification.

Thanks in advance for any help or ideas you might provide!

.


.

After reading your post I
tried Find and it does work and appears to identify all instances of my data.
It does not allow me to do anything with it,

I thought you wanted to color the found cells. I gave a method to do that
but only for one "find" parameter.

With 24 values you want something faster.

What else do you want to do with the found cells?

Sample code to color by value.

Sub colorit()
Dim r As Range
Dim rr As Range
Set r = ActiveSheet.UsedRange
vals = Array(100, 200, 300, 400, 500, 150, 170, 1000, 250, 450) 'values
nums = Array(8, 9, 6, 3, 7, 4, 20, 10, 8, 15) 'colorindex numbers
For Each rr In r
icolor = 0
For i = LBound(vals) To UBound(vals)
If rr.Value = vals(i) Then
icolor = nums(i)
End If
Next
If icolor 0 Then
rr.Interior.ColorIndex = icolor
End If
Next
End Sub


Gord

On Mon, 26 Apr 2010 12:43:01 -0700, joemc911
wrote:

I don't know if it's related, but I am using Excel 2003. It appears I could
perform what I want with 2007 but that's not an option available to me.

Your suggestion was definately what I had hoped for as a temporary solution
but I do want to find a way to accomplish the same thing with a keystroke or
automatically. For now using the CNTL +A does the job and helps me eliminate
the human error factor that seems to plague our output since there are so
many entries and changes. We had been doing it manually.

What I am actually trying to do is find cells containing values, a couple
dozen actually, and when found format the color of the cell background based
on the value within the cell. Say for simplicity I wanted to make every cell
with the value 100 turn red, then every cell with 150 turn blue, and so on.

As I mentioned I had not tried to use Find because I really wanted to use
the Replace function, which only allows me to do find on formula, not giving
the option of find based on value or comment. After reading your post I
tried Find and it does work and appears to identify all instances of my data.
It does not allow me to do anything with it, though, so I am still stuck
with my original problem.

I had originally asked this question of how to format cells based on value
but got no response or got responses that did not work and no reply to asking
for additional information if I did get responses.

I thought if I found at least how to find the data, perhaps in VBA, I could
use that to help me determine a formula or macro. I see lots of references
saying how to use conditional formatting for more than 4 conditions but none
really helped me with the problem I was trying to solve.

Thanks for the help!

"Gord Dibben" wrote:

First you couldn't find the data, now you do find the data.

I can find the data when "Lookin:" is formulas or values using 100 as an
example.

=Sheet1!A1 returns 100

Are you confident you have found all instances?

If missing some perhaps 100 is just the displayed formatted value.

If you add more decimal places is it still 100?

Do you just want to format the "Finds" to a color?

After you have found the cells, in the "Found" dialog hit CTRL + a which
selects all the "founds"

Now click on color picker to color the cells.


Gord

On Mon, 26 Apr 2010 10:25:01 -0700, joemc911
wrote:

Hi Gord. I have checked to make sure I was looking in Formulas but here's a
new twist I hadn't discovered until this prompted me to try something else.

I used Find and was able to locate every cell I wanted by looking in
"Values" but find and replace does not allow me that option, only look in
"formulas". This tells me I definitely need to find some way to identify the
value search in a macro or formula that will then allow me to change the
contents of the cell, actually the cell properties such as color, when I can
find the data value I search for.

Any more ideas that might help?

Thanks for the response!

"Gord Dibben" wrote:

When using EditFind make sure you have "Lookin:" option set to Formulas.


Gord Dibben MS Excel MVP

On Mon, 26 Apr 2010 08:05:01 -0700, joemc911
wrote:

This should be simple, at least I would think so, but I can't find a way to
do it. I have a workbook that imports data from another workbook, then
populates another sheet with which I produce my output. The output sheet
shows all the appropriate date but the cell reference is simply the link to
another worksheet.

For example: My sheet shows the number 100 but in the cell it reads
=otherworkseet!a1 (the source from which the data is extracted.

I want to find out how I can use find or a formula to show me every cell
that has the 100 in it without converting it to raw data. Simply using Find
does not locate any data. The result may be identical to other cells but it
comes from seperate locations. I will have a few dozen types of data I will
be trying to find in this manner and probably use replace to give the
intended effect, or if it helps me with creating a formula to look things up
automatically I can perhaps create a handy macro. For now, though, I would
be very happy with just being able to find the information more easily than
to manually identify each cell myself. I am also looking to eliminate the
human error that comes along with the manual identification.

Thanks in advance for any help or ideas you might provide!

.


.