I have this
=VLOOKUP(G$2,Sheet2.A$1:B$25,2)
How can I increase the Sheet index when dragging down?
Thanks
Hello
to the best of my knowledge only through use of INDIRECT(): Assuming your cell’s formula starts in some cell in row 2 (this is important the get Sheet2 as the first referenced sheet) then you may use:
=VLOOKUP(G$2;INDIRECT("Sheet" & ROW() &".A$1:B$25");2)
(This could be dragged down, since function ROW() adapts the string used for INDIRECT())
If starting in some column in row 3: use INDIRECT("Sheet" & ROW()-1 &".A$1:B$25") in formula above
If starting in some column in row 4: use INDIRECT("Sheet" & ROW()-2 &".A$1:B$25") in formula above
If starting in some column in row 5: use INDIRECT("Sheet" & ROW()-3 &".A$1:B$25") in formula above
… and so on
Hope that helps.
If the answer helped to solve your problem, please click the check mark (
) next to the answer.
Be careful. Run alien “macros” only after checking them for the absence of malign functionality. Use Basic functions within reason. Scaling up your sheets you may otherwise get inacceptable recalculation times. …
Calc neither has a standard function returning the name of a sheet given its index, nor any means to reference sheets or ranges of sheets based on a sheet index. Since syntactically dirty sheet names were allowed long ago, there also are problems due to the need of escaping (“quoting”) such names with the help of single apostrophes. It’s a mess.
As related questions are asked again and again, and the workarounds needed if only standard functions are used are definitely annoying, I took the opportunity to finally write a bit of user code providing a function similar to OFFSET, but in addition allowing for an offset in sheets. Take it “as is”! Tests and checks were superficial, and there is no guarantee of any kind!
I personally don’t intend to use it much. Splitting data over many sheets is and remains a bad practice.
Functions written in Basic are generally rather inefficient.
The compact access to data in cell ranges uses an array format that not is accepted for the output of a function.
The function mostly will need to be used volatile, because Calc cannot determine the dependencies automatically.
(This also is a fact concerning the standard functions OFFSET and INDIRECT being implemented internally in these cases, while a user function needs to provide a trigger parameter for the purpose.)
Please note: To allow for a simple way to pass the starting reference to the function “enhancedOffsetData” it relies on well working Option VBAsupport 1 and may therefore only run in recent versions of LibreOffice. (It surely cannot run in any AOO.)
This attached spreadsheet doucument is containing the mentioned function and a helper to it. It also contains an old function from my box simply returning a sheet’s name for its passed index.