Forum Discussion
Pulling from multiple sheets
I'm not sure if I understood your need. But I'll try to describe a solution based on what I understood (forgive me if it's not correct).
Assuming in the Lookup_sheets worksheet, you have a list of sheet names in cells A1 (Meeting 1, Meeting 2, etc.).
In the Summary worksheet, you can use the INDIRECT function to pull the start date from B4 in each meeting sheet.
In cell B2 of the Summary worksheet (where you want to pull the date from "Meeting 1"), use the following formula:
=INDIRECT("'" & Lookup_sheets!A1 & "'!B4")
- Lookup_sheets!A1: refers to the cell in the Lookup_sheets sheet that contains the meeting sheet name (e.g., "Meeting 1").
- '!B4': indicates that you want to pull the value from cell B4 of the corresponding sheet.
Copy this formula to the other cells in column B to fetch the start date from all the meeting sheets listed.
In this way, whenever you add a new meeting sheet to Lookup_sheets and create the respective sheet with the start date in B4, the Summary sheet will automatically update.
- can you explain what the ("'" in the begining does so I can understand? As of now I get a #ref error using that formula.
The INDIRECT function in Excel converts a text string that represents a cell reference into an actual reference. In your case, we want to dynamically construct a reference to cell B4 in different sheets, whose names are listed in the "Lookup_sheets" tab.
If the sheet name contains spaces or special characters (like "Meeting 1"), you need to enclose the name in single quotes (') for Excel to recognize it correctly.For example:
- No spaces: If the sheet is named "Meeting1", you can reference it like this: =Meeting1!B4.
With spaces: If the sheet is named "Meeting 1", you need to add single quotes: ='Meeting 1'!B4.
INDIRECT("'"&Lookup_sheets!A2&"'!B4")
"'" adds the first single quote before the sheet name.
Lookup_sheets!A2 refers to the sheet name (e.g., "Meeting 1").
"'" adds the second single quote after the sheet name.
"'!B4" concatenates the part that refers to the cell you want to access (!B4).Example:
If the cell Lookup_sheets!A2 contains Meeting 1, the formula will internally convert to:=INDIRECT("'Meeting 1'!B4")
This tells Excel: “Go to the 'Meeting 1' sheet and get the value in cell B4.”
If you're getting the #REF! error, here are a few checks that might help:- Make sure the sheet name is written exactly as it appears in the cell Lookup_sheets!A2, including spaces and capitalization.
- Verify that cell B4 exists in the referenced sheet. The #REF! error can occur if the cell you're referencing does not exist.
- Check the single quotes. If the sheet name contains spaces, the quotes are essential. Without them, the formula will return an error.
If everything looks correct and the error persists, it may be worth reviewing the formula structure or the data in your sheets.
- No spaces: If the sheet is named "Meeting1", you can reference it like this: =Meeting1!B4.
