OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

dynamic list dialog boxes

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
davemorley
General User
General User


Joined: 28 Jul 2012
Posts: 12

PostPosted: Sat Jul 28, 2012 10:56 pm    Post subject: dynamic list dialog boxes Reply with quote

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
Back to top
View user's profile Send private message
jrkrideau
Super User
Super User


Joined: 08 Aug 2005
Posts: 6732
Location: Kingston ON Canada

PostPosted: Sun Jul 29, 2012 5:52 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
davemorley
General User
General User


Joined: 28 Jul 2012
Posts: 12

PostPosted: Sun Jul 29, 2012 2:52 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
jrkrideau
Super User
Super User


Joined: 08 Aug 2005
Posts: 6732
Location: Kingston ON Canada

PostPosted: Mon Jul 30, 2012 5:35 am    Post subject: Reply with quote

davemorley wrote:
I hear you about "silly" mistakes.


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


dave


Yes. I would expect a lot to start with but you win the the longer term.

The problem is finding the time to do the initial planning and development work but, the time spent setting up the popup dialog boxes or whatever may take just as much time and close to as much learning effort and you're still left with a fragile system.

One of the real advantages of a data base is that it is essentially self-documenting so you can see and catch problems. I live in terror (well a bit of worry) that someone will multiply a drug dose by a telephone number or inventory number in a spreadsheet since it is all to easy to click on R22 instead of R23.
_________________
jrkrideau
Kingston ON Canada
Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group