Create reports from Excel workbooks?

Thanks, shg. As a new member, I didn't want to start posting attachments until I was sure it was appropriate and that I was asking the question in the right place that.

I'll need to clean up one of my workbooks for this purpose but will work on that tonight and try to put the example up by tomorrow morning.

Regards,
Redwood

Sorry for the delay getting back to this...just too many deadlines and I had no choice but to get some product out the door manually. I still need to develop this tool for the long run though.

The spreadsheet I'm attaching is far from ideal because I sort of built it as I went along and it grew organically without any kind of roadmap, just a lot of prompting from a variety of bureaucrats with competing objectives. I started with the Calcs tab but created the Inputs tab to streamline data entry. I realize that's quite cumbersome and I need to dump it and instead create a UserForm tool for that task.

But the output report is very close to what I am shooting for. The majority of its values are extracted from the Calcs tab, with a handful of manual inputs and a few calculated values too, but in the next iteration I want all of that stuff to come from the Calcs page via the UserForm entry tool. The entire content of the Report tab should be automated.

In this example, the Report is all from the first row of data in the Calcs tab. Obviously the reason I want to automate is because I want to create an identical styled report for each individual row in the Calcs tab, for this workbook a total of ten reports.

I'm not looking for anybody to do this for me, just trying to locate a specific resource than I can use to learn to do it myself. Any help is much appreciated.

Thanks for the response, and apologies for the lack of clarity.

I haven't created a UserForm yet. I intend to, but have never done one and figure it's going to take some effort -- worthwhile, but for the time being and given the deadlines I'm under, I've already got the data I need in there. I created that Inputs tab in a vain attempt to make that process easier, and only then did I even discover I could create the UserForm, but at that point I just needed to get the thing working.

In any event, that's not what I'm asking about. My objective here is just taking all the info developed in the Calcs tab, which includes all relevant info for each pump in its own row, into a separate single page report for each individual pump.

Does that clear it up? I realize I'm looking at a complete redesign - there's too much going on in this one already. Again, I'm not looking for anybody to do this for me, just looking for a push in the right direction so I can figure it out for myself.

Thanks again,
Redwood

Thanks, Tevlyn, that does help quite a bit. It's not as simple a solution as I was hoping for but just plugging it into a couple of cells I do see how I can make it work pretty easily. I need to go back and streamline quite a few characteristics of the inputs before it's going to come together but at least now I have a road map.

Thanks again,
Redwood

Ok, here is a sample of forms and assorted other lookup stuff.

I created a "Data" sheet. It has columns for each of the entry points from your Inputs sheet (I didn't bother with your Output Report. I'll let you do the legwork on that). This should give you an idea of how to create a worksheet that works like a database. The sheet itself will be many, many columns wide, but since you won't be working directly in the sheet, it doesn't matter.

I added forms to add new records and edit existing ones. I threw in some controls to prevent accidental creating of records. The forms are designed to dynamically adjust to your columns. If you need to add or delete a column, you can do so without fearing it will mess up all your code. Just simply add or delete the required code for that column, and it should run smoothly.

The forms enter the text from the form into the database, but it enters the text as text. For the most part this shouldn't matter, but MS Query may have a hard time deciding what datatype each column is. To help with this, you may need to add formatting filters to make it enter the data as the appropriate type (ie Format(Your Code, "#.##") to format as a number with two decimal places, etc)

I created a Display tab. This is to show you a fancy way to lookup records via the sheet. These formulas are also independant of your column order. You can add and delete columns without fear that it will mess up your entire sheet. It works by finding the row of your record, then the colum of the desired field, and displaying the value of the cell where the two meet. You can use this to fill in info in your Calcs tab that easily adjusts depending on the pumpnames you enter.