Thursday, July 26, 2012

SPREADSHEET POWER

So what did I get out of going to RTC in  Atlanta? One of my favourite sessions was Michelle Leonard from Auckland NZ showing off the power of linking Excel & Revit together. Most of the credit for this post belongs to her.  Thanks Michelle.  I was inspired enough to go out and buy myself a copy of Revit Excel Link, a bargain at $95. 



My first experiment is basedon a rectangular array of curtain panels.  It was meant to be 10x10, but by mistake came out as 11x11.  No matter.  I edited the curtain panel family to make "Thickness" and instance parameter.  Now it's easy to select individual panels, or whole rows, and change their thickness.  X & Y parameters will make it easier to locate the panels once exported to excel.  They are all set up as shared parameters so they can be scheduled (and exported to Excel.



Within excel, the parameters come through in vertical rows.  It will be easier if we can see them in a rectangular grid that matches the curtain wall.  I did this by creating another worksheet called "Source", and linking the cells to the "Thickness" column based on their X-Y values.  A1 to A1, B1 to B1, etc.  You can use your excel skills to drag down the little black square at the bottom corner of a cell.  This will propagate A1 down to A11 and give you the relative links you need.



Apply conditional formatting to colour cells so that you get instant feedback on the range of values.  My first trial uses a simple formula that simply adds 50mm to the cell on the left. 



Now import this back into Revit and the curtain wall updates to match.  So it works.  Now for something more interesting.



I learned from Michelle that Excell has a function called RANDBETWEEN.  A little more Excel trickery and the entire array is populated with Random numbers between upper & lower values specified in two cells to the right of the array.  In this case, between 100mm & 3 metres. 



Import back into Revit & you get a nice little message saying all 121 panels have been updated successfully.



Recalculate to get another random array.



Change the values to create an effect that is more subtle or more extreme.  This is fun.



Next I set up a series of vertical stripes with different upper and lower values.  Again these are controlled by cells to the right.



Now I can try out all kinds of permutations and combinations. I can also make the curtain wall into a group and copy it on either side to simulate a building facade.




Then I can use the randomly generated pattern to suggest changes to the material parameter (also changed to be instance based)  I like this, a mixed approach: partly random, partly ordered (the vertical stripes) partly intuitive choices input manually.





The end result is not a real building, just an idea, an impression, a visual fantasy.  But you get the idea.  Link the power of Revit to the power of Excel, throw in a bit of judicious manual intervention ... lots of interesting possibilities.

15 comments:

  1. Wasn't that a great session? And I wasn't at all depressed by how little I really know about Excel.

    ReplyDelete
  2. Looks awesome Andy!!! I love the power of Excel utilized in Revit.

    ReplyDelete
  3. Andy/all: Right RTC session- wrong software product referenced in your first slide and post! Michelle used "Ideate BIMLink" in her session. Appreciate a correction! Regards, Craig H Dodge (Ideate)

    correct software at www.ideatebimlink.com

    Here was the class description:

    Interative Design with Ideate BIM Link
    Room: Summit
    Speaker: Michelle Leonard
    Category: Architecture
    Skill Level: Advanced
    Description:

    Take 4,420 sun shading blades across a northern façade arranged in 884 diamonds formed by steel cables, 3 sizes of blade, 2 orientations, 2 mirror isomers per blade, 4 possible colours and perforated or not… Which means 32 permutations per blade, which therefore meant 33,554,432 per diamond with no repeats…. And over a Googol (that’s 10 to the power of 100 or 1 with 100 zeros) possible combinations across the screen with no repeats…Phew! So how do you start to input all of this information then test different iterations of randomness across the façade? BIM LINK! For this project we BIM Link to push randomised data from Excel into Revit to change each individual instance of blade to change rotation, placement and material. This enabled us to test different iterations of ‘randomness’ across the screen to get the desired effect, without manually having to input data into the model saving a huge amount of data entry by using this effective plug in.
    Key Learning Objectives
    1. Learn how BIM Link can be used to update information from Excel into Revit, to save
    hundreds of man hours of updating information
    2. Find ways to push and pull smart geometry around quickly through BIM Link
    3. By utilising a program like BIM Link can enable some of your staff that arent familiar with
    Revit to update information like Door Shedules etc...

    ReplyDelete
    Replies
    1. Hi Craig

      No offence intended. Yes, Michelle did use Ideate BIM link. She also mentioned that other products are available to do a similar job. I used the one that I could afford at the time.

      I normally remove any comments that set out to advertise a particular product. In your case I will make an exception out of respect for the good work that Ideate does.

      I wish you well.

      Delete
  4. Michelle LeonardJuly 30, 2012 at 6:28 AM

    Hi All,
    As Craig mentioned above, yes I did use Ideate’s BIMLink for my session that I presented at RTC in Atlanta and Australia. There are a couple of products that can carry out these kinds of tasks but I prefer BIMLink.
    Kind regards
    Michelle Leonard
    http://nz.linkedin.com/in/leonardmichelle

    ReplyDelete
  5. Thankyou Michelle

    I'm sure you realised that my post was not intended to be a description of your presentation. I acknowledged my debt to you and went on to describe my own explorations.

    Thanks again for the inspiration & I hope to see you in Auckland next year :-)

    ReplyDelete
  6. This interaction between Revit and Excel seems like it can be very helpful, I myself just recently created a rough rotating curtain wall system and had to input each value manually (that was a huge pain). Here is some info about it if your curious
    http://revitdowntothedetails.blogspot.com/

    Looks like I will have to take another crack at it now that I know about Revit Excel Link

    ReplyDelete
  7. Hi Andy,
    could you elaborate on how you gave the panels X and Y values and how you sorted them accordingly in Excel?

    ReplyDelete
  8. Hi Andy, when i tried to replicate what you have done in this post, my "thickness" parameter that appears in Revit Excel Link is Read-Only(Orange) instead of Editable(White). What did you do to the parameter to cause it to be editable?

    ReplyDelete
  9. Hi Adriel

    Not sure what is causing this problem for you. I just created new instance parameters. I made mine shared parameters, but I don't think that's necessary for REL to work. Haven't come across the Orange phenomenon.

    I have created a new post describing my process is more detail. Perhaps this will help. If not you may need to talk to the vendors CTC.

    ReplyDelete
    Replies
    1. http://www.youtube.com/watch?v=_iaiIV9XCpM
      here is the link just in case.
      i notice you are using 2012 version of REL and i downloaded 2013 version.
      Will try the 2012 version with Revit 2012 and let you know.
      Cheers.

      Delete
  10. 95 US ??
    I am getting another price on there HP, 850 US.
    Am I getting something wrong ?

    Thanks for the blog post, great stuff.

    //D

    ReplyDelete
    Replies
    1. Try going to cadtechnologycenter.com/store and clicking on Revit Excel Link. Much cheaper that BIMlink and far more powerful.

      Delete

I've been getting a lot of spam so had to tighten up comments permissions. Sorry for any inconvenience. I do like to hear from real people