List same cell from different sheets

I have over 30 sheets with invoices and I want to create a master sheet which lists data from the same cell but different sheets.
My master sheet is called ‘Tax’ and all the others are dated ‘2-3-20’ for instance. The first cell i want from all the sheets is ‘E5’
is there an easy way to do this with out having to do each individual one?
i have tried doing a code for a couple of sheets and dragging it, but it does’nt keep the values i want.

any help will be greatly appreicated.


please see the following sample file: SameCellDiffSheets.ods. Common to both methods is using function INDIRECT() to create the correct reference to the required cell. Just study the sample.

Method 1 - based on listing the sheet names in column A
(be aware, that list must be text and must not be confused with dates formatted like the sheets names. In that case you need to convert to text using something like TEXT(A3;"m-d-yy"), assuming that 2-3-20 should mean something like 3rd Feb 2020))

Method 2 - based on internal number of sheet in column D and a user defined function (macro) getting the name of the sheet for a specific index.

User defined function SHEETNAME() for method 2

REM ---------------------------------------------------------------------------
REM User-Function SHEETNAME()
REM ---------------------------------------------------------------------------

Function SHEETNAME(iShN as integer)
    dim ii   as integer
    dim iShC as integer
    iShC = ThisComponent.Sheets.Count
    if iShN <= iShC then
      REM --- Arrays start at 0 ---
      ii = iShN - 1
      SHEETNAME = ThisComponent.Sheets(ii).GetName 
      SHEETNAME = "#N/A"
    end if 
End Function

General Warning: The attached file contains the macro above. Thus you may get a warning and according to your macro security setting you may not be able to execute the macro / user defined function and you’ll see a #VALUE! error message in that case. Do only enable macros in files downloaded from the internet, if you have understood the impacts of a macro.

Hope that helps.

If the answer helped to solve your problem, please click the check mark (:heavy_check_mark:) next to the answer.