Excel '03-2013 Dynamically populate 2nd Worksheet on 1st Worksheet cell condition.

[Regulluz]

Hello All and BIG THANKS for being such a wonderful community - that I only come to when in need, sadly.

I have this situation in which, I have 3 worksheets.
Sheet1 (FORM) - User inputs data here.
Sheet2 -(DATA)- Big table of products
Sheet3 - (REPORT)- Nice report for printing

Operation:
1. User inputs quantities and prices in FORM, which are linked to DATA.
Example: DATA Cell QTY =FORM!A1
2. DATA then calculates items, dozens ordered, cubic feet, weight, etc.
3. REPORT only grabs rows from DATA that have a quantity in place (from FORM). It also grabs its corresponding description, pricing, dozen, etc.

>0,ROW(INDIRECT("1:"&ROWS(tblDATA[QTY])))),ROW(INDIRECT("1:"&ROWS(tblDATA[QTY]))))),"",INDEX(tblDATA[ITEM],SMALL(IF(tblDATA[QTY]>0,ROW(INDIRECT("1:"&ROWS(tblDATA[ITEM])))),ROW(INDIRECT("1:"&ROWS(tblDATA[QTY]))))))]

4. As of now, the only calculations are made in DATA worksheet.

The Situation:
I have used the vlookup function to populate REPORT items. However, items in REPORT has over 500 rows of functions just in case an order turns out this big. Besides, there are vlookup functions in different columns that look up after the that initial item to bring pricing, description, etc into the report.

However, this has turned out to be quite SLOW and sometimes Excel crashes. In addition, I have to manually filter the report by using an extra column to remove those extra rows with functions that have no value.

Would you guys share a more efficient idea to do what I am trying to do? I know that a database is the way to go but as of now, Excel is the only option I have.
Thanks to all in advance.
Best regards,
Regulluz

 

[byundt]

For Operation 3, your formula is needlessly complex--if I understand correctly. Since you are using Tables, you have the IFERROR function available to you. As a result, you only need to test the range once to see if it returns a value. The following formula (which I assume is placed in cell C1), must be array-entered. It may be copied down, and will return each successive ITEM that has a QTY larger than 0. When the list is exhausted, it returns an empty string (looks like a blank).
=IFERROR(INDEX(tblData[Item],SMALL(IF(tblData[Qty]>0,ROW(tblData[Item]),""),ROWS(C$1:C1))-ROW(tblData[Qty])+1),"")

By eliminating the INDIRECT functions, you reduce the number of cells that need to be recalculated at every change in value in every open worksheet. You also only calculate the array formula once. As a result, your worksheet should recalculate much faster.

 

[zelgar]

Is there a reason why you don't want to run a macro to get the data for your report? It would be fairly easy to create and would eliminate your need of removing rows of empty data.