Automatic Format Table Size with Excel Data Links - With IF formula output ""

[Anonymous]

I am having trouble getting an AutoCAD table to size to the DISPLAYED data in Excel.  

I have named my Excel Range "AutoCADExport" for 9 rows.  If only 3 rows are filled then only 3 rows export, which is perfect.  The problem arises when I try to make the excel cells with equations that could provide blank values.  

The application for this is a "Bill of Materials" that can change size and parts.  

ex) Cell A1 contains -> =if(B1="","",3) 

------ (This populates the schedule number, 3, if the cell next to it has contents, otherwise it has no value.)--------

Currently AutoCAD reads the cell as having information in it, but I want to Data Link a table that doesn't need to be cleared by a VBA button.  

Can this be accomplished with 2 tables?  One table that does all the work collecting data with formulas, and an export to AutoCAD table that just reads the formulas from table 1?

Thanks,

Dylan

[john.vellek]

Hi @Anonymous,

I just found your post and thought I would inquire whether you had proceeded to test the two table method. If not, and you can provide some sample data I am happy to explore this issue.I tested this scenario on my AutoCAD 2019 by creating a spreadsheet with two sheets in it. I linked a cell from the first sheet to the second and then gave it a named range.  When I create a datalink in AutoCAD using the named range, a table works properly.  I would like to see if it works with more complicated formulas in the mix.

Please select the Accept as Solution button if my post solves your issue or answers your question.

[Anonymous]

Using dynamic named ranges in Excel, one can create a logic based table and a linked output table.  The second table is exclusively for export to AutoCAD and is populated with the first table by way of an "IF" excel statement.

ex) =IF(ISBLANK(B11)," ",B11)      

This reads cell B11 unless B11 is blank.  If B11 has nothing in it, the export table cell will display nothing but it will have a space in the cell. " "

Next I created the variable to define the height of the export table.  By using this function in the name manager.  

MyHeightVar=COUNTIF(Sheet4!$F$3:$F$35,"<> ")+2

It counts the number of non spaces in the export table, then adds 2 rows for the start of the table.

This may be a little sloppy but I wanted to see the bounds of my table.  So in I5 and J5 I put the first cell of the table $F$2 as text in the cell.  And the last cell is bounded vertically by the named range "MyHeightVar" and I know my last column is H or 8. So in cell J5 I have =ADDRESS(MyHeightVar,8)

The final step is to define the exported table with this equation.

ExportTable=INDIRECT(Sheet4!$I$5):INDIRECT(Sheet4!$J$5)

That defines the export table to be exactly what has data in it and not spaces. 

Then in AutoCAD create a DATALINK to ExportTable and your table will grow and shrink as data is added to the table.  You must be careful to only edit the first table which is read by the IF statements in the export table.