topic Can I Create a Data Link to a Filtered Excel Spreadsheet? in AutoCAD Forum

Data Link to a Filtered Excel Spreadsheet

Anonymous — Mon, 12 Oct 2020 14:33:08 GMT

My boss has compiled a huge spreadsheet. Some of the data will be used to make a bill of materials. He can create a custom filter within Excel that will show only the data I need, but can I create a data link to that filtered version? It won't be a specific range (such as A1-G12), more likely just rows and columns.

Re: Data Link to a Filtered Excel Spreadsheet

Anonymous — Mon, 12 Oct 2020 14:56:37 GMT

(Sorry for the duplicated post)

Can I Create a Data Link to a Filtered Excel Spreadsheet?

Anonymous — Mon, 12 Oct 2020 14:43:48 GMT

Re: Data Link to a Filtered Excel Spreadsheet

qnologi — Mon, 12 Oct 2020 14:48:24 GMT

This sounds interesting...

https://www.cadtutor.net/forum/topic/23531-hide-columnsrows-in-linked-excel-table/

Re: Can I Create a Data Link to a Filtered Excel Spreadsheet?

chase.gifford — Mon, 12 Oct 2020 15:23:03 GMT

@Anonymous unfortunately there's no way to bring in the spreadsheet filtered into ACAD.

What you CAN do is create a copy for bringing into ACAD.

When you receive the large filtered spreadsheet, copy the tab in Excel and add a column to the end. Put an "x" or something in the cells of that column. You can select the cells in your new column, type x and hit ctrl+enter.

Now all that filtered data is marked with an x.

Clear all the filters and sort that column to get all your data in one range, delete the rest and sort by tag number or whatever and bring into CAD.

This defeats the purpose of a DataLink but I still recommend importing as a link, importing as an ACAD entity will inherit the fonts from Excel and make formatting in ACAD a pain. I recommend putting a date on the tab you made and hiding it (right click the tab > Hide). That way it doesn't get confused for the real working spreadsheet. Maybe add a row to the top that says COPY FOR ACAD, DATE, TIME.

Re: Data Link to a Filtered Excel Spreadsheet

JTBWorld — Mon, 12 Oct 2020 20:54:42 GMT

I have not tried but maybe this can work, then link to the second file created that is filtered. https://www.exceltip.com/tips/dynamically-filter-data-from-one-worksheet-to-another-in-microsoft-excel-2010.html