Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding data within a worksheet made up of links
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding data within a worksheet made up of links
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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding data within a worksheet made up of links
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! . |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding data within a worksheet made up of links
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! . |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding data within a worksheet made up of links
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! . . |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding data within a worksheet made up of links
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! . . |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding data within a worksheet made up of links
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" wrote: 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! . . . |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding data within a worksheet made up of links
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 EditFind............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 InsertModule. 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 ToolMacroMacros. You can also assign this macro to a button or a shortcut key combo. Gord On Tue, 27 Apr 2010 11:18:01 -0700, joemc911 wrote: 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" wrote: 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! . . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding Data in a worksheet | Excel Discussion (Misc queries) | |||
Finding replicate data accidentally entered into a worksheet | Excel Discussion (Misc queries) | |||
Last data entry made time and Date show in each worksheet | Excel Worksheet Functions | |||
Finding cells with links to another worksheet in excell | Excel Discussion (Misc queries) | |||
Finding duplicate data in a worksheet | Excel Discussion (Misc queries) |