I have a database which happens to be for cemetery plots. I need to print a report that graphically represents the cemetery and colors each plot based on its status (vacant or used) and, if used, who is there.
Currently, I can do everything I need with a combination of Access and Excel, but it's a tedious process. I export the data in table form from Access and import into Excel. Then I manually associate the "status" on each line of the imported sheet with the color-coding of a cell representing a plot on another sheet. Then I run a script to update the color coding. After that runs, I need to match the contents of another cell (the "owner") and link that to the associated color-coded cell on the other worksheet. All in all, it's a very tedious process and subject to errors. Adding plots is a problem because the manual matching process is required for the color coding and cell text to be updated properly.
My question is this... is there some way I can set up a report that exports from Access directly with the proper color coding and text? If I could color-code an exported spreadsheet, I'll figure out a way for it to output in the proper layout. (probably by exporting blank cells)
I know that I can generate a report with conditional formatting, and that gets me part of the way there. The problem is that I ultimately need this in Excel so that someone can click on the cell to see the details. Unfortunately, when I export the report, the conditional formatting isn't retained... everything is black on a white background.
Or, is there something I can do differently to make the importation and processing in Excel more automated?
- Dave