searching multiple worksheets

Microsoft Excel 2003 (full product)
January 6, 2010 at 13:22:51
Specs: Windows XP
I have multiple sheets (over 80) and have a summary worksheet in the beginning. Is there any way to look for all negative numbers in the same array in each page and report them in the summary worksheet with their corresponding hyperlinks?

See More: searching multiple worksheets

Report •

January 6, 2010 at 14:43:20

When you say look ... in the same array in each page does this mean the same range of cells in each of the 80+ worksheets.
Also are you just looking in one range on each worksheet or multiple ranges on each worksheet.

You refer to hyperlinks. Does this mean that the cells have hyperlinks to other locations such as internet addresses or file locations and you want the same hyperlinks to appear on the summary worksheet.

If you have 80+ worksheets how will the negative values be arranged on the master worksheet. If there are negative values on Sheet2 where will they be shown on the master worksheet, and where will negative values from Sheet3 go etc.

Can you give some examples of what you want to happen on the master worksheet.

The more information you give, the more chance of someone being able to offer a solution.


Report •

January 7, 2010 at 06:56:04
I used the same template to create 80 worksheets. There is a range of cells within each worksheet that describes the hours bid on a project and the total hours the project has taken. I would like to be able to use the first worksheet in the document as a summary page. I would like to do the following, or something like this.

Have cells in the summary sheet that search the whole document (each worksheet) for any worksheet where the hours worked on the project is a week away from being completed, ie, the total hrs are > or = to the bid - 40hrs. I would like for any sheet that fits this criteria to become a hyperlink on the summary sheet so that I can go to it quickly.

Report •

January 7, 2010 at 09:05:13

For this example, I created a Workbook with 5 worksheets:
Summary and Proj1 to Proj4
Each Proj sheet contained:

	A		B
1	Project Name	Hours Remaining
2	Project X	XX

The hours I entered in cell B2 on each sheet were:
Proj1	10
Proj2	-20
Proj3	-40
Proj4	-60

and in cell A2 on each sheet I had a project name Project A to Project D

In the Summary sheet enter text:

cell A1	Projects with hours <=-40
cell B1	Hours Remaining

On the Summary sheet create a command button from the Control Toolbox toolbar. If this isn't visible, right click on an existing toolbar and check the Control Toolbox.
Select the button Icon and draw a button
Right-click the button and select Command Button - Edit and change the name to Update
Right-click the button again and select View Code
In the code window that opens enter this:

Option Explicit

Private Sub CommandButton1_Click()
Dim wsProj As Worksheet
Dim strMaster As String
Dim intRows As Integer

'get name of initiating worksheet
strMaster = ActiveSheet.Name
'set row counter for next row to display project data on Summary sheet
intRows = 0

For Each wsProj In ActiveWorkbook.Worksheets()
    'test each worksheet name - if its not the Summary sheet - test hours
    If wsProj.Name <> strMaster Then
        'only copy etc. if project hours meet requirement
        If wsProj.Range("B2").Value <= -40 Then
            'copy project name and hours
            Worksheets(strMaster).Range("A2").Offset(intRows, 0) = wsProj.Range("A2")
            Worksheets(strMaster).Range("A2").Offset(intRows, 1) = wsProj.Range("B2")
            'create hyperlink - a two stage process
            Worksheets(strMaster).Hyperlinks.Add _
                Worksheets(strMaster).Range("A2").Offset(intRows, 0), ""
            Worksheets(strMaster).Range("A2").Offset(intRows, 0).Hyperlinks(1).SubAddress _
                = wsProj.Name & "!" & wsProj.Range("A1").Address
            intRows = intRows + 1
        End If
    End If
Next wsProj
End Sub
Note that Private Sub CommandButton1_Click() and End sub will already be present, so don't duplicate them. Option explicit goes before Private Sub CommandButton1_Click().
The two lines for creating the hyperlink have each been split onto two lines for ease of viewing, using the line continuation character "_". This should work 'as is' just copy and paste, or you could remove the "_" and bring the code back to one line.

Alt+f11 takes you back to the main Excel window.
Exit design mode (first icon on the Controls Toolbox toolbar).

Click the Update button and this was the result:

	A				B
1	Projects with hours <=-40	Hours Remaining
2	Project C			-40
3	Project D			-60

Cells A2 and A3 contain hyperlinks, and clicking the hyperlink takes you to Cell A1 on the relevant project page.

Once you get this example working, make a copy of your workbook, and test this on the copy: create the Update button on your copy Workbook Summary page.
Copy and paste in the code as before, and edit the code to match the cell with your Project name and the cell with the hours to be tested. [wsProj.Range("A2") & wsProj.Range("B2")]

You may need to change the logic in this line:

 If wsProj.Range("B2").Value <= -40 Then
to match your requirements correctly.


Report •

Related Solutions

January 8, 2010 at 05:20:04

I don't know if you tried the macro I offered, but if you did, please add this after intRows=0.
It will clear existing results before updating.

'clear any existing data in the summary sheet
Worksheets(strMaster).Range("A2:B" & _


Report •

January 11, 2010 at 14:32:32
Thank you so much!! I did get it to work, but my Update button isn't letting me update if I make changes to the worksheets. Am I doing something wrong?

Report •

January 12, 2010 at 04:57:14

I presume the Update is not working on your real data, not the example I gave. If so can you send me the code you are using. Paste it between

 tags which you can insert from the icon above the reply box.

From the sheet with the Update button, use the control toolbox toolbar to enter design mode. Right-click the button and View code.

In the code window click in the left margin of the code click next to the line

If wsProj.Range("B2").Value <= -40 Then
This will put a 'dot' in the margin and highlight the line of code.

Alt + f11 to go back to the Excel main window, exit design mode and click the Update button.

Does the Code window open with the line of code highlighted in Yellow.

If so click the f8 button
Does the yellow highlight move to the next line.

Now stop the code - from the menu - Run - Reset.
Click on the 'dot' in the margin and this will remove it.

After this line

If wsProj.Name <> strMaster Then
add a new line:

Now run click the Update button again.
You should get a series of dialog boxes opening showing the names of the worksheets. Do all worksheet names appear or not.

Let me know what happens.


Report •

January 12, 2010 at 12:45:49
I figured most everything out. Only the first hyperlink on my summary sheet works like a hyperlink however, the res bring up the titles, but when i click on them I get an error that the Reference is not valid.

Everything else is great! Thank you so much!

Report •

January 12, 2010 at 14:30:13

Using single stepping through the macro may help identify the problem. Set breakpoints by clicking in the left margin of the code and the macro will stop there. Then single step with f8, or hit f5 to continue running.

You can see the value of an expression whilst the macro is running by using the 'watch' function.

In the code window highlight the hyperlink reference part of the code

wsProj.Name & "!" & wsProj.Range("A1").Address
then right click and select Add watch... Click OK in the dialog box that opens, and as the macro runs, the value of the expression is shown.

It may give you a clue as to what is going wrong after the first cell.


Report •

Ask Question