Hey Guys
i have the attached Excel that uses many many Array formulas, which is making the document incredibly slow.
do you guys see any other way to make the attached ? maybe with VBA or something?
regards
Hey Guys
i have the attached Excel that uses many many Array formulas, which is making the document incredibly slow.
do you guys see any other way to make the attached ? maybe with VBA or something?
regards
Hi,
The only reason it's so slow is that you're referencing entire columns within those array formulas, e.g.
=IFERROR(TEXTJOIN("; ",,IF(P16=Invoice!$B:$B,Invoice!$A:$A,"")),"")
As such, just one instance of those TEXTJOIN formulas is being forced to calculate over more than 2 million cells, an astonishing number for a single formula. What's more, the Invoice sheet only contains 3 entries!
Choose a suitably low, though sufficient, end row reference instead, e.g. A2:A100. Or else used dynamically-defined Named Ranges.
Regards
Last edited by XOR LX; 01-29-2019 at 01:37 AM.
1 million is still 100 times more than 10,000.
If 15,000 is a sufficient upper limit, use that. Better still, use dynamically-defined ranges which automatically detect the last-used rows in the relevant columns and reference those in the formulas instead.
Regards
I prefer to use non-volatile INDEX set-ups, myself. It is unfortunate that so many Excel sites promote the use of the volatile OFFSET in such cases.
I'm more concerned here about your intentions in this workbook. I take it you realise that TEXTJOIN is designed to concatenate text into a single cell? So potentially you'd want to have up to 10,000 entries put into a single cell? This doesn't seem like a very good idea. What's more, you should know that that function has a limit of 32,767 characters: any more than that and it will error.
Regards
A typical set-up for the Invoice sheet would be to define, in Name Manager:
Name: Invoice_LRow
Refers to: =MATCH(REPT("z",255),Invoice!$C:$C)
which automatically detects the last-used row in that worksheet, in this case using column C (Company) as a basis.
Note that the choice of formula in the Refers to field is dependent on the datatype of the column chosen to determine the last-used row. I used REPT("z",255) here since the column C entries are text; if they had been numerics, I would have chosen 88^88 instead. Slightly different constructions are required if the column chosen contains either a) a mixture of text and numerics or b) null strings (""), e.g. as a result of formulas within those cells. However, usually we can find a column to use which is either purely text or purely numerical and which does not contain any null strings.
You can then define:
Name: Invoice_Invoice_ID
Refers to: =Invoice!$A$2:INDEX(Invoice!$A:$A,Invoice_LRow)
Name: Invoice_Requisition_ID
Refers to: =Invoice!$B$2:INDEX(Invoice!$B:$B,Invoice_LRow)
and reference these in the formulas instead.
Regards
Dear XOR LX
tried to replace the value in the Array formula with the name range but i am not getting any results.
{=IFERROR(TEXTJOIN("; ",,IF(E16=RequisitionB,Requisition,"")),"")}
the original array formula was as below
{=IFERROR(TEXTJOIN("; ",,IF(E17=Requisition!$B:$B,Requisition!A:A,"")),"")}
any suggestions?
Can I see your updated workbook?
Regards
Please change
RequisitionB to
=Requisition!$A$2:INDEX(Requisition!$A:$A,COUNTA(Requisition!$B:$B))
Requisition and RequisitionB need to have the same no. of rows.
Yes, you should
Please see attached
2019-01-31 12_20_22-Budget Template for Marwan.xlsx - Excel.png
Sorry should be Requisition not RequisitionB
=Requisition!$A$2:INDEX(Requisition!$A:$A,COUNTA(Requisition!$B:$B))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks