How do I Import Data from Various Tabs onto One Sheet?

[SingleTXGuy4Fun]

I am building a shift tracker where there is the main metrics dashboard on the first sheet/tab and locations/percentages featured in the succeeding sheets which are labeled as dates (ex. Oct 3rd).

The main metrics dashboard has these locations and percentages posted in the form of mini-dashboards in succession by date. Each dated mini-dashboard on the main sheet has a formula similar to this one under categories and numbers it has listed ='15 Oct'!A1, ='15 Oct'!B1, etc.

How do important the data from the succeeding sheets to get this?

I tried using =date on tab command V+Command B, but have been unsuccessful in importing the correct information from the dated sheets onto the main dashboard.

I have Microsoft 365 2020 for Mac OS if that's any help.

Thank you in advance for your help!

[hrlngrv]

You could use the volatile INDIRECT function to refer to arbitrary worksheets, e.g., with a given date in cell C5, =INDIRECT(TEXT($C$5,"'dd mmm'!")&CELL("Address",A1)) would refer to the A1 cell in the worksheet given by C5. However, if you have A LOT of formulas calling INDIRECT, you can really slow down Excel's recalculation.

If there are worksheets for each day of the year, and if they're all contiguous with '01 Jan' leftmost and '31 Dec' rightmost, you could use a defined name and a non-volatile user-defined function to access any of those worksheets. The defined name would be _ALL_ referring to the formula =COUNTA('01 Jan:31 Dec'!$1:$1048576). The user-defined function would be

Please Login or Register  to view this content.

Call this as =ref3D(DATE(2020,10,15),A1,_ALL_) in cell formulas.

Formulas calling ref3d with _ALL_ as 3rd argument become dependent on all cells in worksheets '01 Jan' through '31 Dec', so any changes in those worksheets would be reflected in formulas calling ref3D this way when Calculation is set to Automatic, but changes in other worksheets would leave these formulas unaffected. Note that the 3rd argument to ref3D isn't used in the body of the function. That's intentional. It's presence as 3rd argument when called from cell formulas is all that's needed to make those formulas depend on all cells in worksheets '01 Jan' through '31 Dec'.