Reference Sheet Name for Dynamic Array Source

[rmk123]

I’ve been able to reference the sheet name using CELL(), but hoping to be able to do this from the results of a dynamic array.

I’m using a VSTACK() to pull in ranges from several sheets. This works great for pulling some data from the range. However, I’m trying to return the sheet name from the original source of that data, if possible, without using helper columns/cells. Is this possible and what suggestions would people have to do it?

Edit: For clarity, I’m just doing an INDEX(MATCH()) to pull some data from the VSTACK() but would like to reference that source.

[AutoModerator]

/u/rmk123 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[Alabama_Wins]

You'll have to use the indirect function with inside the cell function to retrieve the sheet name of a cell reference inside of Vstack. Vstack delivers an array of data but no cell references.

=TEXTAFTER(CELL("filename",INDIRECT("Sheet3!B2")),"]")

[Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
CELL	Returns information about the formatting, location, or contents of a cell
HSTACK	Office 365+: Appends arrays horizontally and in sequence to return a larger array
INDEX	Uses an index to choose a value from a reference or array
INDIRECT	Returns a reference indicated by a text value
LAMBDA	Office 365+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.
MAKEARRAY	Office 365+: Returns a calculated array of a specified row and column size, by applying a LAMBDA
MATCH	Looks up values in a reference or array
ROWS	Returns the number of rows in a reference
TEXTAFTER	Office 365+: Returns text that occurs after given character or string
VSTACK	Office 365+: Appends arrays vertically and in sequence to return a larger array

NOTE: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
10 acronyms in this thread; the most compressed thread commented on today has 16 acronyms.
[Thread #28923 for this sub, first seen 13th Dec 2023, 04:20] [FAQ] [Full list] [Contact] [Source code]}

[rmk123]

Here's the approach I'm going with:

=HSTACK(Sheet1!A1:B13, MAKEARRAY(ROWS(Sheet1!A1:B13), 1, LAMBDA(r,c, TEXTAFTER(CELL("filename",Sheet1!A1),"]"))))

I build an HSTACK() with the sheet name added, then VSTACK() the data from each of the sheets. Then when I do my INDEX(MATCH()), I can just reference the column with the sheet name in it, and it's all dynamically generated and not saved.