How to refer from multiple sheets

Question

How to refer from multiple sheets

Red Berry 20

I planned to make a summary based on time (using date as the name of the sheet) in Ms. Excel. lets say I have Sheet1 (01-01-2024) and Sheet2 (02-1-2024). Function input will be {='01-01-2024'!$A$1} and {='02-01-2024'$A$1}. The problem is the date will always be updated which makes more work just by inputting the sheet name manually. How can I refer to these data from multiple sheets with just dragging? Thank you! ~Red Berry

Barry Schwarz 4,871 Reputation points

2024-01-17T08:46:03.1033333+00:00
Are we to assume the summary is in a different sheet? Are you summarizing the data from a single sheet or will your summary combine the data from multiple sheets?

Is the function really going to process just a single cell in each data sheet?

You are asking how to tell the function which input sheets to process? Some possible methods of passing that info are:

Populate the summary sheet with the dates (and thus the data sheet names) of interest. The function can then extract them as needed.

Tell the function a specific date and the number of days before or after to work with so it can generate the sheet names.

Call the function multiple times and give it a single date each time?

If you give the macro a short cut key, you can select the data sheet to process and presss the short cut key. The active sheet method will let you know which data sheet to process.

If you give us some indication of what you are doing, we might even come up with a reasonable suggestion.

Answer accepted by question author

0 additional answers

Your answer

Barry Schwarz 4,871 Reputation points

2024-01-17T08:46:03.1033333+00:00

Are we to assume the summary is in a different sheet? Are you summarizing the data from a single sheet or will your summary combine the data from multiple sheets?

Is the function really going to process just a single cell in each data sheet?

You are asking how to tell the function which input sheets to process? Some possible methods of passing that info are:

Populate the summary sheet with the dates (and thus the data sheet names) of interest. The function can then extract them as needed.

Tell the function a specific date and the number of days before or after to work with so it can generate the sheet names.

Call the function multiple times and give it a single date each time?

If you give the macro a short cut key, you can select the data sheet to process and presss the short cut key. The active sheet method will let you know which data sheet to process.

If you give us some indication of what you are doing, we might even come up with a reasonable suggestion.

Answer 1

Emi Zhang-MSFT 30,126 Microsoft External Staff

Hi,

I created a sample abut your requirement, you can use the INDRECT function and try this formula:

=INDIRECT("'Sheet"&ROW(A1)&" "& "("&TEXT(ROW(A1),"00")&"-01-2024)'!A1")

User's image

Just checking in to see if the information was helpful. Please let us know if you would like further assistance.

If the response is helpful, please click "Accept Answer" and upvote it. Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Red Berry 20 Reputation points

2024-01-18T07:27:31.8833333+00:00

Hey thanks a lot, buddy, that actually helped my problem here is my final function

=INDIRECT("'"&TEXT((A2);"DD-MM-YYYY")&"'!$A$1")

I can easily drag the data and it's filled based on the Date which has the same name with the sheet reference. Thanks! ~Red Berry

Share via

How to refer from multiple sheets

0 additional answers

Your answer