• 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.

How to make this formula range expandable??

rkspeaks

rkspeaks

Member

Attachments

  • Invoices.xlsx
    36.5 KB · Views: 0
Is this...

=IFERROR(SMALL(INDIRECT("$H$2:$H$"&COUNTA(H:H)),G2),"")

=IFERROR(SMALL(OFFSET($H$2,,,COUNTA(H:H)),G3),"")
 
Hi Deepak,
Thank you so much for the reply,
Did you try adding new records and pulling the same by selecting the choice there in the Drop-down. It is not picking up the newly added records for some reason. This is what I have been on for couple of days. Please help me.

rkspeaks
 
rkspeaks said:
Hi Deepak,
Thank you so much for the reply,
Did you try adding new records and pulling the same by selecting the choice there in the Drop-down. It is not picking up the newly added records for some reason. This is what I have been on for couple of days. Please help me.

rkspeaks
Click to expand...


I just given the idea to auto extend the range.

$h$2:$h$22 = INDIRECT("$H$2:$H$"&COUNTA(H:H))
$h$2:$h$22 = OFFSET($H$2,,,COUNTA(H:H))

when new record will be added in col H same will auto get updated in the said function.
 
Deepak,

Actually If Col-A is having some data i.e Invoice no, then this formula should extend its range to the same row in Col-H and reflect the same record in the drop-down which was missing. When I try to pin-point the problem I found out that it is NOT properly refreshing after the formula got evaluated. But the code which you supplied is perfect as the other Excel heroes on Excel forums.

What might be going wrong where?? What are my options in this case?
 
Hi ,

After adding new cells in Col A i found that

Col K {DynamicRange} updating..
Dropdown in M2 updating...
Formula in col G,H,I updating....

As you already using the tables that's nice so that i have also updated the formulas..
Check these & let me know is still something i am missing.....o_O
 

Attachments

  • Invoices.xlsx
    34.9 KB · Views: 0
Deepak,

You are the Hero man, Really you have put a full-stop to my problems I have been facing for the last few days man. I thought it can't be done by Excel and thinking for other options. Everybody is giving me perfect formulas worldwide but I could not get what I want to. Finally it ended here. See the power of Excel.

You did a magic, Excel is the machine, you are the man.
 
Deepak,

How long this formula works?, I mean how many rows it can handle as the data keep on growing an growing. Can I use it for 1,048,576 rows? Can the formula you mentioned handle/capture and reflect so many rows??

I added some fifty rows, some times it is not reflecting the last record.
Did you tried that at your end?
rkspeaks
 
rkspeaks said:
Deepak,

How long this formula works?, I mean how many rows it can handle as the data keep on growing an growing. Can I use it for 1,048,576 rows? Can the formula you mentioned handle/capture and reflect so many rows??

I added some fifty rows, some times it is not reflecting the last record.
Did you tried that at your end?
rkspeaks
Click to expand...


Pls share the wb.
 
Deepak,

I will test this tonight spending some hours on this. I am 99.99% confident that the small change in the formula will makes the entire billing process fine.
I will meet you tomorrow on this to congratulate you. So far so good.

Thank you so much, Deepak, you made my project finished.
 
You must log in or register to reply here.
Back
Top