LinkBack URL
About LinkBacks
Hello,
On the Status sheet there are drop down lists in M3 and P3. Is there a way to remove the empty spaces in those drop down lists?
Thank you
Hello,
On the Status sheet there are drop down lists in M3 and P3. Is there a way to remove the empty spaces in those drop down lists?
Thank you
Yes. Change the Named Range used for data validation to :
=Customers!$C$2:INDEX(Customers!$C:$C,MATCH("zzz",Customers!$C:$C))
that one is for the customer ID. Do similarly for the other one.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Thanks Glenn you are the Formula King!
I did similar for the CompanyID but since there are IDs that don't have Company Names there are still blanks. Can you take a look please?
OK. I see the problem. Can we create a helper column somewhere, anywhere - for example on a hidden sheet, that contains all the non-blank values??
Sure of course thanks
The formula is on ID_List:
=IFERROR(INDEX(Customers!D:D,AGGREGATE(15,6,ROW(Customers!$D$2:$D$100)/(Customers!$D$2:$D$100<>""),ROWS($1:1))),"")
Adjust ranges, as needed. The Named range is now:
=ID_List!$A$1:INDEX(ID_List!$A:$A,MATCH("zzz",ID_List!$A:$A))
Thanks so much that is perfect!
You're welcome. Thanks for the rep.
Hi, these comments are intended as constructive and in no way meant negatively.
I am surprised that no one opening your file to assist your query has failed to comment upon the fact your application would rapidly grind to a halt.
You are saving each invoice to a separate tab, each embedded with formula, 1,000 invoices = 140MB, even if Excel allows you to add that many tabs.
Personally I would re-visit your application and emulate procedures adopted by most accounting programs.
Create a single sheet "transaction history" then create or retrieve your invoices 'on the fly' (using template or coded structure) as and when needed.
Below is a recent link that demonstrates how quickly an application can become memory hungry and slow.
torachan.
I didn't look at the individual sheets that torachan has mentioned above, but I did notice the empty rows (and columns on some sheets) which are causing file bloat due to the way you have set things up.
Instead of 'setting up' rows in advance, use a structured table to make everything dynamic.
If you remove entire rows or columns of data, then use right click and delete instead of the delete / backspace key.
Take a look here for more on this https://chandoo.org/wp/big-trouble-i...e-spreadsheet/
The structured table idea has been floated in earlier threads, but so far ignored.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks