Populating a report sheet based on imported values on another sheet

hi th3spankst3r,

When re-developing a process, it is often a good idea to challenge the validity necessity/design of the existing process. For example:
- does it need to be processed via Excel at all?
- did you know you can Create Reports in Access?
(this may provide a valid alternative)

I don't want to apprear rude, but I have waited for someone else to help you out with this, because, put simply, to a more experienced eye the spreadsheet looks painfully messy ie it is not designed as well as it could be*. My main suggestion for improving the ERO spreadsheet would be to remove the merged cells and to use the Format - Cells - Alignment - Horizontal: "centre across selection" (& tick "Wrap Text") as necessary. A second suggestion is to change your database export so, that when any info is imported into Excel, each header is presented in a separate column & the related data is presented in the rows below.

*The following link(s) may be quite useful for your coworker (& you) to help with some better practices in spreadsheet design: http://www.eusprig.org/smbp.pdf which can be sourced from http://www.sysmod.com/sslinks.htm

Originally Posted by th3spankst3r

I can make simple changes to my db, but due to its complex nature and large size, I'd prefer not to rethink the whole task. If someone can help me out with what I have, I'd GREATLY appreciate it. If you have trouble understanding what it is I'm asking, please ask questions.

I've only started learning Access in the last 6 weeks & am lucky enough to be working alongside some helpful & knowledgeable people (so my learning is being accelerated) but I don't yet recognise all the possible complexities. However, in your case, I think you will get a better & more robust solution if you change the process as close to the "source" as possible. To help me (us) understand more clearly...
- Would Access generated "Reports" work for you?
- does anybody enter data directly into the db?
(could a form be created for entry into the db - perhaps into a temporary table for review)
- do people enter comments directly into the Excel sheet or is it initially manually written on a printed page & then transferred to Excel?
- how is the data "imported" into Excel?
- Once (if) you change the format to one row for headers (with more columns across the worksheet), can you use an "Append Table" query in Access to add updates to new rows, instead of overwriting the existing information?

hth
Rob

hi,

I'm sorry, I don't think it is worth the effort of developing complex formulae in your existing spreadsheet design...

Mods/others,
I'm warping the direction of this thread away from Excel Worksheet Functions sub Forum towards the Access Forum & I apologise for that. If the OP is willing, this thread may need to be moved again!

Originally Posted by th3spankst3r

...
This sounds confusing when I re-read it, but knowing what I want, it's easy for me to picture it in my mind. Sorry I can't be more descriptive.

It sounds like you have already got the solution in mind. I think you may need to step back & look at this from the start by identifying what the problem is, my understanding is:

1 You have a database that users update with the aim of minimising printouts.
2 Interim printed reports are currently needed for initial manual updating.
3 Final reports are needed for attaching to dispatches.
4 An electronic record of these final reports needs (?) to be kept.

Currently...
a) Some updates occur in the data base.
b) Info is exported to specific cells in Excel & these specific cells are referenced to try & put the info in the desired format*.
c) I think (?) a copy of the Excel file is kept as an electronic record.

My suggestions are made with no regard to your current db setup, but more for ongoing usability:
*If Excel is to remain in the process, I strongly recommend redesigning your spreadsheet with NO merged cells (see earlier post). To remove complexity I suggest you modify your ExportToExcel function to write directly to the correct cells in the redesigned Excel Template. This shouldn't be too much of a change, as you are already writing to specific cells, and it removes the necessity for some complicated formulae to "align this with that...".
However, because users are already interacting with the db, I will go a step further & suggest completely removing Excel from the process:

With that in mind, I think that you can achieve "points 1-4" with Access Reports because:
- All the information is already within your existing database.
- I assume, additions to an ERO via your db are stored within (appended to) an existing Access table which can be used as the source for the reports. See here for an explanation of Append queries (this concept is probably hiding within the forms you use).
- Yes pdf'd Reports can easily be created when necessary. In the mean time, data sits patiently in the Table.
- With this data already existing, the specific report can be created at any time the push of a button (see below).
- Unfortunately I'm not experienced enough to provide a solution esp without seeing your existing db design. However, you may be able to add the flexibility to your db yourself by using the ideas in the below links:

http://www.grbps.com/access4.pdf
http://www.eggheadcafe.com/software/...ecific-re.aspx
http://allenbrowne.com/ser-50.html
http://www.brighthub.com/computing/w...les/42372.aspx
http://office.microsoft.com/en-us/ac...963181033.aspx

Try creating a test Access Report using Create - Report Wizard...
This will give you a starting point & you can then move the controls around in Design or Layout View to achieve the layout you want to see.

If you need any specific help, can you please upload a zipped version of a stripped out sample database?
(I think this is legal file type for uploading...?)
This will allow any of the knowledgable Access guys to whip up a mock form/report without needing to start from scratch, so 1) the solution is more likely to be offered and 2) it will be more specific.


Rob