Finding data within a worksheet made up of links

[joemc911]

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!

 

[Gord Dibben]

When using Edit>Find make sure you have "Lookin:" option set to Formulas.


Gord Dibben MS Excel MVP

 

[joemc911]

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]

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

 

[joemc911]

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]

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

 

[joemc911]

First of all thanks so much for following up on each response! I appreciate
your time you have spent to help me!

Yes, I do want to color found cells. I am able to use the method you gave
me. The Control +A was something I wasn't aware of being available. This is
a great short term alternate method to manually selecting each cell. I don't
need any other effect in the cell.

You are correct, as well, that I would like to make this faster and honestly
I would like it to be automatic or via a keystroke command. What I am
producing is a usage schedule. The 24 or so values remain the same but occur
in different cells each week. I have a reference sheet that tells me the
color ID numbers so I can use that to make sure I get the colors I want per
item.

I am sure it is obvious from some of the trouble I have had I am not as
fluent in the programming area as I would like so please forgive my lack of
understanding. What I am getting from your sample code tells me to set up
something similar to a lookup command but with the ability to apply it to the
whole sheet. Each vals array corresponds to the color I have chosen and
identified in the nums array for that value. I'm not sure what each function
or identifier means.

What is Dim?

vals = (comma seperated list of all my possible data)

nums =(corresponding numerical color identifier from Excels color choices)

Do I just open VBA and type all this in there? (Cut, paste and edit more
accurately)

If this is too confusing or takes too much time I understand and appreciate
all you have already done. I intend to get a book on Excel and learn more so
I am not so poorly versed in things I could do within Excel if I only knew
how.

Thanks again!

Joe M

 

[Gord Dibben]

It is worksheet event code.

Use the Dim statement at module or procedure level to declare the data type
of a variable

The vals are the values to color

The nums are the colorindex numbers.

Looking at all this in new light of today, I would not use event code, just
a plain macro you could run to color the cells.

No need for Edit>Find............the code will do that for you.

Example........................

With a list of values in Sheet2 A1:A24 and a list of ColorIndex numbers in
Sheet2 B1:B24. If not Sheet2 then edit the code accordingly.

Sub color_cells()
Dim Vals As Range
Dim R As Range
Dim RR As Range
Set R = ActiveSheet.UsedRange
If ActiveSheet.Name = "Sheet2" Then GoTo oops
Set Vals = Sheets("Sheet2").Range("A1:B24")
On Error Resume Next
R.Interior.ColorIndex = xlNone 'clears existing color
For Each RR In R
RR.Interior.ColorIndex = Application.VLookup(RR.Value, _
Vals, 2, False)
Next RR
Exit Sub
oops:
MsgBox "Do not run macro on this sheet"
End Sub

If you're not familiar with VBA and macros, see David McRitchie's site for
more on "getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or Ron de De Bruin's site on where to store macros.

http://www.rondebruin.nl/code.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run or edit the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.


Gord