Download the Best Excel Bill Format for Effective Planning
Move your business forward with the airSlate SignNow eSignature solution
Add your legally binding signature
Integrate via API
Send conditional documents
Share documents via an invite link
Save time with reusable templates
Improve team collaboration
See airSlate SignNow eSignatures in action
airSlate SignNow solutions for better efficiency
Our user reviews speak for themselves
Why choose airSlate SignNow
-
Free 7-day trial. Choose the plan you need and try it risk-free.
-
Honest pricing for full-featured plans. airSlate SignNow offers subscription plans with no overages or hidden fees at renewal.
-
Enterprise-grade security. airSlate SignNow helps you comply with global security standards.
Excel bill format download for planning
Creating a bill in Excel can streamline your invoicing process and improve your financial planning. To utilize the airSlate SignNow platform effectively while managing your billing documents, follow these steps. This guide explains how to navigate airSlate SignNow's features to enhance your document management and eSigning experience.
Excel bill format download for planning
- Open your browser and navigate to the airSlate SignNow website.
- Create an account with a free trial or log in if you're already registered.
- Select the document you wish to sign or send out for eSignature.
- If this document will be reused, consider converting it into a template for future reference.
- Access your document and make any necessary changes, including adding fillable fields or inputting specific information.
- Sign your document and designate signature fields for each intended recipient.
- Click 'Continue' to configure the settings and dispatch an eSignature invitation to the recipients.
Utilizing airSlate SignNow offers businesses a simple and affordable way to streamline document handling and eSigning. With its extensive set of features, organizations can expect a high return on investment relative to their spending. Plus, it is exceptionally user-friendly, making it the ideal solution for small to medium-sized businesses looking to scale.
With clear pricing models and no hidden fees, airSlate SignNow also provides outstanding 24/7 customer support for all paid subscriptions. Start simplifying your document processes today with airSlate SignNow for a better workflow!
How it works
airSlate SignNow features that users love
Get legally-binding signatures now!
FAQs
-
What is the excel bill format download for Planning?
The excel bill format download for Planning is a customizable template that allows businesses to create professional invoices efficiently. This format can be easily edited in Excel, facilitating better organization and tracking of financial documents. -
How can I download the excel bill format for Planning?
You can easily download the excel bill format for Planning directly from our website. Simply visit the product page, click the download link, and the template will be saved to your device for immediate use. -
Is the excel bill format download for Planning customizable?
Yes, the excel bill format download for Planning is fully customizable. You can modify fields, add your business logo, and change the layout to suit your specific billing needs. -
Are there any costs associated with the excel bill format download for Planning?
The excel bill format download for Planning is available at no cost for our users. This aligns with our goal at airSlate SignNow to provide cost-effective solutions for businesses without sacrificing quality. -
What features does the excel bill format offer?
The excel bill format download for Planning includes features like pre-set formulas for calculations, itemized billing sections, and customizable aesthetic options. These features ensure that you can efficiently manage and create accurate invoices for your clients. -
How does the excel bill format download for Planning integrate with other tools?
Our excel bill format download for Planning is designed to integrate seamlessly with popular accounting and project management software. This allows for smooth data transfer and enhances your overall workflow efficiency. -
What are the benefits of using the excel bill format for Planning?
Using the excel bill format download for Planning helps streamline your invoicing process, saving time and reducing errors. It also enhances your professionalism when dealing with clients, promoting timely payments and better cash flow management. -
Can I use the excel bill format download for Planning on any device?
Yes, the excel bill format download for Planning can be used on any device that supports Excel. This gives you the flexibility to create and edit invoices from your desktop, laptop, or tablet, ensuring you can manage your billing on-the-go.
What active users are saying — excel bill format download for planning
Related searches to Download the best Excel bill format for effective planning
Excel bill format download for Planning
hello this is randy with excel for freelancers and welcome to the construction estimator in this week's training i'm going to show you how to create incredible construction or any type of estimates with a very unique tab feature i've never taught before we're also going to be able to add clients edit clients on the fly add or edit items on the fly and we're going to have a unique mini dashboard along with printing emailing and a whole lot more i hope you'll stick with us so let's get started thanks so much for joining me i've got a really fantastic training with a brand new unique feature i have never taught before certainly we have taught tabs before but never in this way quickly easily simply i'm going to show you how we do that and a whole lot more through this incredible construction estimator this template is absolutely free all i ask is for your name and email and i'll go ahead and get that sent right over to you through email or through facebook messenger i create these trainings each and every week or and soon to be every other week but we'll certainly make sure to bring you amazing content always and the best way to do that is to subscribe to our channel that way you get notified when you click on that notification icon bell i certainly do appreciate it there are many ways to support this channel this content training is free however if you do want to support us a great way to do that is through our patreon platform because on our patreon platform i bring you even more in fact additional training for example last week or we had the shopping manager i think it was two weeks ago shopping manager where we created this really cool drag and drop shopping manager well based on your request i added even more on our patreon platform we now have the ability to add different stores so we can create multiple stores multiple different aisle configurations and with the admin screen we now have can add different aisles for different stores we've got we can assign different categories on different stores so it's really an incredible feature thank you for your suggestions on that that is available this workbook template along with the training that coincides with the adding of stores on our patreon platform so i hope you'll get that the links down in the description excel for free and that's just patreon is where it's all at all right so let's get on this training because i've got a lot to share with you so we'll go over overview and then we'll get into the details of exactly how i created this every step every line of code every function feature and conditional formatting i will bring to you so grab your beverage of choice i will be creating this every step of the way as we as i've created it and i'm going to go over every detail with you all right so what we have here is it's a relatively basic application in the sense that we have an admin screen where we go over some defaults we've got item categories now what i like about this is these items categories are dynamic so that means as the users add these categories here we those tabs are then automatically customized here ingly so if we were to change something if we wanted to add an additional one or we wanted to change this maybe to an additional type of expense maybe you had field labor you wanted to separate out your field labor and your local or office label maybe add some office let's do office maybe you have office expenses associated with a project as you see there we now have office here so office is automatically updated inside the tabs and we'll go ahead and bring that back to where it was which was commission and but you can see how it's very versatile in the fact that we can do that creative so when we add that and automatically update it notice that that tab automatically gets updated and of course when we click on that we notice that all the commissions are added per job one of the great features of this is a drop down list and it's a dynamic drop down list so we here have here a set of items right these items are for any type of item you want however when we click on the equipment tab we see that it is equipment right now the great feature of this is that these are actually the same cells we're not changing any columns we're not changing any rows in the past our tab features has have hidden columns or hidden rows but in this case we're not doing that so actually i'm going to show you a trick it's quicker it's easier and it is really beneficial with a few challenges that we're easily able to overcome so each one of these are the same cells notice e9 right or let's say e13 in this case e13 we have a drop down list available of weekly daily service right we also can auto complete that if we have that so notice uh we can do weekly service or anything like that just typing in the w even though even if it's not here for example let's say we have a brand new job and we go on to let's see equipment right or let's go ahead and save this job first we'll add a customer here a project name here and then what we'll do is we'll just save that now when we go into the equipment notice that there's nothing here we've got information all we need to do is to start typing it and automatically comes up that's auto populate i'm going to show you how to create that however in the same column notice the same column e9 notice the same column e9 right so the same exact cell here we have a different list here if we type in bathroom it's going to come up right so notice that came up b-a-t-h let's bring that up again b-a-t-h there it is bathroom vanity okay so notice we have multiple multiple items on bathroom so that's going to take a little while to come up notice we have that so also what i want to do is also want to have the ability to that autocomplete and a dynamic drop down list so this particular drop down list is one way when we depending on it's dependent on the tab so when we click on the service tab that drop down list is monthly weekly or daily services if i click on labor that drop down list in the same cell is hourly or supervised labor so it's a dynamic based on the tavern which is really helpful it takes large projects like a construction job or construction and it breaks it down into the project details and the types based on a category very very important here and of course we have the navigation we can print it which is going to print it now we want to print it we want all the items associated with that we want all the items the equipment the travel and commission we want that all separated very very clearly whether we're printing it or emailing so i'm going to show you how to do that it combines all of the items on the particular estimate and combines them all in a very organized manner and of course we can create a pdf if we want to create a pdf of that just by clicking pdf that's going to create a very nice organized a very beautiful project estimate and we'll show you how to do that and of course printing and emailing it's going to do the same thing basically when we create an email it's going to create some default subject and of course we have that same pdf here but it's now been attached in an email format so we're going to go over all of that i'm going to show you every step of the way so make sure you watch the entire training here all right don't forget to comment below like it and subscribe right i respond to every single comment okay so we've got a set of item categories now these item categories are based on you know whatever the user entered it is those item categories that are visible in the projects it are those item categories that are also located here inside our item database so it's the same ones it is those same item categories that when we want to create add or edit existing if we click here we see that we're editing the existing right notice we have the item category up here if we want to create a brand new one we select on a new row and then we also have then available to have a new one notice that the category name items is default however if we're on equipment and we decide to create a brand new one here we click on here we see that equipment is set to the default of course the user can change that but it's nice to have that category set as the default based on the selected tab notice the selected tab will change the look of it and there's no items on there however so it's really really some cool really features in here and so continuing on with the admin screen we have a status right each of our projects can have a status that status is available in a drop-down list right here located in endline so we can have all those statuses so we know the status and then we have a per we have also in the admin screen we have are some project defaults what is the required profit margin perhaps i'm going to do something with this inside our patreon platform maybe i'll do an additional dashboard when we hit or but i put this in temporarily because i want to know when we're at or above a certain profit margin when we reach that our profit margin can be displayed here inside here our profit is 36 percent and that is because we're able to track both the costs and the price of the job the total cost of the job and the total price so i may want to know when we hit a certain profit margin on an estimate so we've got that here i want to know if we're going to be including sales tax and if we are what is the name of that sales tax and what is the rate of that sales tax that is available when we print it we will see that that sales tax becomes obvious right here in the footer of that we have a dynamic floating footer i'm going to show you so that sales tax five percent is going to be displayed in that footer we'll be getting to that of course all of that and also continuing on with the admin defaults we also want to know if we're going to be including the date and printout sometimes we don't want to use the date so if we change this to a no we do not want to use the date sometimes we use the date what i mean by date i mean this date column right here right when we print it out maybe we don't want to use the date column also if we're going to be sending it to our customers we certainly don't want the cost associated with that inside the printout so if we decide to turn this off no that cost is not going to be in that printout i also want to know the default project status when i create a brand new project what's the default status writing a bid so you can create your any status you want and then you can have that status available here and basically all that means is when we create a brand new project what do we want to locate it right here writing the bit all right and also what is the default footer message that default footer message is right here so we have as many footer messages as we want the name and then the message itself here and what that means is when we print that out we want to know what is the default message and that default message is right here notice in the footer it says this estimate is good for 30 days of the estimate so that can be changed on one the printed and emailed out it'll be visible at that point so we have those defaults there and the footer that's it for the admin screen relatively simple i've got a really cool background that i'm using on both the admin and the project and that background is basically a picture that we can have when we change the page layout so if i click on the page layout here and i delete the background you'll see and if i decide to add it in i'm going to put that picture in i'll work offline i don't know why that message comes up in excel kind of annoying but i have that available for you and of course i'll make that available on the patreon along with all the other icons and everything else here i have that available right here inside our patreon platform for those of you who want to have your additional so we got a construction estimator and i have this background image right here and that's the one i'll be using okay so that's going to be kind of helpful all right so we see that and we've gone over the admin screen we've got a project databases where all our project information is stored we have a project id we have the date of that project i've got the status i want to know the status of that project the client id the customer and the client name the project name the supervisor sales representative the footer message used the total cost sales price and the estimated profit so we're going to have all of that in there and we're also going to be using data mapping to locate that basically these data mapping is associated if you haven't seen my trainings before data mapping helps us reduce the code to just a few lines when we associate specific columns with specific cells meaning the date column is associated with n3 right here on the project so n3 and each one of those is associated with that okay so that's how we map these column data to the specific cells located right here all right so we've also got an items database item id the category which is really important because we're going to need to know where to place that we've got the item name the description the unit type the item cost and the sales price so that means when we make changes in here or when we make changes on the pop-up it's going to pull the information so if i want to edit that bathroom vanity and i want to look here and i see that items here and i want to add that in here but i want to make a change to that item maybe i want to change the cost of it i can just simply click here and it's going to edit it maybe that item cost has gone down to 199 so i can go ahead and update that and i can update that right here when i save that it is that information that's going to be placed up here and it's automatically going to be updated here if i decide i'm going to change the quantity it's automatically going to be updated so when i switch tabs here and go to equipment and then back to items we want to make sure that that loads and it's automatically saved automatically okay great so that's going to come our original data is going to come from the items then we have the project items now we notice that we have inside this project number four we have several items we have equipment we don't have any service but if we want to put service monthly service or we want some kind of weekly service we can do that and also the labor shipping and all the information that's associated with that but we need to keep track of them in a database right where is it located so it's located right here and that means all of these items are associated with project number one if we take a look at project number one right we have several items we have a service labor shop shipping travel so all these are associated with project id 1. if i want to locate that i just type in 1 here it's going to load that project id we see we've got several items we've got for equipment we've got some service items labor items we've got shipping items travel so this takes away a lot of the confusion when it comes to estimating especially when we have large estimates we want to break down those estimates into you know anywhere from five or six or seven or eight different categories we can do that very easily with this now you could easily add this to any type of a project right project estimate or project jobs or project scheduling could easily be added we've created those in the past okay but we need to store those and this is where they're stored right so we want to know the category i want to know the item id i want to know the date when was it added in if there's a date column the item name the description the quantity remember the date here is associated with this specific date right here so that's important and i also want to know the unit right how many units is it a pair each the item cost the total cost the item price the total price the project rows is associated and that means row 9 10 or 11 is associated with this row here 9 10 or 11. so that means when i save that information i want to make sure that it comes back into the exact same row as saved one before so it is important to save the row that's associated with that and i also want to know the database row what is the row associated with this if this is row 4 row 5 row 6 i want to make sure to say that we can do that with the formula row okay great and then of course lastly in our database we have a list of clients client id name address city state zip phone and email relatively simple with the clients when i add or update a new client i want to make sure that it gets sorted in alphabetical order so if i go back to projects and i click on let's say we want a new job and we we put in let's say john smith and he's no longer in the list i want to be able to add him very very easily simply it says here a pop-up john smith is not currently a client would you like to add this client yes it's going to pop up let us know we can put an address for that client whoops one two three four five main street okay and then we have la california and then just put in a zip code i want that automatically to add in automatically and then we can do that john at gmail so simply click and save it's automatically going to load that information and if i decide i want to edit that it's going to edit we're going to pull that up information if i want to make a change very very simple and i want to save that i want to make sure all that gets saved to a database and that's going to get saved down here so we have that new client id that's automatically added we've got the street and all the information that's added right here and we can pull it up again very very easily insert and i want to make sure that that name gets sorted in alphabetical list so notice that name is brand new name is here and it is sorted ingly alphabetically so i'm going to show you how to do that as well and of course we're going to be able to add this really cool dashboard where we've got a donut chart and a pie chart we want to know the prices per category and that's going to be based on the project that was selected so we're going to get we got it's a very very it's a kind of a tight application right just this one feature but it's really so many cool features features built into it the only way you know how many features if you stick with us so i know my trainings tend to be longer but i guarantee they're going to be packed with great content great learning new features that you can apply in your own applications today okay if you do want to learn how to build these applications by yourself and of course sell them for passive income check out our mentorship program in fact i've got a brand new mentorship express which is all the same content of my mentorship but it's packed into five months where you get six hours a week of training and it's a brand new low price i'll include that link down below okay great so what we're going to be doing here the first part is how do we create this really cool dynamic tab feature meaning how do we get these tabs created automatically if we want to add a new one right if i want to add a new one i want that automatically to come in and automatically notice now that that test is right here of course there's no items under that but we could easily create that and how do we want to do that if i decide i want to create a brand new item for that test i could do that and it's automatically going to be here very very cool right very very dynamic very easily user friendly if i delete that here i want the projects tabs to be automatically updated here notice the test is gone so how do we do that very very cool okay so the first thing what we want to do is we want to be able to create these tabs across it now all these tabs have actually already been created if we look into the selection pane there's maximum of eight because we have a space constraint so if we take a look let's say i think it's right about here at an item button right about here group 41 i think this is that i should have updated okay so if we take a look at this one right here see this tab right here this tab has already been created it's called category eight right so we have maximum of eight categories maximum eight categories however there's nothing on the eighth one so that of course has been hidden it's been hidden right here right so all we need to do is basically show and hide these tabs ingly and of course i'm going to show you how we get the contents of those tabs a really really cool trick how we get that the totals of those tabs so how do we do that first let's go into macro and see exactly how we created the position of this won't matter because it is vba that takes care of that position automatically so when we add it notice that that test is automatically in the right spot here so let's take a look at the macro that gets created now that macro it starts out when we make a change anywhere from c7 to c14 so we're going to go into vba and see exactly how changing those cells can automatically create and update those tabs ingly okay well that's of course going to come inside our vba if you want to get to the vba just click on the developers or alt f11 as a shortcut that will get you there and we've got several modules here and we've got a client module an item module that's going to take cover items project which is focused on the project saving updating deleting the project and the project sheet macros which is some of the ones on the sheet that's what we're going to start out so in the admin screen here just a few lines of code when we make a worksheet change right when we're making a change from c7 through c14 that's where our categories are located we're going to write a macro called category tab updates category tab updates that's the macro that runs it's going to automatically update those tabs it's located in the module called project sheet macros and here it is right here it is that first macro okay so the first thing what i want to do is i want to determine how many we have right now i've got a named range i want to know how many categories we have if we look in the form list we've got a name manager we're using of course our categories it's called categories right here that first one here and we're using a dynamic named range to create that using the offset formula so we've got seven items in our categories really what i really want to do is i want to determine how many are there right how many items are in that so we need to loop through all those categories and create those tabs or display those tabs and position them ingly okay so what we have here is that particular name range now with that name range what i want to do is determine how many are in there so we're going to create a long variable called category quantity and what we're going to do is we're going to use the application worksheet function count a count a of course is the same as we would be in the formula and i want to determine inside this named range from the admin how many items are in that and this is going to be seven right so now that we know how many what we want to do is we want to determine the width of it now if i look in the projects right i want to know the width of each of these tabs i want to make sure that they all have the same width it's nice but i want to stretch it automatically on these columns from column d all the way through column l i want to make sure that however many or however little they they come across this and they're all equal sizes so what i want to do is determine the width of d through l all the columns that way if we increase the columns or shrink the columns they're going to go along with it i want to know how many of those categories should fit on here and divide that and determine the quant the width for each particular tab so we can do that through just a little bit of math that width of each of that shape is simply going to be the width of those columns from d to l and we're going to divide that by the number of categories so for example if the width was 70 and we had particular seven quantities we'd know we'd have a width of 10 okay so a simple division to do that i want to set that initial left position and the initial tom position now remember all of these have been created already it's called category 1 category 2 category three right so they're all named already they're all they're either visible or they're hidden but they're all on the sheet all eight tabs are already on the sheet so to do that what i want to do is i want to set that initial left position we're going to set that initial left position as column d and the top position based on actually row seven so row seven is going to be that so that's what we're going to set set that a left is just based on column d7 or column d any row would be fine okay so once we have that top position and the initial left position we can then loop through all of the shapes and make sure they're positioned right so to do that we're going to loop we're going to create another long variable for the category number equals 1 2 8. now we're going to go through all of them we need to know whether we're displaying them or hiding them and of course where they're positioned so if the category number equals less than the category quantity meaning we have eight let's say we have our category quantity seven but we get to number eight if we get to number eight we're gonna do something else right we're gonna hide that category otherwise we're going to display it now to display it we would do the following so we already have all eight shapes already so with the category and category number meaning anywhere from one through eight we're going to set the left position to the left position the top position to the top the category width that unique width that we've already calculated and we want to make sure it's visible what good is we're going to update that left position i want to move it over to the right that left position but how much well it's current position plus we're going to move it the width of that shape right the exact width of that shape is going to move it over to the right the exact amount that we want to otherwise hide the category right otherwise meaning otherwise let's say we're on number eight we know that there's not eight categories so we want to hide that eighth one so let's just put those hidden categories hidden or non-existing hidden for non-existing categories non-existing okay all right so that's how all we have to do to actually create those relatively simple but now what i want to do is you notice that there's no text in here we're not placing the text that text has already been placed right and notice the text is dynamic right so they're linked to a particular cell because one i want the text to include the item category which is dynamic based on whatever the user enters here and two i want to know the totals of that tab of that particular tab and as we increase them i want them to be also increased so this number items 46 17 is going to change when the user changes that automatically as soon as they make the updates now here's the tricky part and a great challenge and of course we're going to show you exactly how to do it here's the tricky part right as we move to the tab this notice the data in this all changes right so how do we get this to save and notice that these are the same cells right but what i want to make sure to do is as soon as i move over we're going to save this data all this information it's going to get saved in this database right here it's going to see it saved all the way to the end right so how do i know and make sure that if we're on a specific tab it is that data that's automatically getting updated so this data here the the totals here is coming from our database meaning it's coming from here so we're going to get into that in a little bit as far as how we create it but we want to make sure that we're totally these totals on the current tab are updating as we make changes or as we notice that the tab the total in this tab is automatically changing as we add items automatically here so we can see that as we add here a different item it's automatically going to get updated very very easily okay so how do we do that well of course we got to link it to a cell if we take a look on here clicking here we see this is linked to cell b17 b18 and so on and so forth so let's take a look at those admin calls columns a and b and see how we've linked them so the first thing what we want to do is link this particular to of course our admin we could just as easily link it directly to here so we've linked this i can just easily probably easier just to do this equals admin whatever is located right here we don't need an indirect formula for that something for another reason so here's what it is so i've got this basically these are linked to exactly just to make it a little bit easier link them directly to the categories in our admin screen next up we have b17 all the way through b24 this is the cell that's linked directly to the tab here and what i want to do is basically i want to create a total and the total is going to be for everything that we have for this category right so i've created some name ranges going to help us with that total and i'm going to show you how that works so let's go over a few of those named ranges because what we really want to do is we want to total those totals based on what's located in this database the project item database so if we go into the formulas and then name manager we'll take a look at some of those formulas now the project items we've got one called project items category it's going to be associated with the category because we're only summing totals based on a certain category that's very important and also it's going to be so we're going to use this offset to do that and of course we also want to know for a specific project id it's got to be for the selected project id and not for all the projects we also want to know the total cost that's going to be help help us out for some of our graphs and we also have the project the total price which is we're going to be using so the total price so i've got these four particular named ranges that are going to help us in those formulas so the basic idea was i want to sum all the total price four items for project one right and i want that total to appear directly inside here now that's all good and dandy right so it's very easy just using a sum if some if based on this project id project two based on this category the selected category now that's selected category which we haven't gone over yet is going to appear directly into b5 whatever category we select b5 is going to take on that category so this is the category so so basically what i want to do is i want to sum all the categories and then what i want to do is make sure however there's a little trick so we're going to base it based on what's on the database right but what if i do this what if we're summing all the items based on the database that's been saved what if i add a new one here if i add a new item here we haven't saved it to the database until i click save as soon as i click save it's saved so how can i if the if all those totals are based on the database how can i get this up here we say certainly we'll just update this just total this that's fine that's great but what about when we switch to this tab right how do i make sure this is updated so the idea is this the selected tab whatever selected tab is we're going to use this this total here this column here l however for every other tab we're going to use what's in the database as soon as we switch tabs it'll save the database i'll so i'll show you that but so that's the idea whatever selected tab we are on it's going to add in this total here is going to be based on what is located in this column everything else is going to be located what's in the database okay all right so that's how we're that that's the main idea behind this particular formula here so let's take a look at this formula okay first of all i want a17 a17 is of course that particular item category i want that item category as the first part right because i want to show that item and then next up i want to space so we're going to put a space in there and next up comes the total and basically the selected tab we're using column l if it's not the selected tab we're going to base it on the database so here it is right here if b5 equals a17 b5 meaning the selected category equals a17 we are then going to add up whatever is in column l so we're going to use the sum we're using and of course we've wrapped it around text i want to round up i don't want i don't want i don't have a lot of room in these tabs so we don't want the decimals right so the reason i want to get rid of the decimal points i'm going to use round up and i'm going to add that into the sum so basically i'm going to sum everything that is in column l and i'm going to round it up i don't want any decimals so i'm going to show this zero and i want it in this format okay so i want the currency symbol so what that's going to do is add up everything in the tab but only if b5 equals a17 right so in this case we're on equipment so in this case right here it is b5 equals a18 equipment that's the tab that we're on same formula here l however however if it is not the selected tab i want to pull the sum from the database and we're going to use the sum ifs for that we're still going to use roundup but this time we're going to use sum ifs i want to know the project total price that's what we're going to be summing in from the database and i want to know the project based on this project id located in n2 n2 is our project id number two and i also want to know based on the project item based on the category of a18 right so i want to know all of the items for that specific project for the equipment and i want to total that up so that way we're going to get the total regardless so that means the selected tab is always going to be dynamic based on the items we add so the more we add the more this number goes up right however as soon as we switch it has already been saved that 815's already been saved into the database because switching these tabs is running the macro which i'll go over to in just a moment that'll be the next macro we go over it's automatically going to save these so i just added daily and weekly service if we look in the project item we scroll all the way down we see those items daily weekly service right here those two items just got added for project two two items service right 20 those are the item numbers so we have all this information saved in the database so that formula the sumifs formula now is going to total up the service and it's going to add in these two items so it saved it so it's really really cool because it automatically saves it as soon as we switch tabs now switching tabs if you didn't know already as soon as we switch tabs and we're going to go into this macro it's going to load up only those items from that project or it's going to load only those equipments from the product only those services that's going to load up into this section so it's very very simple we're just going to use an advanced filter to do that okay but right now so i wanted you to understand how we got to this formula and guess basically to sum it up that selected tab right that selected tab we're going to use this sum every other tab otherwise we're going to sum it from the database okay so as long as we've linked these to the specific cells that's how we get this dynamic content on these tab headers and we can out get a sum of all those very very easily okay great i'm so i'm glad i got to go over that with you now let's move on to the next one which is that selected tabs now when i select the tabs what do i want to do basically what i want to do is i want to clear i want to make sure to one save any changes that the user is making we're going to run this macro and basically it's this macro right here which is going to save that project the only difference is i don't want this fade out message to appear i only want this faded message to appear when user clicks save although everything else is the same so when they click this the first thing that's going to happen is they're going to save it the second thing that's going to happen is i want to color these differently now we can't use conditional formatting because these are not cells these are shapes all right and i also want to make sure this is bold and i want to switch the colors notice that it goes from light blue to medium blue however normally it's medium blue to white okay so i want to change that and we want to change that to bold and what i also want to do is i want to determine the selected tab category which is category here items and i want that to be used as a criteria so that we can take all these project items and we only want to load those items that are associated one with this project and two with this category okay and we want only those items to appear as results and then we're going to bring only those items and bring it directly inside the projects right here okay so let's go through that macro now and that's the next macro called category select that is the macro that's been already assigned to these individual tabs these shapes here already have been assigned this one called tab select so inside this macro what we're going to do is first of all what i want to know is i want to make sure that they've saved this project right it's very important if i'm going to be switching tabs i need to make sure to save it and of course i want to make sure that the project got saved and that means when we click on a new project if i try to switch tabs i want the user to say hey please save this project before switching tabs right so all they need to do is put in a customer click save and it'll automatically be saved so that's important and how do we know that well we would know that if b2 and let's take a look at some of these i've got a project load i want to know when we're loading the project now this is important because in one instance vba is going to make some changes here as we load in those tabs vba is going to make a change in another instance user is going to make a change i want to differentiate between those two types of changes this one b1 is going to help me do that right when the vba is making those changes loading it in this is going to go to true and back to false it happens very quick i also want to know that the project row what is the row that's associated with this project i've got a name branch called here a named range called project id and i've got a project id located here too so i want to know what row is associated with project id 2. if we look in the project database we see that project id 2 is located on row 5. so i'm going to use a named range and we're going to use a match to do that so we can just use this matching based on n2 the project id we're going to add 3 because we want to extract the row number so that's going to get us i also want to know the next project id we're going to use the max formula of project making sure they're all numerical and we're gonna add one because we want the next one if there's no data or there's an error we're just gonna revert it to one i wanna do the selected item row i've got conditional formatting here a few different rules that selected row is going to change here as we select something else so if we take a look at the conditional formatting we've got three different rules we're going to manage the rules and we're going to use one for the select row based on b4 just highlighting it in black in black bold font and then of course the blue background then i've got one particular conditional formatting very slightly white color very slightly blue color it's very very slight for the odd rows this will work and for the even rows this will work notice that e9 right e9 is consistent that is our starting row and it's also the row that we're going to be using for the applies to notice that starts and applies to d9 through l200 very good so we've got that up there that conditional formatting that's going to be triggered by change in this so the selection change event which we'll get into will put whatever the rows that's been selected here we've got an item category here now this and we've got the item tab here now when we select an item tab it's like here this is going to change to 2. the the macro that we're just about to go over is going to change this to 2 automatically and then we have equipment so what i want to do is i want to take this 2 and i want to pull equipment from it how are we going to do that well we can do it through index now we notice that we have a named range called categories right that's the same named range from the admin screen it's this one right here right so if i if i know that categories is here and i want to pull the second item from categories because it's a 2 i can just simply use index for that and we're going to extract that equipment so using that formula index of the categories based on the based on the number of b6 is going to extract that categories so all we need to do with our macro is simply do this get rid of the word category remove it in our macro determine the name of the shape that called it using application color remove the word category and what's that going to leave us it's going to leave us with the one the two or the three i'm going to place that number directly inside b6 so let's go and go through that right now so certainly we want to make sure that b2 contains a value if it's not let the user know to save that project we're going to turn off application screen updating temporarily and we're going to then again i'm going to run the macro that's going to save or update that i want to make sure that we are saving that we'll get into this macro in just a bit this is the macro that saves the project what it will do is it will save any new changes to new items in this row automatically as i switch the tab and that way it's saved to the database already so when i come back we can easily load in those items here they're remembered because they're saved inside the database so this macro is going to take care of that right before we do anything else then what i want to do is i want to determine the category number remember that category number is based on the shape name here is the name of the shape if we try to run this macro through this often we're going to get an error why is that because there's no shape that called it i called it from right here right so make sure that when you use application call you have to run it from a shape that called it in so what i want to do is i want to set that category number and as i said before if i remove the word category and i replace it with nothing using the replace command it's going to leave us with that category number i'm going to put that category number into the variable and i'm going to take that i'm going to put that directly into b6 that is the category number so now that i know the category number i can automatically here set that item so we know that's very important because i want that category number because i need to extract the name right from it that name is going to help us in these formulas that was very important we need to know the selected tab b5 is going to let us know the name of that selected category on that selected tab okay now what i want to do is i want to reset all caps now what we want to do reset all tabs is i want to set that selected tab i want to change those colors from blue to white from white to blue and i want to put it to bold so the first thing what we want to do is reset all of them back to their original color which is this light blue to white and non-bold so that's what we're going to do so for the category number one to eight we have all those shapes we're going to set that fill that filled to that blue down to white and that's can be done right here category category number the fill we want to color gradient and to get this to get this all i did was just simply run a macro while i changed the color and it came up with this and a little bit of other code i just extracted that out so it just turned out to be gradient horizontal two horizontal number two so that's pretty much it and then the reverse was just one so relatively easy and i want the bolt equal false right we're taking every single shape and we're making the bolt false right we don't want it bold and we want to set back to that original color that original color is this light blue on top and then white on the bottom however for the selected one we want to do something different how do we know the selected one because i've got that into a variable called selected category the number here if i know that right the shapes the category and the selected category this is the one we've selected this one we're going to change up that's still the two color gradient but the number is going to change to one this particular one is simply changing the horizontal position from top to bottom and bottom to top we don't really need to change the colors it's the same colors i'm just reversing the colors from top to bottom and bottom to top so this one will do just the opposite right it's going to take that go from white to light blue and it is that light same light blue that we're using to color this right so if we take a look at that format that shape we see that we've got that fill here is basically just the two colors from the white or actually it's quite light blue to this light blue so it's just those two colors so all i'm doing inside vba is simply basically just reversing the direction using these let's move that over here so i'm just reversing it let's move that over here i'm just reversing it inside vba right here i'm reversing this thing so one of them is type one here and one is type two so that's all we're doing is just like reversing these two down and up down and up but we're doing it in vba so it's relatively simple in that manner that's why we only have to change one to two and two one so this is the selected category from white to blue and then also we're going to take that font and make it bold so that's the only difference relatively easy to change those tabs right so very easy we don't need multiple shapes we don't need to hide and show shapes we just need to change the direction since we're using the same color and that's going to set that selected tab apart from the other so it gives it that tab so that the color is blended if you're going to be doing this just make sure that the blue on the board below is enough and want to make sure that the height and now another good thing here's another thing that's kind of tricky that you wouldn't necessarily know really zoom into that now we don't want borders the reason i don't want borders on these is because to take a look there's no border on this shape and i don't want a border because i don't want a separation so all i've done is just lowered this shape below it but you said there's no border but how do i get this border that notice that that does look like there's a border on the left in the upper well that's actually a shadow right so when i format that shape all i've done is given it a specific shadow on that and that's going to help out a slot so if we take a look in the shadow i've given it a little shadow and we're using this shadow one right here using the upper left shadow here but upper left i think it's this one here sorry it's a little bit off the screen so we're giving that a shadow let's move that over just so you know which shadow i use i'm going to bring it over a little bit here so we can see it so it's this one this one right here now it's on the screen so it's this one right here so the upper upper left shadow is what i've given it and we're giving it just simply one point and one point so it's very very small but it is the shadow that creates that border effect and that way we don't need to put an actual border because in excel we we can control top left right and bottom border so we can only control border or no border but we can use a shadow so that's where we get this effect so it gives us the tab effect otherwise we'd have no borders at all so that's kind of a nice effect without having to use borders okay i'll place that down there but vba would be able to take care of that for us we just want to make sure that it's placed down low enough so that it blends the colors it gives us a nice look and so that's all we have to do so again it's the shadow we're using not a border very very helpful to give us that tab effect without having to use borders because we don't want borders separating it we want that blend to show it okay great so that's how i gave it inside the vba that's how it's able to give it that particular colors okay now what i want to do is i want to set that selected item list it's very very important that we know the list and why is that important let's take a look here we have a date we have a defined list inside here notice that in this list we see that we have tile saw rental we only have those equipment however when we click on items same cell here right but different list same cell different list right how do we do that we can do that with a dynamic named range if we take home the data and we go into the data validation here we see it's called item selected tab we have a named range called item selected tab and that's going to come directly from our items list so what i want to do is i want to create a dynamic named range called item list and it's based on the items and so that means that whatever we've selected i want to know all the unique items for this service so if i click on projects and i click on items and we go back into this items list notice that we have a different list same named range data right same exact name range it's called the selected item list let's look at that one more time right name manager and then we have the item selected tab here same name we're using the offset items l2 and then one row down count a so here is a named range based on the results of an advanced filter now what i want to do is i want to take all of our items i want to create i want to know only those unique items based on the items and i want to have those appear here notice that the selected category is here if i change it right again we could simply do index categories b6 actually i can just do just i think it's just a little bit easier to equals right let's do equals because we always know that selected tab is going to be inside here and right up here we don't need to based on the number because i've got it right here in b5 right so we know in b5 as b5 changes right we know that item will change here inside it's going to change the equipment right so if i know the equipment that we tab and i want to know all the items associated i want those results to appear right here we can do that in vba with an advanced filter we're going to determine the last row we've got our criteria the category and we want the unique items for that category it is this unique items that make up our drop down list so we don't need to change the data validation on that because the contents of that dynamic named range will change automatically based on the results of this advanced filter so it's that advanced filter that we're going to run right now inside to create that selected item list determining the last row based on the item sheet now if it's less than four we're going to exit the set and then all we do is create an advanced filter we have a criteria our advanced filter is going to go from a3 through g right here through g our criteria is j2 through 3 and our results are going to come into l2 and that's just what we've done here j2 through j3 and then l2 that's where our results are coming that this few lines of code creates a very very cool dynamic name range so that way when the users are in the equipment tab they only have a choice of those items that are equipment if they're in the shipping they only have the choice of those shipping items it makes things a lot easier and a lot more organized when you create these detailed projects and we can do that through that dynamic drain range and like i said just a few lines of code okay now what we want to do is i want to add the items from the selected tab right so how do we do that and basically what that means is if there are any items that are associated with this project i want those items to appear here and so those are going to come directly from the project item database so what i want to know is i want to know all the shipping items that came again this is again i can just use we'll keep that there but basically this is the same thing project b6 is the same thing as whatever's in b5 so either one would work this is probably unnecessary so what i want to do is i want to know all of those items from the shipping on project 2 and i want those to appear here i want to bring those items in i want to place them directly inside here and so that's what we're going to do inside this vba first of all what i want to do is again remember i said project i want to set b1 to true i want to differentiate between when a user makes a change and when vba is making a change so when i make changes here i want this to go to true b1 to go to true so we're going to start it out b1-2 and of course before we finish we're going to set it back to false and so what we're going to do is first we're going to clear out all of the remember we've already saved everything we've already saved it right here all match up we've already saved all the items in the project in that tab here so we can easily clear it out and that's what we're going to do we're going to clear out everything b4 including b4 b4 is our selected item row so b4 we want to clear out we don't want to show any selected row i want to clear out b4 and i also want to clear out everything from d column d all the way through n well you say y n right n is going to be located we've got some data located in n as well okay now we'll get into the data that's going to save but there's two there's data information located here and i should probably group this all so we can easily move it i'm going to group this here and then what we're going to do is that we can easily move it okay so group it and then we'll unselect this and then so basically inside here we'll get to this in a moment inside here is we have some data in here so right in here i'm going to write in we have right here this is our project our item id here so i'm going to write in just temporarily item id i wanted the id that's associated with the item and i also want to know the database rows i'm writing a database row now they're here already here if you can see here but they're hidden right and i like them hidden i want to keep them and they're hidden based on the custom format if i go into the more number formats you see that i've got two or actually two different semicolons actually you could just put one here if they're numbers i can just put one thank you and then so we can just put one semicolon to hide them however if we want them displayed we can just change them back to general format general okay so it's going to display it and that means i know the item id that's associated with each one of these individual items are all the same right if i change that right to shipping right you're going to see a different item id appear here let's click on that shipping here we see the different item id is going to show up here of course it is located 12 it's just hidden right but it's here inside 12. okay so but i also want to know any database row that's been saved what is the row that's data notice this is 39 that is going to be the row that's associated with this item if we take a look inside the projects and we take a look on row 39 we're going to find that particular item right here so it is that item here it's located on row 39 and here's the item id so when we save that when i go ahead and click save it's going to automatically fix that here all right we'll reset that and set our titles it's going to be i want to put these here there we go that makes more sense put them up the top those variables they don't belong there now do they no they don't okay good so we'll just delete that and we can just reload that tab we don't need that there when saving that work that'll update that okay so we'll just update that okay so what we want to do is we want to make sure when i make a change here i want to make sure that that item database is saved automatically in down okay so to do that all we need to do is just select a different different number and we want to make sure the item id and the database row if those associated are going to be put in there again i'll go ahead and change these to general too so you can see that there are still custom general we can see the item id in the database row that's associated with that okay so we want to bring all this information so the
Show moreGet more for excel bill format download for planning
- Invoice format word for Healthcare
- Best Invoice Format Word for Higher Education
- Invoice format word for Insurance Industry
- Invoice Format Word for Legal Services
- Invoice format word for Life Sciences
- Invoice Format Word for Mortgage
- Invoice format word for Nonprofit
- Invoice Format Word for Real Estate
Find out other excel bill format download for planning
- Easily manage your electronic sign on phone with ...
- Sign documents effortlessly with e-sign forms
- Elevate your hiring process with our e-signature offer ...
- Easily electronically sign PDF on computer with ...
- Discover the free electronic signature PDF tool that ...
- Revolutionize your workflow with the best advertising ...
- Visix login made simple for seamless document signing
- Create your documents effortlessly with our text ...
- Transform your workflow with our digital media ...
- Experience seamless efficiency with Visix software ...
- Create your signature maker free with airSlate SignNow
- Enhance your workplace efficiency with a meeting room ...
- Experience the ease of virtual signature for document ...
- Effortless paperwork login with airSlate SignNow
- Convert PDF files to Word for free no signup with ease
- Validating with a digital signature made simple and ...
- Get your official sign-off with airSlate SignNow
- Easily streamline your word document signing process
- Create your internet signature for word documents ...
- Create a legally binding e-signature with airSlate ...