Referencing from sub-sheets

Anonymous
2025-04-30T07:31:50+00:00

Hi MS Community,

I am using the desktop app-version of Excel from MS 365 for Business.

I have a large spreadsheet with multiple underlying sheets. Names of the underlying sheets are put in the first collumn on a main sheet, and on this same sheet I would like to display data from certain cells in each underlying sheet without manually typing (or using the mouse) cell-references.

How do I use cell-values as a referencepoint in a function?

Example:

The first underlying sheets is named "loan 1". The name "loan 1" is put in cell A1 of the main page.. On the sub-sheet "loan 1" I have values in e.g. cell A22 I want to refence.

I know the function is "=loan1!A22" but instead of typing "loan1" in each function i would like to just reference cell A1 which has the value "loan 1"

Anybody?

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
Rich~M 20,370 Reputation points Volunteer Moderator
2025-04-30T16:41:10+00:00

To use the sheet names that are in Column A to retrieve data from A22 in those sheets, use this formula that I show in B1 and filled down.

=INDIRECT("'"&A1&"'!A22")

Image

NOTE: In this instance because the cell number is included as text in the concatenate structure the A22 reference will not change as the formula is filled down. If it needs to change there are other ways to express that cell reference that can make that adjustment, but it doesn't seem that is the objective here.

Was this answer helpful?

1 person found this answer helpful.
0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2025-04-30T07:50:03+00:00

    Maybe paste special, copy the cell to paste into a target cell on another sheet

    Was this answer helpful?

    0 comments