• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Trimming Array Formulas and Circular references.

M

Mahir Yagimli

Member
Hello Everyone,

I tried to create an index page for a material table. I guess this task exceeded my knowledge a bit. The Workbook size increase from 1 MB to 5 MB and shows circular reference failure's.

We get on a daily basis a workbook with available Materials from our Factory. This includes about 47 Products. For each Product there is one sheet with its Name.

Now I thought it would be a good thing, if there would be a summary of all the sheets, which only shows the available Material.

So I created an Index sheet with a summary. First I extracted all the sheet names, then I put them in Dynamic Data validation list. (Index!AE14:AE..).

I've used excessively Indirect, even I know it is not recommended, since indirect is a volatile function.

Please have a look to the sample file and share your Idea's, how you would do this summary sheet or what I can do to reduce the workbook size and get rid of the circular reference.

I'd be very happy for any shared suggestion and Idea's.

Thanks to everyone, who has taken their time to read through this.
 

Attachments

  • TEST1.xlsx
    217.2 KB · Views: 3
Faseeh said:
Hi,

Wasn't it a better option to append everything in a list and make a pivot table?
Click to expand...
Hi,

I need it to be updating otomatically.

So that my Co-worker changes the entry as it appears in the stock and the Sales department, can look through the available material with ease.

Thank you for your advice. For myself I will have a try.
 
Mahir Yagimli said:
Hi,

I need it to be updating otomatically.

So that my Co-worker changes the entry as it appears in the stock and the Sales department, can look through the available material with ease.

Thank you for your advice. For myself I will have a try.
Click to expand...
@Faseeh : Just now I have tried your suggestion and it didn't work. I need the summary of multiple sheets.

So how would you extract data's from multiple sheets with pivot table function?

Power Pivot?

I'd be glad, if you can explain a litte bit more how you would solve this problem with Pivot tables.

Thank you.
 
Hi Mahir ,

There is nothing wrong in using a few hundred INDIRECTS ; try it and see if your workbook becomes sluggish.

My first suggestion is that you are using so many INDIRECTS because there is no helper column ; if you can use a helper column with the following formula :

=SMALL(IF(INDIRECT("'"&$B$3&"'!l514:l936")<>"",ROW(INDIRECT("'"&$B$3&"'!l514:l936"))-ROW(INDIRECT("'"&$B$3&"'!l514"))+1),ROWS($B$6:B6))

this will give the index numbers ; each formula in the table needs to make use of these numbers ; straightaway you are reducing 21 INDIRECTS per row.

My second suggestion is whether the column layout can be the same in your Product worksheets as well as the Summary sheet ? If so , instead of having a different formula in each column , you can select all the columns and have a multi-cell formula.

Suppose your helper column is K , starting with cell K6 ; select all the cells B6 through I6 , and enter the following array formula , to be entered using CTRL SHIFT ENTER :

=INDEX(INDIRECT("'"&$B$3&"'!c514:j936"),K6,{1,2,3,4,5,6,7,8})

This will populate all 8 columns with the respective data from the product sheet.

Narayan
 
To give a better understanding of what i mean I have added a few sheets to the sample file.

Now for instance you can choose Product 2 and you'll see the available material for product 2 immediately on the "Index" Sheet.
 

Attachments

  • TEST1.xlsx
    484.8 KB · Views: 1
NARAYANK991 said:
Hi Mahir ,

There is nothing wrong in using a few hundred INDIRECTS ; try it and see if your workbook becomes sluggish.

My first suggestion is that you are using so many INDIRECTS because there is no helper column ; if you can use a helper column with the following formula :

=SMALL(IF(INDIRECT("'"&$B$3&"'!l514:l936")<>"",ROW(INDIRECT("'"&$B$3&"'!l514:l936"))-ROW(INDIRECT("'"&$B$3&"'!l514"))+1),ROWS($B$6:B6))

this will give the index numbers ; each formula in the table needs to make use of these numbers ; straightaway you are reducing 21 INDIRECTS per row.

My second suggestion is whether the column layout can be the same in your Product worksheets as well as the Summary sheet ? If so , instead of having a different formula in each column , you can select all the columns and have a multi-cell formula.

Suppose your helper column is K , starting with cell K6 ; select all the cells B6 through I6 , and enter the following array formula , to be entered using CTRL SHIFT ENTER :

=INDEX(INDIRECT("'"&$B$3&"'!c514:j936"),K6,{1,2,3,4,5,6,7,8})

This will populate all 8 columns with the respective data from the product sheet.

Narayan
Click to expand...
@NARAYANK991 : Wow, superb. That's Great.

I will need a bit to digest, but that's great and genius again.

Thank you so much.
 
Hi Mahir ,

See your file ; I have not corrected the column headers on the Summary sheet , but the formulae are entered.

Narayan
 

Attachments

  • TEST1 (1).xlsx
    437 KB · Views: 1
Hi Narayan,

Know I have understood second suggestion. That is even much better, then what I had in mind. To work around the problem with the different column layout, I have created a series of formulas which extract the Values belonging to the rows that have "Stok" as value, using a simple countif function.

The formulas are on each sheet in $B$514:$j$938. Also there is one Sum Function on every sheet in $A$856.

Your approach would not even need to create this extra formula's. That's Great, but then the Column layout would have to be the same, which I can not change since it is a form belonging to another department.

Please also have a look your file, that I have adjusted the way I need it to be.

Thank you Sir.
 

Attachments

  • TEST1 (1)_Rev.xlsx
    408 KB · Views: 1
Hi Mahir ,

Now things are more clear. Can you confirm a couple of points ?

1. Will the daily data be given to you in the format which is available in the range B13 through N44 ?

2. Will you be entering the text STOK in column A ?

3. Will all the STOK items be together , or will there be some rows in between which have the text Customer ?

Narayan
 
Hi Narayan,

To 1. The daily data will be entered from a colleague in every product sheet in the range b13:n500. There will be never so much items, but for this calculation the mentioned range will do it.

To 2. The Text Stok will be entered by my colleage in column A. It means the goods are available on stok. Once a Customer selects the goods, the text will be replaced with Stok.

At the moment, the Countif function counts how many rows are there with the text Stok. This Number is the limit for the extraction, which starts from the 13th row. Thus if there are any other text strings inbetween they will be extracted aswell, which can be very irritating.

To3. To solve the problem my colleague will cut and paste the rows in such a manner that firstly the "Stok" rows will be shown and afterwards the ones with other texts. So that's a yes.

I haven't found a good way to solve this problem.

By the way, I have applied your methods in the real workbook and the size was reduced around 200 KB, but more impressive was that it works fast and has no circular reference any more.

Thanks.
 
Hi Mahir ,

Thanks for the clarifications. I was trying to see if we could do away with the intermediate table in rows 514 and beyond , and directly take all the data from the input table in rows 13 through 500.

See if this is OK.

Narayan
 

Attachments

  • TEST1 (1)_Rev.xlsx
    413.4 KB · Views: 2
Hi Narayan,

Works Great.

Still one more Question I do have. In column Z of the "Index" sheet there is the =SMALL(IF(INDIRECT("'"&$B$3&"'!l514:l936")<>"",ROW(INDIRECT("'"&$B$3&"'!l514:l936"))-ROW(INDIRECT("'"&$B$3&"'!l514"))+1),ROWS($B$6:B6)) Array function.

In this function the range of the intermediate table's are mentioned.

Would this work also with the range you have written in your extracting formula 13 to 500.

The function needs to extract, when there is a "Stok" Text string in the A column.

To achieve that I used the following Formula =COUNTIF($A$3:$A$513,"Stok") in B514

The result was combined with the Formulas in the intermediate section, which are basically like the following Array formula =IF(ROWS($C$514:C514)>=$B$514,"",INDEX($C$13:$C$513,SMALL(ROW($M$13: ($B$514))-ROW($M$13)+1,ROWS($C$514:C514)))) Entered with CSE

Is there a more efficient way to get rid of the intermediate tables?

Let's say a SMALL(IF(... combination. I tried a few, but somehow it didn't work out. For example i tried =SMALL(IF(($A$13:$A$513)="Stok", ROW($A$13:$A$513)-ROW($A$13)+1),ROWS($C$514:C514))

Thank you for your incredible knowledge on Excel.
 
Hi Mahir ,

Sorry , I forgot about the formula in the helper column ; let me see if that can also reference the data directly.

I'll upload the revised file if I get it done.

Narayan
 
Hi Mahir ,

The newly introduced helper column is Y ; see if the formula is correct.

I have already replaced all references to column Z with references to column Y ; at present the tables in row 514 and beyond are not being used.

Narayan
 

Attachments

  • TEST1 (1)_Rev.xlsx
    424.1 KB · Views: 2
Hi Narayan,

Just when I thought about how to solve the issue with extracting only the rows that contain "Stok" in column A. You came across with a Great solution.

=SMALL(IF(RANGE<>"",IF((RANGE)="STOK",....

Thank you. Really incredible.
 
Hi Narayan,

Wow. I just applied your method to my existing material workbook and it works perfectly. The Best thing though is the workbook size reduced around 3 MB. From formerly 5 MB to 1,9 MB. The Workbook without formulas was 1,1 MB.

You are truly an Excel Genius !

Please mark this thread as solved.
 
You must log in or register to reply here.
Back
Top