Hello,
Is it possible to have a dynamic listing of the Invoices in the Status sheet and if so how? Can this be done preferably with formulas?
Thank you very much in advance for your help,
bj
*to open all worksheets on the Switchboard is 'Unprotect All Worksheets' password is "pass".
is this dynamic list coming from the tab names or can it come from the customers tab ID column?
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
Tab names because it needs the financials thanks
so, basically you want the list to be 100, 101, 102... 105 and that list would be... where exactly and you want it to automatically grow as you add more tabs right?
I see a spot where in cell C9 (merged with 10 and 11) that you have "list of all Invoice #'s" - where do you want that list? Or are you wanting to put in an invoice in C14 and have it populate the other items in row 14 such as invoice date, customer, due date etc.?
No it should not include 100 (that is reserved to be a template for copies). It should not include those invoices that have not been used yet.
Yes it should populate the table starting in row 14. I have the merged cells in yellow for notes.
Thank you
so, 101, 102 and 103 have names in the bill to section but 104 and 105 do not, is that what constitutes "use"? so the list would only include 101, 102 and 103 but not 104 or 105?
I'm starting to think this might require a VBA solution.
Maybe you are correct about the vba. Is this your expertise?
this site lists one way to do it with formulas (and no, VBA isn't in my wheelhouse).
https://www.howtoexcel.org/tips-and-...k-without-vba/
You can then create hypertext around those that will allow you to jump to the specific tab you want and you can also use hypertext to jump back to the Status tab.
Thanks but I don't want all worksheets listed only the invoices.
Maybe this should move to vba section?
a moderator has to move it unless it is in the dropdown list at the top for the original poster (thread tools).
I can't swear to it that it is only VBA to do this, I just cannot think of a way to create a formula to pick up only tabs with info on them to list. I can create a formula for you to click on to go to a specific tab and another to go back again but that isn't what you want. I've tried to help you refine your request more so someone else picking this up hopefully can get you what you need. I'll be interested in seeing what is offered, good luck.
Thanks Sam for your help.
Moved to VBA section.
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.
Thank you Ali
I hope this isn't causing too much trouble.
This formula soln may not be workable, however...
You have seven sheets (non-invoices) that must remain first in the window.
Create the *NAMED FORMULA*:
name: tabNames
refers to:on status sheet, cell C14:
Please Login or Register to view this content.
*A8 because 101, the first invoice is the eighth tab in the window.Please Login or Register to view this content.
Retrieve the Inv. date into B14:
Retrieve Cust.ID into D14:Please Login or Register to view this content.
etc...Please Login or Register to view this content.
Ben Van Johnson
Thanks but when I add more invoices it isn't updating.
I appreciate you trying though.
slightly different approach to Protons solution...you can add the sheets prior into the table
hide the Column N to tidy it up
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks