Discover the Best Plumber Bill Format for Export

Plumber bill format for Export

[Music] welcome everyone to another ctc software webinar my name is sean service technical evangelist here at ctc and today we're going to talk about plumbing code calculations using a bim project suite collection of tools spreadsheet link and schedule excel so let's quickly kind of cover what it is we're going to talk about and why revit being a fantastic building information modeling tool sometimes has trouble with some of those more complicated calculations and this is especially noticeable when you're trying to run like cross category maths you know how many rooms do i have on certain levels and what's the occupancy of those or how many rooms that are associated to certain whatever a lot of different things and then when i'm trying to tie that into plumbing fixture counts and stuff like that that can be really annoying and so when we're doing this in a lot of cases especially like in today's example the plumbing code calculations we really usually take this out to an excel environment or a spreadsheet environment and then we run our calculations there to create the graphic layout that we want because revit really can't do that very well either and then we we manually input a bunch of information and then turn around and print that stuff to a flat image or link it to a dwg and bring that back into revit and we do this stuff all manually but we can do this incredibly far more efficiently by using some tools that we're going to talk about today which is spreadsheet link and schedule excel and in today's example i'm going to take you through the settings on exactly how to configure this and how you can calculate plumbing fixture counts now if you're doing something else maybe a little similar maybe a little different whatever we're going to go step by step through this process now i want to be clear today is not a demo today is very much a training on how to do the things that we do if you want to see a demo of this workflow the introduction to uh spreadsheet link and the introduction to schedule excel demonstrate this workflow but that and that's a great null but i want to show you how to do it so we're going to go through how to establish a repeatable workflow we're going to generate reusable excel templates that we can use over and over and over again we're going to leverage some excel formulas and i use the term excel here because this is what i'm going to be using today in my example certainly you can use other spreadsheet programs as well but we're going to use excel formulas to drive calculations and some graphics and then we're going to take those graphics that result from excel and we're going to bring that back in to the revit environment for use on revit sheets i'm going to go through that full workflow start to finish today and hopefully hopefully you can pick something up from this and you can leverage this in your own workflows with your own excel files before i dig in i want to show you exactly what we're going to be doing okay just little screenshot here in spreadsheet link we're going to be generating an export of rooms you can't quite see on the screen right now but we're going to generate an export of rooms using level area a value that i've made called area per person ctc and a value that i've made called occupancy ctc and we're going to run some maps here i'm going to use a little formula to drive based on the area and the area per person how many people are in a space rounding up or down in my case doing a round down calculation i'll show you how i did that and then i have all these extra tabs down here at the bottom that came from excel they in no way came from revit this is all excel based stuff and so i'm going to be taking that information and bringing this back into revit in basically this exact layout as what we would call a revit schedule this doesn't look like a normal revit schedule but revit's brain is going to think it's going to be a schedule we're going to bring that in and drop it onto a sheet and we're going to have all the numbers in this spreadsheet from excel automatically filled out and ready for us to use while i'm going through this today if you have questions i encourage you to throw them in the questions window i will be watching those and trying to answer them as live as i possibly can i also will stick around at the end of this discussion today and answer any questions i couldn't answer in line so by all means throw questions out there and i'll see what i can do to make sure i answer them now since i'm not a big you know individual on death by powerpoint let's just go in and check this out live okay so i'm gonna jump into revit i'm gonna open up a project here and uh let's see this this little office building that i've got i'm gonna basically be doing occupancy calculations in here with my my custom parameters that's about the only thing that you have to have in your project that's custom is those two parameter values that i've got um you can of course use whatever ones you want and just follow along with this if you want to or whatever but i'm going to use those two values to run the maths in my code plan my life safety plan or whatever you want to name it you can see that i've got some rooms out here and i've got some room tags pre-placed now if i select this room you can tell that i've got an area that's a built-in revit parameter and i have an area per person ctc this is one that i made this is for me to be able to enter a value this is currently not a shared parameter it's just a project parameter it's not shared because i'm driving this particular value with the occupancy category room key schedule key and this way when i switch out to different types of rooms and i've pulled this list from an older ibc i think it was ibc 2007. doesn't really matter it's just a list of names and square footage values here but as i'm using this calculation it drives that number right there so a user can choose the occupancy category by name and it drives in the area per person requirement that load factor you can use whatever terminology it is you want to use but i have here an area and an area per person requirement and i want to do some basic maths i want to take the area and divide it by area per person and have it driven automatically into the occupancy ctc parameter value now i'm choosing not to use the built-in revit occupancy parameter value because that is text and it's trash and i can't do other calculations in some cases off of that and i like having actual numbers or integers or areas or things like that so i've made my own parameter and i leverage this in other workflows you'll see these workflows coming up later on um and where i need an actual number or integer predictable value coming out so that's why i've made my own parameter this is not required for this workflow it's just how i've chosen to do it so what am i going to do well i want to to do my math and yes i know i can do a calculated value in a schedule but that doesn't automatically drive into the room tag and yes i know that i can do a calculated value in a room tag but that could potentially not match my schedule and in both of those cases that value does not actually exist on the room so it's not really bim it's calculations happening externally the room doesn't know about it which means none of my other consulting engineers can know about that value by checking the property of my room object if i delete the schedule the data's gone if i delete the room tag the data doesn't really exist i want my room to know what its occupancy is and that's part of the reason for this workflow but i'm not doing occupancy just for the sake of doing occupancy i want to then leverage that to figure out based on code compliancy calcs how many water closets labs drinking fountains urinals whatever do i need on this floor or in a certain smoke compartment or you know you name it how do you want to calculate that out we need to find a way to do that and it's beyond just occupancy so i'm using this workflow for two purposes i'm using this so that i can get my occupancies done automatically pushed into my room elements that other you know team members can leverage that but i'm also using this to get my my code compliancy calcs and you can expand upon this if you want to how many parking spaces do you need how large do your egress openings have to be total on a floor whatever what is you know you can do all kinds of stuff with maths with this workflow that i'm not going to be demonstrating today oh and by the way this is of course being recorded so you can always come back to this later in case you missed something however i do recommend if you're going to try to follow along or you're going to try and take notes by all means do that because i find that i remember more when i take notes on things okay so where do we go for this and i'm going to show you how to get these tools later if you don't have them yet but from the ctc software tab here at the top this is where our suites i'm going to go here to the bim project suite i'm going to pull some things off the ribbon just so it leaves with me a little bit more space here bim project suite is where i'm going the tools in green and the two tools that i'm going to focus on today is spreadsheet link this is one that lets me take data from my model pull it to a spreadsheet run calculations and round trip data back in the other tool that i'm going to use near the end of this workflow that i'm setting up for is this one here schedule excel this one lets me take a an excel file and basically build a revit schedule from it a graphical revit schedule from an excel heavily formatted environment okay so i'm using this for the data exchange and the calculations and i'm using this for the graphic import so i can get the representation of what i want all right starting with this here spreadsheet link i'm going to open up spreadsheet link i'm going to start this workflow by just showing you how to get the data okay i'm going to go in here for a fully repeatable workflow i do not recommend using schedules that's great for one-off stuff for quick data entry but that is not how i build 100 repeatable workflows from project to project what i want to do instead is i want to go find my categories i'm going to look for all the different categories here i'm looking for rooms that's my first one i'm going to grab rooms i'm going to pull this out it's going to take it a second to grab all of the rooms in my project doesn't take very long just a little bit and then it gives me a list of all of the available parameters that relate to my rooms and i care about grouping these by level that's how i'm going to be doing my code calculations is by level and you can certainly do this by smoke compartment if you have a parameter for that or department or you name it you choose the parameters you want to group this by in my case i'm going to group this by level personal choice i then want to get the area of these rooms the area per person ctc you can see i've got a few other parameters in here that probably came from other workflows in the past i just haven't cleaned up before but i'm using the one that's specifically called out of ctc and my occupancy parameter occupancy ctc i'm not using occupancy i'm using the ctc property here this is the integer based value or the number base value so i can have decimal points of people in a space if i really care to and that's how revit is going to store it you can certainly go through and include more values if you want to validate visually things like the room name and room number by all means go in there and do that if you really need that in my case for my workflow i don't need the data i don't need to see it all i need to know is the maths are happening correctly so i'm going to basically remove these two because i don't really want them in my list i want just the four major fields that i care about how i'm grouping it in my case by level the area the area per person what i'm doing the maths on and my occupancy parameter where i'm dumping the result of my maths okay now with this workflow i'm going to check this calculated option in front of occupancy you see the reason why i'm going to do this is because if i go here and i put in equals say one plus one right basic math it'll do a calculation and i can choose to manually autofill this down but every single time i run this workflow i'm going to have a different number of rooms because i'm going to be using this on different projects so when you check the calculate option here in front of a field it's because you are intentionally putting in a formula that you want to be repeated on every element that spreadsheet link has found so when i check this box it's going to actually take and auto fill this all the way down to the very last entry of whatever is in this list and then it'll stop so it's all done now one plus one is not really what i want to do i want to do equals this cell this is just like using any spreadsheet program right this cell divided by this cell and then when i hit enter it does that math all the way down autofills in this case you're seeing i'm getting sort of an error pound value is what excel will do or a lot of spreadsheet programs will do when it can't understand how to interpret the math you see over here when i've exported this data from revit it says 430 space sf see i'm using an imperial model which has inches and feet and so in this case square feet is my area measurement value and it's coming out of square feet if i was using a metric project it might be m squared m you know uh superscript 2 or something like that this isn't really very conducive to math well i could in my formula get more complicated and do things like trimming the right you know do like a right three character trim um whatever and then do the math based on that but that gets complicated and it also isn't very sustainable if i was to say do something with a length value especially in imperial measurements where it's feet and inches right try and trim off the feet space dash space values here fractional inches and get a decimal value back it's not very convenient at all what is convenient is simply checking this height unit symbol option here at the bottom of your available parameters what this will do is for every parameter that you've grabbed that has a unit of measure this program spreadsheet link will take the unit of measure strip it off the end and put it up here at the top so you can know what it is so when i check this option it's going to give me back just the numerical value here with square feet up at the top and over here for the decimal for the feet and inches it gives me back decimal feet far more convenient for the math that we want to run now i don't need perimeter so i'm going to strip that away i'm just going to stick with my occupancy stuff here and now i can do whatever i want equals this divide by this and i get a result right and it runs that all the way down except for in cases where i have a non-occupiable space one where i haven't assigned an occupancy category it doesn't know how many people how many square foot required per person so in this case it doesn't know what to do and so we have to build in some extra logic here this is where a little bit of excel knowledge comes into play and by the way if you wanted to you could certainly save this spreadsheet out to excel or whatever your native spreadsheet editing program is and you can leverage that and it's heads-up displays to be able to far more easily write your formulas i'm going to do this here in excel today because well i have a high comfort level with excel and i also remember all my formulas i'm just going to do it live right here i'm going to put in here if error the iferror function in excel lets you pass something to it and then if there is an error like that pound value that was in there i'm going to tell it what to put in place of that pound value i'm going to say put nothing put quote quote which means a null value an empty value and when i change this now nothing really changes up in the top half but if i pull this down here you'll notice that where there originally was those pound values in non-occupiable spaces well now it's empty it puts nothing there and when i push this back into revit it will put nothing back into revit as opposed to putting zeroes back into revit because i want rooms that are non-occupiable to have nothing in their occupancy pro occupancy ctc value so at this point i could i could be done right with this i could do my occupancy calculations and just apply this back to revit but really i don't want to have you know decimal points of people in a space what i really want is i want this to be rounded to the nearest whole person and so inside this function here where i'm doing the maps i'm going to tell it to actually go ahead and round this some way either round using native rounding or round up or round down but i'm going to control exactly how this works for this little example i'm going to do just a basic round up always rounding up to the nearest whole person okay so i'm going to do round up i'm going to put in the same maps as before this divided by this i wouldn't have had to have deleted that but i did and then in the roundup function you have to tell it how many decimal places you want and i'm going to put 0 decimal places so if there's an error of doing this roundup function if it can't do the math it can't round up it can't do anything it's going to put nothing but when it does the math it's going to do roundup d4 divided by e4 to zero decimal places and when i hit enter i did something wrong what did i do wrong did i spell something wrong goodness sakes there's always something right uh round oh i didn't spell round correctly this is part of why it's really convenient to sometimes do this in excel and you don't misspell something right it didn't know what that route up function was so it yelled at me in this case now i've got the roundup function d4 divided by e4 and it actually auto increments this it doesn't standard autofill all the way down here in this case where it can't do the math it does nothing but it does my calculation right you can make this math thing as detailed as you'd like it to be you can do an if statement inside of here if um you know the round down of this or if the math of this is less than one then round up to the nearest one or make it one otherwise rounds down to the nearest person i have an example that i'll run for you in a minute here that does exactly that right but um that is effectively the beginning of how we do this basic workflow okay so at this point if i really wanted this to be applied back to my model i would simply do apply active worksheet and this dumps back into my model right it'll write to every one of these rooms by their element id the data value here and it's done but i wanted to build up a little bit more of a complicated workflow than just this because i want to take and total up all the different levels over here and and figure out what my total occupancy is on level one and level four and basement level and a couple of other things and i also want to put that into a table now coincidentally i already have a table that's pre-built in fact let me start this here save save my settings i'm just going to save this out real quick we'll just put this on my desktop for now so i can recall this in a minute i'm going to show you how you can build a really involved workflow here i'm going to save this out as my room calculation actually let's call this out as plumbing plumbing calculations plumbing fixture calculations i spell it right i think i did nope i didn't calculations there we go all right so i'm saving this in my case to my desktop yes you can save this to a project directory or a network location you choose where you want to put this but this sl settings file is what i'm going to use for the the main part of this workflow here so i'm going to save this out okay that's been saved now i'm going to basically reset this entire thing here let's close this out i'm going to open up spreadsheet link again and i want to open up a spreadsheet that already pre-exists this is an excel file in my case i'm gonna i'm just gonna go over here and do open open this file i happen to have this saved on my c drive in my little demo folder because i do this kind of thing quite frequently here and here is my original file basically okay it's got different methods of calculations for different counties or regions or states or you name it that i might work in it has these things all laid out here so water closet method number one the one that i'm gonna fill out today in front of you for just water closets i have the different levels of my building this is stacked up to five levels and i could add as many as i really wanted it takes the level and i want to total up how many people exist on that level now this in my normal workflow would be something that i would go into revit and i would have it total it up for me and i'd come back here manually and type in oh there's there's 75 people in the basement there there are uh 200 people on level one and then as i type that in you can see it's giving me division over here it's basically doing a roundup function of this divided by two rounded up to the nearest whole person because i can't have half a person on for calculations and then it takes whatever the maths are for this and it in this case is ratio of one water closet for every 500 people it does the the rounding up of this divided by 500 and it gives me a value this is a pre-existing spreadsheet that i've had in my you know portfolio of stuff for a long time but i want to integrate this with a real revit workflow where i don't have to manually type this number in where it does it automatically just i run a process i get my occupancies dump it into my rooms and this fills itself out in a second how do i do that well let's go back and let's just load those settings that we just saved i just did all the calculations for rooms i told it what fields i wanted i told it how to work let's load those settings from a file so that's out here on my desktop okay here's my desktop there's my plumbing fixture calculations that i just saved today and it's going to add to this already pre-existing excel workbook my rooms calculations here now this workbook doesn't know how to how to leverage that yet but it does exist right i've got method one calculation over here i've got method two calculation for other things i've got method three where i've got all these different things but now i've got a rooms tab and that's that's fantastic right now because i also wanna know the exact level names of my building and i kind of want to have them stacked in a proper order here i don't want it to be alphabetical because alphabetical doesn't always work out in my favor right we know that you know basement level 1 level 2 level 10 whatever roof level they might not always just alphabetically sort super clean so what i need to do is i need to figure out what their elevation is and sort by that so i'm going to add to this workflow another tab i'm going to tell it to pull my levels in as well this will let me get the exact level names and it will let me get their elevation so i can sort by that so i'm going to go grab levels there's a new tab in here now i'm pulling rooms and i'm pulling levels at the same time into a pre-existing excel workbook all at once now with my levels i want to know what the name is okay let's go find name there it is name okay so it tells me level four one two three roof basement parapet right this sorted alphabetically might be great it might be squirrely whatever also the parapet level isn't a real level that is it's just a building it's just a reference level so there's a property here called building story and if i leverage this in revit where i can mark something as yes it's actually a building level versus just a reference level not a building story i can use that and i'm also going to care about what its elevation is okay there's the elevation now once again this here little tricky to sort because excel is going to interpret this as text and so i'm going to hide the unit symbols again so that i get the true integer values and now i can go here to my filtering and i can tell it to filter by anything where building story is actually true and you'll see parapit went away now it doesn't exist because it's not a building story and i'm also going to go here to my sorting and i'm going to tell this to sort by the elevation so now it goes basement level one two three four roof right in the order in which they are in the building physically well dimensionally here's a cool trick i don't have to include building story to actually filter by it the way that this works i could choose to remove building story from the actual list there it's okay my filtering unlike a revit schedule i can still filter by building story i can filter by every parameter that exists because spreadsheet link reads all of it whether i include it in my actual data export or not so here i now have the list of level names i have their exact order set up here correctly and now on my water closet calculations over here let's use uh method number one i want this level name to directly pull its name from my list of available levels let me pull this a little bit wider yet so i can see that tab over there so i can go into here and i can do what's called either a vlookup or an index or a lot of different things i'm going to use the index command i'm going to index an entire list of things okay i'm going to index the entire levels area over here i'm just going to grab a bunch of them i don't really care you can go 100 levels if you want to if you commonly do 100 you can have this thing pull from the full list so i'm going to index that full set of things and i'm going to look at index number one here and i believe this should yep look at that basement level gets pulled in right so it automatically goes the first thing in that list is the basement level now i'm going to do a little bit of manual work here because i'm going to copy and paste this here and say index my levels for the second level in the list i don't care what the name is just pull the second name i'm gonna come down here do the exact same thing paste use the third thing in the list so level one two uh paste i think i did 3 4 there we go and then 5 here and you could do this as many times as you needed to i'm doing this in a very manual way there's of course more automated ways of doing this my roof level i'm going to intentionally exclude because i'm probably not going to be putting plumbing fixtures on my roof level so i'm just going to exclude that one okay but now i know what the exact level names are coming out of my project exactly what they are now let's figure out exactly how many people exist on that level okay so i know over here the room's level and i know what the occupancy is so what i'm going to have to do is run a some kind of a sum i want to sum up all the things but i only want to sum these things up if the level name matches whatever level i'm looking at right so let me just reference this real quick to make sure i'm doing the right maths here sum if look up something get a cell value and then what am i summing okay so the way that this works is we go equals sum if that's a command in excel sumif and i'm going to tell it um what range to look at for the matching value so i'm going to say go over here to levels and sorry go here to rooms and and pull the entire level column here rooms c so some if the value that's found in the c column matches the value that is found here on this tab and what i wanted to do the summing from is the f column okay so what what this will do is it'll say hey if the the level is basement level then go over and grab the occupancy value and sum that all together in this case i get 76 right so i can copy this in fact i can probably just cheat and go copy paste oh but it's going to screw my formatting up all right i'm going to undo that i'm going to go back and just copy the value from this oops undo copy and i'm going to paste into here and i'm going to tell it to reference a different cell instead of a3 i'm going to reference a5 just because that's that's this cell right here level one so a5 right and so now it does a new total there and i can do the same thing here paste this in and reference a seven okay delete seven okay there we go 230 people on this level you know i'm getting all the maths over here because this is just taking the resulting value of this and it's giving me divide by two uh for an equal number of men and women and then it's doing the maths based on that i've already written this because this is already in my pre-existing uh layout okay paste this inside of here and i'm going to reference another cell what is this nine and this one paste is going to be uh 10 delete uh nope 11 sorry 11. there we go so i get all my calculations and you can repeat this for as many things as you want you do this this step once okay now i'm going to save this spreadsheet out since i've already done all the maths i i don't need to um have end users do this over and over again i just have to do this once and i'm going to save this spreadsheet someplace i'm going to save this here back to my desktop let's go desktop where's it at there's desktop i was right there look at that save my spreadsheet out as plumbing fixed uh template plumbing fixed template as an xlx file as i save that out i can tell if i want to open it or not yeah let's go ahead and open this up i'm going to say yes to this and this is going to open up in excel real excel because that's my native spreadsheet editing program here i'm going to do one special thing here because i want this to be a truly repeatable workflow i'm going to go to the rooms and i'm going to delete all of the data that exists on rooms i'm also probably going to delete all the data that exists on levels i'm going to keep the tabs i need the tabs that these these other excel tabs know how to reference them but i don't want any actual starting data here otherwise it might be distracting it might show me some information that i'm not expecting it won't let me visually validate that i'm pulling my active project's information so to do that you notice there's a little lock symbol here that's excel telling me this is a protected tab so i have to go here to review and unprotect the sheet this is a training class i'm going to tell you something really super secret here i'm going to tell you the password to unlock anything about spreadsheet links exports here's the deal if you start changing data that spreadsheet link can't handle it's going to cause problems and it's not going to work for you so be careful of what exactly you change like don't change anything here in column a and b don't change manually things on the header up here in in rows one two and three so a and b one two and three leave them alone don't change anything there if you're planning on bringing data back into revit otherwise it'll break stuff the password here is super secret okay it's all lower and it's the word password type that in this is now unlocked so now what i can do is i can take the entirety here of the data and i can select everything come on now shift key why can't i select that let's zoom in a little zoom in a little bit here we go i don't know why let me select that shift key select there we go and i'm going to right click and i'm going to clear the contents of those cells and i'm also going to go here to the home tab and i'm going to take my background in fills and make it no fill so it's empty i'm going to take my borders just to be all kinds of pretty because i'm like that and i'm going to say no borders so this way this starts out with just the headers up here and that's it that's all it's going to have now if you want to get real fancy you could do something like uh format painter for this and format painter for this you get like an empty first row like it's not super needed this is just me wanting things to look kind of cute but that's i mean it's it's totally unnecessary right it's not needed at all but you can save this this way right save this file this is my template this is what i'm going to make my workflow use in a minute here now i'm going to go over my levels and i could do the exact same thing right i could i could go here to review i could unprotect the sheet password entered and i can take all this data here and i can right click and i can clear the contents and i can also do the uh background of no fill and the borders of well no borders okay i'm done let's just leave it like that for now and i'm going to save this i'm going to leave this on the rooms tab when i save it though because i like this to be sitting out here on rooms this is what most people are going to be the most interested in okay so there's my template this is the file that i'm going to open the next time i do this process inside of spreadsheet link i'm going to open that up directly and i'm also going to tell this thing to automatically open so you saw me manually open this before and get this older file that didn't have the rooms tab and the levels tab but this time i'm going to make spreadsheet link as a part of its saved settings i'm going to make it open that for me i'm going to show you exactly how to do that you go here to save settings you load your save you use save settings to do an advanced save okay advanced save here allows you to pre-pick the template worksheet you want this to open before it even goes and grabs rooms and levels before it does either of those things so i'm going to browse here on my desktop to that uh plumbing fixtures template where did i save that plumbing fixture template there it is right there okay and with this i'm going to save my settings out to a file in fact i'm going to overwrite that same plumbing fixture calculations file that i have out here on my desktop already these are the settings that we originally saved for rooms that knew about my calculation right that's that's that was so i'm going to save this replace that file settings saved successfully let's click ok here and now let's see what happens if i shut this down i'm not going to do anything i'm just going to shut this down and let's open that back up so let's pan this down here so we can actually see some of the rooms spreadsheet link let's collapse this up a little ways here so we can see some of what's going on back in the background load my settings from a file and let's go grab that plumbing fixture calculations now remember this file here first step is to open that spreadsheet so let's open this up and see what it does spreadsheet is already open look at that and it already has my empty rooms tab now what it's doing is it's saying hey i'm supposed to grab rooms but you've already got a rooms tab i do in fact that's the rooms tab that i was using as the source for my data it wants to know if i want to overwrite it yes yes i do want to overwrite it so when i do that it grabs all my rooms and it'll do all the math for me as well it also says that i've got a worksheet out here for levels which of course i do because i'm looking up data off of that as well for these tabs so i'm going to overwrite the levels tab as well and when it's all done up here on the rooms tab you'll notice that it has that occupancy checked it has the height unit symbol checked it knows the exact order of the fields it does all the math it's all ready to rock it does all the work for me in this workflow when i load that setting up as an end user it now opens the spreadsheet that i know is working it does the maps it goes over here it's already looking at my data it already does all those calcs all i have to do if i want these occupancies pushed back into my project is to apply the active worksheet to my project and it dumps that data right in here so it's updated 96 of my rooms right it's all done you saw the workflow now open up the settings this is what the end users do open up the settings apply the the rooms tab they're done they're done now if i wanted to now take the graphics of this and bring that back into my project i would just want to save this spreadsheet someplace now i'm not going to save over that template because that's my template right i'm going to save this out as project x p fixed calcs or something whatever you want to call it right and i can save this to my project directory or wherever is appropriate so i can have this plumbing fixture calc thing ready to go so i'm going to save that save that in my case to my desktop here save that out wants to know if i want to open that file in this case no i don't need to all i needed to know is that it saved out my excel file so i'm not going to open it and i'm going to shut down spreadsheet link because basically at this point i'm done right i i i've done my room calcs my occupancy ctc value here is filled out for every room that's occupiable based on the maps that i told the excel file to use and i've now saved that spreadsheet but this is the final step of that workflow i want to bring that spreadsheet back into revit because those different methods of calculation well i want that formatted layout that i have in excel to appear in revit the same way see now natively if this was a normal workflow i'd be taking that excel file and printing it to an image and then i would import the image or i might take that excel file and link it into a dwg as an ole embedded object and then i would link the dwg into my revit model the problem is every time there's an update to that excel file i have to manually print or manually open up the dwg and save it before i can update into revit and that's tedium that's that's not the most efficient way to work so what i want to do instead is i want to use this tool schedule excel i'm going to scroll down here so you can see where this is going to go i'm looking at my my revit schedules and quantities right here and i want to have this act like a revit schedule obviously it can't be a real revit schedule because revit doesn't know how to format the way that i have that table formatted with the grade rows and the the groupings of level with the you know how i did all the stuff revit can't do that normally but with schedule excel i can do that so i'm going to add a reference to that excel file i'm going to open an excel file that i've saved out let's see here there's that that project x plumbing fixture calcs right there and i'm going to open that up this is going to give me all the tabs that are in that file now some of these tabs you notice i didn't go and fill out every single tab you don't want to watch me do that in a little training like this so i didn't fill out all these tabs they don't all know what they're supposed to do just the first tab in this example knows what it's supposed to do but i want to bring this tab right into revit directly right i want the basement level with the occupancy at some number i want this whole thing here the little notch cut out of the corner i want the background cell and fills level by level right i want this to all come in how do i do that i'm going to bring this active worksheet directly into my model it's going to name it based on the tab name wc water closet method 1. i'm going to bring this in as a revit schedule view because of my graphics options here it is going to prefix it with the link space dash space that way i can have all these excel imported things these spreadsheet link or schedule excel imported things grouped all together down here at the bottom of my list with z as its prefix you could choose a suffix you could do whatever you needed there's all kinds of fun things you can do here but what i'm trying to do is exactly preserve what i've done in excel preserve the cell heights preserve the cell widths if this happens to be longer than 20 inches tall it's going to break into multiple tables this is great for things like abbreviations legends and things like that um it's going to apply font colors and background cell colors and apply conditional formatting if you're doing conditional formatting on things this this tool set can do all of that i'm not going to overwrite any cell borders i'm just going to bring in all the cells like they're supposed to be brought in so i'm going to click ok here this is going to bring in just the one it's only going to bring in one because that's all i've told it to bring in was the active spreadsheet so you can see here this has given me a sort of revit schedule and basically what this is guys is a heavily manipulated header in revit a scheduled title that's manipulated with no data showing up but what that does for me oh and by the way this knows what the path is to that original file and it knows to auto update it so that when i open up my project just like normal revit links and dwg links update as you open this can auto update as well in this case it's already up to date something to worry about it but if i ran this workflow again that file got updated it would actually help me update that or i could force it to update before i print but hey look i've got all this stuff here i want to now bring that into a sheet like let's go to let's go to my title sheet this will be a good example i've got a normal revit sheet list out here and now i'm going to take that water closet calc and throw this on the sheet as well notice how it lines up my header just like normal revit schedules would and if i zoom in closer you'll notice that i have all the different line weights that came directly from excel heavy outside lines medium thin right i've got the font size i've got background cell and fills this is exactly formatting like i had it in the excel file but now it's in revit and it's told to auto update so what happens if i wanted to say have all the rest of the things in here right i only brought in the one part of this i want to bring in the other parts well the workflow that we developed today i didn't fill out the other tabs that relate to this as well so i can't really do that with our template from today but what i can do let me remove this guy here i'm going to delete that schedule i'm going to run this workflow one more time beginning to end not explain all the things that are happening i'm just going to show you the speed at which this can happen in fact let me undo all those element attributes that i've modified back to the beginning back before we did anything today so my rooms have no occupancy on them i've done undone all of it but i'm going to run this workflow as i have it saved okay with my full pre-existing you know demo set here i'm going to load my settings from a file i'm going to browse to where this exists in my case it's on my c drive assuming i can scroll to that so i've got a spreadsheet link and in my case it's in my room calcs folder here i'm going to open this up you'll see everything happened exactly the same as it did for us before right it's going to grab my rooms it's going to overwrite my rooms it's going to overwrite my levels it's got my water closet calcs now all filled out my urinal calcs labs drinking fountains all the things all filled out because that's what it's supposed to do i'm simply going to apply the active worksheet to my project so 26 people here 96 rooms updated you can see it's exactly the same thing that we did in our beginning of this here and i'm going to save this file out let's just go back to my desktop and for the fun of it let's do project x plumbing fixture calculations and because i want to show the difference here i'm going to do 2 as a suffix i'm not going to open it i'm just going to shut this down now i'm going to bring in those calculations here into my schedules list just like before same exact workflow right add a reference to plumbing fix project x plumbing fixture calculations in this case number two open that up this time instead of just bringing in a single worksheet at a time i'm going to bring in as many as i want in fact i'm going to grab everything all of these tabs i wouldn't normally do this i'd probably only bring in the ones that i know i need for my project i'm going to bring in everything for you all things as a revit schedule view and it's going to bring in tab by tab each individual one as a revit schedule water closet method calculation one method two method three now it's gonna do urinals now it's gonna do probably the labs is my guess yeah lab method one two three every one of those tabs that i've told to bring in it brings those graphics in directly now i often get the question asked of me why don't you just put it all on one tab and that's a great question the reason why i've chosen not to do that is because you know when you work in different counties or states or provinces or cities or whatever they might have different calculations different governing authorities want you to follow different rule sets in some cases it might be like water closet method three urinal one lab three drinking fountain one right and so now i've built this so that i can mix and match depending on whatever province or rules that i'm trying to follow i don't have to have one big long one and then have to have you know every possible different iteration of those i just have the individual calculations running and then i can stack them together you might choose to do this differently if you always follow the same rule set fantastic go for it you build yours up the way that you want it in my case i've chosen to build it up as 10 individual tabs that i can stack up however i see fit so what is the final result of this whole workflow let's put this back out here again water closet method three lav one uh sorry urinal one lav three and uh drinking fountain one i'm just mixing and matching these guys all up here you can see how i've built my excel spreadsheet this looks like one continuous entire schedule even though it's four individual tabs from excel being brought in but i can bring them in and drop them next to each other and very simply run an update on this just by re-running my workflow right super super simple so you've seen today in this example how you can go from an existing excel spreadsheet and by using the spreadsheet link and the schedule excel workflow together how in a matter of minutes you can get this stuff brought in and by the way your update process is just as fast in fact it's probably even faster let's just say we take this dorm room here and we change this from or this room here 26 people we change it to be something like a dormitory right 50 square foot instead of 20. well my maths have to change on this now and i would have fewer people in here if this was a dormitory it's a horrible dormitory but let's just use it as an example in fact let's use this one as well because why not so if we go here and swap this out for being a dormitory as well that number needs to change here as well 505 divided by 50 rounded up or down in the nearest person would be either 10 or 11 people in this space instead of 25. so let's run that workflow let's see what happens here spreadsheet link load my settings from a file i'm just going to go find where those settings are located which for me is on my c drive again that's where i've put these go grab my settings it's the same calcs every single time it's going to grab my rooms it's going to grab my levels i'm simply overwriting the rooms and levels i'm applying back to my project the active worksheet if you watch this number right here we'll see this update to 10 in this case apply active worksheet so i have 16 fewer people here and probably 20 15 fewer people on the other one i've only updated two rooms in this case but they're both on level one we just save over the top of that spreadsheet desktop uh this one right here that i just saved out overwrite that no i don't want to open it and i'm done from an end user point of view i'm effectively done to update my sheet over here now these numbers here on level 1 need to change drastically so i'm going to go to schedule excel and you'll notice that this file is now out of date so let's just do an update all and we'll see what happens this is going to go through the process now this is where i probably wouldn't have linked all 10 it's unnecessary to have all 10. i only need the four that i'm actually using but this is gonna go through and refresh this and instead of 122 people my level one should have fewer people and then my number of men and women will be fewer and my calculations here will update probably ingly as well done 91 people 46 men 46 women two water closets instead of three water closets and i'm ready to print all told if i hadn't been talking i probably could've done this in 60 seconds less but i mean really that's the 60 seconds of workflow easy easy it's cake once you have it all set up and your users can follow that very simply all right let's jump back into the powerpoint here i wanted to give you some information here about how if you wanted to you could obtain this suite of tools how do you get it well this is how you're going to get it you're going to go to ctcsoftware.com you can try this exact workflow out for yourself completely for free for 14 days if you don't have it already if you do have it certainly just use your license but you go on ctc's website to the bim project suite 2021 you may have noticed today that i was running this in 2018 this automatically installs on top of and runs in the last five versions of revit 17 through 21. so one installation installs in all of them you can try it free yourself if you already have it you know you've just seen today how you can use this in this fashion but you can get this this suite of tools and and make your life a whole lot easier it on your revit environment with revit closed and then you can do all the things i want to thank you for the time that you've given us today and you know certainly sign up for our webinars as you have today come back for more i'm going to be doing a lot more of these instructional type webinars so if you've got ideas that you want me to cover let me know and i'm all about talking about those things [Music]