dynamic list dialog boxes

[davemorley]

Hello all.

First time poster, long time user of open office. Hoping to seek some advise.

We have been using a spreadsheet as a quoting system for work for quite some time. It has served us well and has become more and more complex over the years. Im at the point now where I want to take it to the next level. At the moment the process is quite manual in a few areas (I have simple macros to do some menial tasks, but even they have issues at times) so I would like to remove the manual side and look at introducing the concept of popup dialog boxes as an easier and quicker way of inputing the data.

Im not necessarily asking how do i do this, more so than, is this possible?, and ideally get some pointers in the right direction to get me going.

Hopefully I explain it clearly enough to understand what I am trying to achieve. My field is Visual effects and Animation, so the examples below relate to our day to day work.

The quote sheet is spread across multiple pages. One of the pages called "Templates" is a breakdown of "subTasks" and the times that would be associated with them based on a department.

The other sheet is the "shot" that then has a duplicate line for each subTask required.

for example.

Templates may contain
- camera track
- rotoscoping
- mattePainting
etc...

as each of these tasks involve different departments we have columns that have these departments and the times in man hours that each of them would need to complete the subtask in the template. So camera track may only require the camera department for 10 hours and the editorial department for 5 hours.

Once this information is in, we then use these template items to build man hours required to complete a given "shot"

So, we may have shot 0010, the brief may be to add a matte painting house behind a person in shot. So for this particular shot we would create 3 lines all associated to shot 0010 that would point to the data on the template sheet

s0010 - camera tracking (given the example above would actually equate to 10 hours camera department, 5 hours editorial department)
s0010 - rotoscoping
s0010 - matte painting

the times associated with these "Templates" then get used to derive the total man hours for shot 0010. (this is calculated in another sheet that sums the total man hours across all template items for each shot) giving us a cost for the shot.

Hopefully this is making sense.

Currently what I need to do is duplicate each line and change each "template" item name. This works fine, but is slow , when I can have 8-10 templates required to solve 1 shot. multiple that over hundreds of shots, it can take quite some time to get through it all.

I would really like to be able to hit a macro to pop up a dialog box that is dynamically linked to the template page. I would then simply be able to check on or off which items are required for that shot. When I accept my options, it would automatically duplicate the rows and change the template names.

Am I making sense?

Any advise would be hugely helpful. Ive been trying to get my head around lists, but Im just not sure If this is the best approach and also how to dynamically link lists.

Thank you for your time

regards

dave

[jrkrideau]

I'm not a data base user so take this with a grain of salt, but I really think you need to consider migrating to a data base approach.

It sounds to me like you have reached about the upper limits of the kind of complexity that a spreadsheet can handle safely. Note, I don't mean it cannot be, just that as a spreadsheet becomes more complicated the potential for errors in operation or maintance seem to grow much faster than the spreadsheet complexity.

I think this is particularly true if it has multiple users/maintainers.
Some simple but nasty examples Spreadsheet Horror Stories

I don't want to downplay the effort that would have to go into developing a data base application but I think it is, in the long run, much better and much safer in terms of data integrity and data safety.
_________________
jrkrideau
Kingston ON Canada
Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2

[davemorley]

I hear you about "silly" mistakes.

We cross check the final number with another quoting system based on total man hours calculated per department, so that helps picks up any glaring errors.

I look into the database Idea, but it does sound like quite an undertaking to translate everything over?

thanks for the advise

dave

[jrkrideau]