Pulling from multiple sheets

Hi JustStartingFormula 

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:

1. Make sure the sheet name is written exactly as it appears in the cell Lookup_sheets!A2, including spaces and capitalization.
2. Verify that cell B4 exists in the referenced sheet. The #REF! error can occur if the cell you're referencing does not exist.
3. 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.