Export Report with Conditional Formating???

[Anonymous]

Is there a way to export a report (schedule) with the conditional formatting? I have formatting that is turning a cell black when that parameter is not being used in the project. When these schedules are exported (into excel) the conditional formatting does not follow. So, the end user receiving an excel file for this schedule does not know that the cells that should be blacked out. Are not active in the project. Any ideas or workarounds would be awesome.

Thanks.

[m.voss.alvine]

what are you using to drive the conditional formatting?  Could you use text that says "not used" and then apply the conditional formatting to find the "not used" and then fill the cell?

[Anonymous]

There are yes/no parameters driving the conditional formatting. This is the schedule with all parameters unhidden:

Basically, if a rod is showing (Yes), the point number cell needs to be available. If the rod is not showing (No), the point number cell needs to be unavailable.

[rudi.roux]

Hi @Anonymous, 

All the best for 2017! 

Unfortunately, the Revit Schedule Reports can't be exported with Color Conditional Formatting due to the nature of the .txt file which is imported in Excel. 

But, I have a Solution (workaround) for you. I attached two files for you which you can use as reference. (Revit & Excel File). 

Quick indications of end results in Revit and Excel shown below: (I just applied the examples to Mechanical Equipment, but it can be done with anything). 

It is a 1min process to apply Color Conditional Formatting in Excel from imported Revit Schedules. 

The Solution: 

I created Yes/No Parameters in Revit to mimic your information where you need to see if there is a Rod/Hanger applied to "whatever". 

I then applied a Conditional Format to each of the Parameters where the Background should be black when no Rods/Hangers are available. 

You can then simply tick the checkboxes to show that a Rod/Hanger is either present or not (Yes/No). This will then leave you with the end result in Revit as indicated above. 

Then you simply export the schedule and import it into Excel. You'll then have actual Yes/No values in cells in Excel where you can then apply a Color Conditional Format. 

Select the Columns which you would like to "Edit" or apply an Excel Conditional Format to: 

In Excel >> Select Cells >> Conditional Formatting >> New Rule >> Format only cells that contain >> Cell Value >> equal to >> NO.

Click on Format and select a Background Color...

I hope this helps! 

[Anonymous]

Appreciate your help with this. Totally see what you are doing here. Makes total sense. But, the issue is the ability to let the end user know which point is available and which one is not. With the ability to type in the cells that are available. The "Point Number" parameter is not a yes/no parameter. Its a text parameter. Basically, we export these schedules to excel and number only the available "point numbers" in the case of the schedule below. Then dump this information back into Revit applying the added "point numbers" to the families/schedules that are effected. I can apply conditional formatting to the "point numbers" that do not show (as below). Which are being driven by a yes/no parameter. But, this indication of the "point numbers" not being available does not transfer to excel where we number them. So the end user does not know what "point numbers" they should actually be filling out and which ones they should NOT being filling out. I guess I could put together a training for our team to apply conditional formatting in excel. But, the boss is looking for all this to be "automated". Export schedule to excel, team applies number to necessary points, dump back into Revit, done. Not sure what my options really are though.

[rudi.roux · Accepted solution]

Hi @Anonymous / @Anonymous, 

Ok, I see what you're getting at. I then have another proposal/solution for you.

It still works on a similar principal as my previous post, but I made a few alterations where I added the Yes/No Text Parameters and where the "Point Numbers" are calculated parameters with conditional formatting assigned to them. This means that you will still be able to assign text values to families and export those values to Excel and when you import them again, it will work/update accordingly in Revit. The previous "Rod 1 Active" parameters are still available within the families, but not in use for the examples below. The NEW Yes/No Text parameters are Rod 1 - 4 ...

So, this is what it looks like now: 

Now, say you made changes in Revit and you need to export the schedule again and open the new information in Excel, you simply open the schedule in Notepad and re-paste it in Excel as shown below: 

The attached files are for reference. I setup the Excel file in so that it will format the around 45 rows for the columns present, but you'll be able to play around with it and setup your own sheet as required or use the attached one as a starting point. Excel Conditional Formatting can be seen and managed here: 

The Workflow within your Office to automate everything to a great extent: 

• Setup the Excel File once, according to your parameters in Revit. 
• I know you would not like to have the "Rod 1 Active" parameters available once exported to Excel, but they are important for the Conditional Formatting. 
• You can Protect the Excel Sheet/Cells and only you would be able to edit it, so the rest would be able to add the "Point Numbers" and it can then be imported into Revit, which you're aiming on doing. 
• You can do something like this below, where the grayed out cells are the ones to be Locked/Protected by you.

If I understood what you're trying to achieve, then there will be one step which you'll have to do which isn't automated... That is to copy and paste the info from Notepad to Excel and save the sheet for others to use, then you won't need to train the rest. 

I hope this helps! 

[Anonymous]

Thanks for putting so much time and effort into this. Appreciate it. This will get the job done in the end. I will have to test it out later. Got busy around here. I actually was working with Autodesks development team regarding this, the last few days. Since the "point numbers" are actually from the Point Layout Add-in. I worked with them to find a more streamlined way of accomplishing this. Without even using a schedule or exporting anything. We also found a bug in the software along the way. Which will be fixed for the next software update. So, that is a good thing. I will definitely keep the information you provided on hand for some potential other uses. Or to provide an option for employees without the Point Layout add-in to accomplish the same goal.

Thanks again. 

[rudi.roux]

Only a pleasure @Anonymous!