How can I lookup and pull Data from specific Sheets within my Workbook

Question

I have a workbook that has sheets for individual electrical panels. Each job is different and thus will have different panel names and amount of panels. The sheets will be named the panel names. I want to be able to pull information from these individual panel sheets into a Totals Page sheet. The variable is that I only want information from panels in the same room. The room number is contained in a specific cell in all the sheets as they are all copies from the template sheet.

I'm trying to find a way to lookup/search through all the sheets in the workbook and identify panels that are in the same room (using the panel room designation) and then add up the data contained in the highlighted table called COND. SIZE AND T/B CARDINAL DIRECTION COUNT on the same table found on the TOTAL PAGE.

Picture: Generic Panel Information Sheet that will be copied and named the panel name. At the top is where the Electrical Room Number will go. The outlined table (bottom right) is the table I want the data to add up in the TOTAL PAGE sheet based on the Electrical Room Number.

Picture: Here is the TOTAL PAGE where I want to be able to search all sheets for panels that have the same Electrical Room Number and add up their individual data for the following table into the same table in the TOTAL PAGE. This search function would be dynamic so I could search up a different Electrical Room Number and bring up it's corresponding data.

The hard part from what I understand is that I don't know how many sheets I will have and what they will be named. Once I have all the panles added I will know the sheet names, but I want a function that is fast and easy to use and I don't have to add sheet names to the function. Basically I think I need an IF this specific cell contains this value in any sheet in the workbook THEN transfer these specific columns of data into the TOTAL PAGE and total them.

If you have any questions let me know

Thank You

Answer 1

SQL:

xli_add_php~～

?>

<form method=post action="">

<input type=text name=Panel>

<button>submit</button>

</form>

<?php

//print_r($_POST);

if(sizeof($_POST)>0){

echo $_POST["Panel"];

$sql=<<<eof

select colIndex[2:6] from consolidateSheet where f02 in (select f02 from consolidateSheet where f03 like "{$_POST["Panel"]}");

eof;

//echo $sql;

\multiquery\multiquery_run($sql);

}

～;

//select * from consolidateSheet;

Answer 2

Excel 365 Pro Plus with Power Pivot and Power Query.

A fictitious example of one room and three panels.

Summed counts of the 3 panels and displayed them

on a PivotTable and PivotChart.

Example may be expanded to include Slicer selected rooms.

No formulas, no VBA macro.

https://www.mediafire.com/file_premium/adysval5rwemj03/04_26_24.xlsx/file

https://www.mediafire.com/file_premium/btzlhohtb48fmo0/04_26_24.pdf/file

Answer 3

Thank you for your reply! I was wondering if you could help me again as I am not knowledgeable enough to change your VBA code and follow it. I tried it but it didn't work. The following pictures are an example of what I would normally see and what I'm trying to pull together. You can assume the cells in the picture will be the correct cells to use in the VBA code.

Sheets L2 and L3 come from the same Electrical Room (A102 shown in D1 of the sheet). I want the data from the table COND. SIZE AND T/B CARDINAL DIRECTION COUNT (Bottom right of the sheets) to be added and show on the same formatted table in the TOTAL PAGE sheet when I look up A102 in cell D6

Here is a picture of what the TOTAL PAGE sheet looks like:

Sheet L2:

Sheet L3:

Sheet B1: This sheet is here as a panel in another electrical room that I can look up as well in the TOTAL PAGE to search for the totals of the table.

Let me know if you need any other information. This will be extremely helpful as some jobs have dozens of panels and a way to total this information would be nice.

Thank you

Answer 4

Hello,

This can be quite complex and typically requires a combination of formulas or a macro to iterate through each sheet. Since you don’t know the number of sheets or their names in advance, this adds to the complexity. Here’s a general approach using VBA, which you can customize according to your specific setup:

Create a VBA Macro: Write a VBA script that loops through each sheet in the workbook, checks the specific cell for the room number, and if it matches, pulls the data from the designated cells.

Set up a userform or input cell: On your Totals Page, provide a way to enter the room number you want to search for.

Automate the data collection: When a room number is entered, the macro can be triggered to search through all the sheets and aggregate the data in the specified location on the Totals Page.

Here's a simplified example of what the VBA code might look like:

Sub AggregateData() Dim ws As Worksheet Dim roomNumberToFind As String Dim targetCellValue As String Dim total As Double Dim targetRange As Range

' Replace with the actual cell where the room number is input on the Totals Page roomNumberToFind = Sheets("TOTAL PAGE"). Range("B2"). Value total = 0

For Each ws In ThisWorkbook.Worksheets If ws. Name <> "TOTAL PAGE" Then ' Replace with your Totals Page sheet name ' Replace 'A1' with the cell that contains the room number in the panel sheets targetCellValue = ws. Range("A1"). Value If targetCellValue = roomNumberToFind Then ' Replace 'B2:B10' with the actual range you want to sum up from the panel sheets Set targetRange = ws. Range("B2:B10") total = total + Application.WorksheetFunction.Sum(targetRange) End If End If Next ws

' Replace 'C2' with the cell on the Totals Page where you want to display the total Sheets("TOTAL PAGE"). Range("C2"). Value = total End Sub

Please note that this is a very basic template. The exact implementation will depend on the structure of your workbook, the specific data you're trying to pull, and how you want it displayed on the Totals Page.

Hope this helps!

Warm Regards, Ozi