Worksheet to count lighting devices by type on each position.

[tdlx]

Hi, 

 

Apologies if this has been covered, I've looked through several worksheet posts and can't seem to find a solution. 

 

I'm trying to create a worksheet that lists the lighting positions in rows, and the instrument type across the columns. 

 

 

I can populate the headers / position names manually if necessary, but working out how to do the counts I'm getting stuck on! Any help greatly appreciated! 

 

Many thanks, 

Tom 

[TomWhiteLight]

You won't be able to get the worksheet to populate in this format.

The only way to do this is to specifiy a single worksheet by position, making the criteria. (Position contains LX 1 for example). and then line up all of the individual sheets on the page to make one sheet.

Then you can have counts  (Func = CT).

Basically counts and rows can only go do the page not down the page and across at the same time.

There is a feature request for this but this is not something the current worksheets can do.

 

[Pat Stanford]

@TomWhiteLight Can you clarify please?  A Worksheet Database certainly can't do what is being asked for but I think you can accomplish it with Spreadsheet cell formulas.

 

Since the Lighting Device record contains both the Instrument Type and the Postion, you can use a complex criteria with the Count function to get the data directly from the Lights.

 

In my test file I listed the Position Names in Column A and the Instrument Types in Row C.  By using a $ in the cell reference in the formula you can force it to use an absolute Row or Column reference so when you copy and paste the formula it will still work. The formula I came up with is:

 

=COUNT(('Lighting Device'.'Position'=$A7) & ('Lighting Device'.'Inst Type'=B$6))

 

But I know almost nothing about lights and positions, so I could have this completely wrong.

[TomWhiteLight]

Thanks @Pat Stanford any chance you can post the worksheet.

I knew this was not possible using the worksheet database but I have never tried then editing the worksheet criteria.

 

[Pat Stanford]

The "good" part of the worksheet is rows 6-10. Above that was when I was still trying to use a database.  This would be tedious to set up for hundreds of positions and instrument types, but it should work.

 

 

Count Pos and Inst Type.vwx

[TomWhiteLight]

Thanks Pat, I will try it with a few files and report back.

[TomWhiteLight]

Pretty solid, obviously have to update the position and instrument type names but its great!

[Pat Stanford]

With the absolute Row and Column references, you should be able to paste the formula anywhere inside the "rectangle" of the Positions Name and Instrument names and it should just work.  🤞