0

I am working on a project that is pulling a bunch of spilled data across a ton of sheets that are all in the same spot as the sheets are based off of a template.

Using the name manager I was able to create a function to evaluate an automatically forming text join to create a VSTACK formula with the spilled data from all of the sheets.

When copy/pasting the text from this text join, and calculating it normally, it has everything working, which means it’s not a problem with the calculation. But having this many different variables is causing the =Eval(my name manager formula for =EVALUATE(x)) to not work. After investigation I found that Name Manager formula's have a character limit of around 250, so I had created a UDF Formula using this code.

Public Function eval(ByVal str As String) As Variant
  eval = Application.Evaluate(str)
End Function

Although UDFs are supposed to calculate up to around 8000 characters, it still couldn t calculate my text join(around 4000 characters). Is there a way to make this work or have a version of evaluate that can process more? VBA solutions are absolutely on the table, let me know.

example of the text that is trying to be evaluated:

=VSTACK(Sheet1!A1#:Sheet1!G1#, Sheet2!A1#:Sheet2!G1#, Sheet3!A1#:Sheet3!G1#,etc…)

Also I have done a good amount of experimenting but for some reason excel wont let me do something like =VSTACK(Sheet95:Sheet100!A1#:G1#). Any feedback on why would be great as this would be a far simpler solution to the issues I'm facing.

Improve this question

1 Answer 1

Reset to default
2

You can try this way:

=REDUCE(EXPAND("",1,7,""),{"Sheet1";"Sheet2";"Sheet3"},LAMBDA(a,b,VSTACK(a,INDIRECT(b&"!A1#"):INDIRECT(b&"!G1#"))))

List of sheets can be prepared separately and passed as a name.
You can also make a list in a sheet and refer to it as a range of cells e.g. E5:E7 or E5:G5, or E5# if it is a dynamic range. You can also call the UDF function, which returns a list of sheet names MyList().
Initial row is now empty, but you can put some headers in it.
3D notation is not accepted here, because it would have to be something like this:

=VSTACK((Sheet95:Sheet100!A1#):(Sheet95:Sheet100!G1#))

and such operations on 3D ranges are impossible.

Improve this answer
4
  • How would I go about making this dynamic, the list of sheets will be changing as new sheets are added and removed. I have a formula to list the sheets, and I can use text joins to format them as required but I am unable to evaluate(with both name manager evaluate and UDF evaluate) a text joined version of the formula. I currently have 3 cells being combined: _ REDUCE(EXPAND("",1,7,""),{ _ "Sheet1";"Sheet2";"Sheet3";"Sheet4" _ },LAMBDA(a,b,VSTACK(a,INDIRECT(b&"!A1#"):INDIRECT(b&"!G1#")))) _ The sheets are dynamic based on the actual sheet lists, but the full formula wont get evaluated.
    – Thomas Wohllaib
    Commented Jan 15 at 1:19
  • 1
    You need a list with the names of the sheets. You can generate this list dynamically, and you can give it a name and use that name in the formula. You can create a list using the UDF function. How to do it in detail depends on whether it will be all the sheets in the workbook or selected ones, I don't know.
    – MGonet
    Commented Jan 15 at 1:34
  • I have created the dynamic array with the sheets that I want to be included, how would I integrate it into the formula you had provided? The list I am using was created with the name manager and spills the names, or should I create a text joined named list instead of a spilled named list?
    – Thomas Wohllaib
    Commented Jan 15 at 3:08
  • 1
    It should be a list, not a text joining names. I completed my answer.
    – MGonet
    Commented Jan 15 at 9:14

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .