How to make this formula range expandable??

[mrxlsx]

Hi all,

I have a formula range like this " =IFERROR(SMALL($H$2:$H$16,G3),"") ".

It works only for H2:H16 at present, but I want it to be a dynamic one as the no of rows grow.

Basically I am trying to extract Invoice records from a self-expanding data range.

If I select an Invoice no in M2, I will get records for that Invoice no. But it has a limitation regarding its range, how to overcome this??

All the problem is in the Column I i.e. in the SMALL function's place.

Please check the below link for more information.

https://drive.google.com/open?id=0B6GD5M1_zN7bTUFaSWlubGtCcjQ&authuser=0

Please ask me for any clarifications.

Thank you
mrxlsx

 

[Smitty]

If you convert your data range to a Table and redo the formulas they'll have dynamic references (i.e. structured references).

 

[mrxlsx]

Hi Smitty,

I tried all the following options but for some reason I am getting only a few outputs i.e. I could not get all of the invoice records when I select the choice from the Drop-down.

1. Naming the column
2. Converting the data range into a Table

The idea is too good to use but it should be changed a bit to make that work perfectly.
All the problem lies with the data range in the formula, Any help is greatly appreciated. Thank you all.

mrxlsx

 

[Smitty]

Do you meant that AutoFilter isn't displaying all of the records you expect?

Can you post a screen shot?

 

[mrxlsx]

Hi Smitty,

thanks for the kind reply.

The whole process is for the sake of printing the selected invoices' details for billing purpose(literally for printing the bill in the counter). The moment I select the Invoice no, I will get all records pertaining to the Invoice and print them all as a bill.

In the original sales data I have 22 columns. But I don't need 22 cols to be printed. I just need what I have shown you in the Sample excel sheet, hardly 4 cols i.e. Products, Qty, Rate, Amount. But if you go for Auto filter, here are some problems I expect.

1. Every time the Sales counter personnel has to do the process of filtering,
2. You will get other unnecessary filtered columns,
3. Sometimes as the transactions grow in thousands Auto-filter will become late.

Did you see the sample work book and try to print the new records added? Try that you will understand the problem. The formula will not pick the new records.

I am 100% sure that if someone can make the formula range expandable, the formula works smoothly. Even making the sales data as table also could not address the problem as it is not picking up the new record entered.

I have seen some time back Mike Girvin has used this technique some where and I have been on web search for the last 3 days. He used Index or offset function I guess to expand the range. But I get lot of tricks on this, can't choose exactly what I need from 'em. I am trying all.

Please help me.
mrxlsx.

 

[mrxlsx]

Here's the screenshot of the problem.

When I select the new record it could NOT pick that.

https://drive.google.com/open?id=0B6GD5M1_zN7bV0ZBX1IxS2h0Tnc&authuser=0

 

[Smitty]

Sorry, I can't access your files.

 

[mrxlsx]

Sorry Smitty, I thought I had given the right sharing link.. try now...!

https://drive.google.com/file/d/0B6GD5M1_zN7bV0ZBX1IxS2h0Tnc/view?usp=sharing

https://drive.google.com/file/d/0B6GD5M1_zN7bU08zbUZkNE9qYkE/view?usp=sharing

Thanks

 

[Smitty]

I'd use a Pivot Table(s). If you add Slicers you can quickly toggle between various views and it's a lot faster than changing Filter criteria.

 

[mrxlsx]

Found a solution for this, thank you very much, Smitty.