Totals on a worksheet

[James Russell]

Hey all,

Just doing some work on a theatrical template in some downtime.

I have a database lookup for all the light position object in my drawing.

Two seperate but related issues:

1) I want to count the number of lighting devices on each position. I thought this would be =count('lighting device'.'position'=B4) where B4 is the cell of the database containing ='light position obj'.'position name'. This just returns false.

2) I want to sum the total weight of all the fixtures on my position. Perhaps some sort of sum if? Don't really know where to head with that one.

See attached for the basic layout screenshot.

Cheers,

J

[michaelk]

James

I think this will do what you want.

I thought I could get clever and bury the sum in a concat, but it refuses to update. At least in 2011 it doesn't. If you change the units on a position and recalculate the worksheet the concat doesn't update, but the regular cell does....

hth

mk

[michaelk]

Here's another method.....

There's probably a way to get the unit count to work too, but my brain is too slow this morning.

mk

[James Russell]

Michael,

This is why I love monday mornings. Always something new to try from a weekend worth of pondering.

I'll have a play with these, if nothing else they are really slick. I think your approach of identifying the lamps and which position they are on, apposed to the positions and which lamps they contain is probably the more appropriate path to take at this stage.

I'll let you know how I go.

Cheers,

j