basically if I want the content of the cell A1 on the second sheet named “big_cats” I can just use =(big_cats.A1). but what do I do if I wanted the cell to display whatever is on the second sheet without using the sheet name.
let say that I have added a third sheet and named it “fluffy_cats”. is there a way for me to detect the content of the third sheet the moment it gets added.
for example I can make a column name “sheet names”
row1:(first_sheet.A1)
row2:(second_sheet.A1)
row3:(third_sheet.A1)
…
row10(tenth_sheet.A1)
and while sheet number 10 does not exists it will show empty cell the moment that sheet is added the row will display the content of cell A1 from that sheet.
You can’t (at least just typing) use a reference to an unexisting sheet (you get #REF!).
See related questions:
is there at least a way for me to say to call a sheet by it number instead of it name?
I use the following macro quite a bit. The array-awareness isn’t well tested. So, with this macro in your library you could just do something like:
=INDIRECT(SHEETNAME(2)&"."&"A1")
Experience has taught me to concatenate the “.” separately. 1) So it’s habit and I don’t forget it and 2) so I can make it a Named Range reference to convert to Excel, using “!”.
Also note that here sheet 2 is in fact the second sheet in the current sheet order, it is not like the Sheet1, Sheet2 internal names for sheets in Excel.
I use this mostly to address the previous sheet.
=INDIRECT(SHEETNAME(Sheet()-1)&"."&"A1")
This way I can build a single report sheet that I just slide to the right of any of several identically formatted sheets (e.g. one for each month), and the report populates according to that sheet.
Function SheetName(Optional sheetIndex As Variant)
Dim Result as Variant
Dim Row as Integer
Dim Column
Dim Index as Variant
If IsMissing(sheetIndex) Then
SheetName = "#" & CVErr(502)
Exit Function
ElseIf IsEmpty(sheetIndex) Then
SheetName = "#" & CVErr(502)
Exit Function
ElseIf IsNull(sheetIndex) Then
SheetName = "#" & CVErr(502)
Exit Function
End If
If IsArray(sheetIndex) Then
ReDim Result(LBound(sheetIndex) to UBound(sheetIndex), LBound(sheetIndex,2) to UBound(sheetIndex,2))
For Row = LBound(sheetIndex) to UBound(sheetIndex)
For Column = Lbound(sheetIndex,2) to UBound(sheetIndex,2)
REM Specifically, empty entries in sheetIndex() will be "", the empty string
If IsNumeric(sheetIndex(Row, Column)) Then
Index = sheetIndex(Row, Column) - 1
If (Index >= 0) And (Index < ThisComponent.Sheets.Count) Then
Result(Row, Column) = ThisComponent.Sheets(Index).getName
Else
Result(Row, Column) = CVErr(502)
End If
End If
Next Column
Next Row
SheetName = Result
Else
If IsNumeric(sheetIndex) Then
Index = sheetIndex - 1
If (Index >= 0) And (Index < ThisComponent.Sheets.Count) Then
SheetName = ThisComponent.Sheets(Index).getName
Else
SheetName = "#" & CVErr(502)
End If
Else
SheetName = "#" & CVErr(502)
End If
End If
End Function