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.
Hello,
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
else
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 (
) next to the answer.