Excel tables need to be exported to PDF. Microsoft Excel is not designed to work across to Adobe applications like it does in Office, nor does InDesign to Microsoft. How it works also depends on the version.
You will need to re-export the worksheet to PDF every time you make a change in Excel. When you open the InDesign document linked to that file, InDesign will ask to relink to the original document; from there you direct InDesign to where the update is saved (or if you have the doc open while you make updates, in the Links pallete in InDesign, a hazard exclamation point will appear - right click to rectify).
As for the transparency settings in InDesign associating with the data, the data would exist in PDFs so there really is no "dynamic" linking. If the cells in the tables you work with in Excel have "no fill," then exported to PDF, they would be transparent.
My solution would be to take advantage of the layering capabilities in InDesign and its Object Styles function. In a nutshell, have the Excel PDFs on its own independent "top" layer and create shapes that are sized and positioned accordingly to the cell you want to change the opacity to (or any other object style characteristics) - those shapes should be on its own independent layer just below the layer with the PDF. Such layering makes it easier to work with the shapes. If you have created an Object Style before, associate the shapes with the desired look you are going for; when you make changes to the Object Style later, the changes will take globally within the document. If you haven't but have worked with Paragraph Styles, it is basically the same principle. I personally exploit the Object Styles function for complex transparency, shadowing and blending formatting in my work and cannot live without it.
There is a mail/data merge function in InDesign in which you merge InDesign to data in a comma-delimited (.csv) or tab-delimited (.txt) file (which you can create in Excel). I have used it successfully for certain circumstances. However it acts rather wonky in InDesign's tables, so I would reserve that InDesign function for another time.
I hope this helps.
m.a
