LinkBack URL
About LinkBacks
Using XP/Excel 2010/Laurent Longre's Morefunc add-in.
This is a recurring issue, which while not insignificant for the immediate problem, is also going to be used as a project model for other reporting systems. So, the top-down issue is that I want to summarize reporting on a host of facilities whose data is housed by a third party provider. I have had intermittent issues with the Indirect.ext add-in, but it’s so useful I keep on turning back to it despite the difficulties - but if anyone can resolve why Column C is experiencing more frequent #REF! issues than Column D, I would be ever so grateful
I start with creating the summary sheets, each with a standard nomenclature of a three (3) letter facility code, i.e., "abc" - approximately 80, which would probably be easier to create with a macro to create a new workbook for each of them based off of a table or the like, but because of the way I assemble the reporting it’s not so big a deal to me so as to make that issue significant. Then I go outside of my network to a third party provider and I pull two (2) .pdf records per facility from that third party provider, use the provider's built-in conversion to export those .pdfs to .xls files that I save into a desktop folder as [3-letter facility code][1] and [2], respectively. From there, the formulas I built in are reading the filename, determining the files to read in the closed folder from that filename, and using the Indirect.ext function to pull that reporting data. Also, while I left everything unlocked and visible in the attached document, only incomplete reporting periods would be unlocked and all of the rows and columns in italics would be hidden, as would the hidden chart data, again to avoid confusion for end users who will simply be updating this page only with data entry.
The next issue is to modify the exiting macro to copy values only for the first 4 columns (first two for the "legend" and mapping, and last ones actual reported data) both because the Indirect.ext has experienced the instability issues, and to avoid the confusion that results when the end users review and enter subsequent reporting information in adjacent columns. While the macro functions to move values only data, I also need it to retain the remaining columns *including the formulas* on Sheet 1, together with the chart and hidden chart data for sheets 2 and 3. So far, my macro only copies the first four columns with partial formatting - the font and fill, but not the layout, row or column sizes - and leaves out the entire rest of the workbook. I know that there are merged rows but I need to keep those so that the overall report maintains its legibility and ease of use.
Finally, I appreciate all of the advice and reading other users' approaches and problems, from which I almost always learn something - a testament to which is having solved 3 of the issues I originally experienced in the course of analyzing my problem and posting here. But while I have reviewed most of the Microsoft offerings that are written in human language and not just dropping you into the Reference Library hell (can't tell you how many times I can use "hello" on my screen!) and the free offerings in the general Training / Books / Sites most recently compiled stanleydgromjr, I am still lost. Even when I find isolated information, what I would really like to understand is how the logic works - so rather than just using trial and error of multiple copied codes, I understand what and where Excel and the macro are doing in order for me to know where I need to reach and manipulate it to achieve my objective, and what operations can be employed at the various levels. I'm at a substantial disadvantage here because while most of my professional career has involved written logic and controls, I am much newer to the programming end of those skills. I kind of feel like I'm trying to drive a car with nothing but the radio and waiting to see how the vehicle responds to each action as I'm going down the road...
Register To Reply
Bookmarks