Can this type of lookup be done in Fireworkz?

[Michael]

I have a large wine collection. Each type of wine can be stored in any of 6 locations. I have a master file which has one row for each type of wine: column a is a total for that wine. Columns b-g contain the number of bottles of that wine in each of the 6 locations. Column h has the description of that wine.

I would like to create 6 sub-sheets, which read from the main sheet. Each sub-sheet would list the wines for one of the six locations. So subsheet 1 would list the wines in location 1, and so on. Each such list could then be printed and put on the door of the relevant location.

I am sure that somehow I need to create an array for each of the 6 locations, which would contain the contents of that location. However, the LOOKUP function needs a fixed key, not an expression. I need to create an array listing entries in the main table where the relevant column (listing how many bottles are in that location) is not empty. So for sheet 1 I would list rows where column b of that row is not empty, for sheet 2 I would list rows where column c of that row is not empty, etc.

I thought this would be a simple task, but I can't see how it can be done (or if it can be done at all...)

Any help would be most appreciated.

Many thanks,

Michael

--

Michael
myb...@gmail.com

[John Harrison]

I did something similar.

In each sub sheet I set the heads of the relevant columns to equal the
equivalent cells in the source sheet (by selecting the target cell, type
=, click the source cell, then press enter. Then I used Fill down to add
the other cells.

That doesn't omit blank cells though, it just shows them as blank (the
source cells have a formula to be blank if there is no value feeding
them).

You could sort the sub sheets to put blank entries at the bottom. That
would preserve each cell's link so the sub sheet will update, but if blank
entries become non-blank or vice versa you would need to re-sort.

Not sure whether that helps.

--
John Harrison
Website http://jaharrison.me.uk
Using 4té and ARMX6, both running RISC OS

[Michael]

I hit on a great solution!

Stage 1 is to have a column containing a test to see if the slot in the main sheet (listing all the wines) with the quantity of the wine in that entry which is in the relevant cabinet is not empty, and if so, set the slot to "YES".

For example, if column b has the number of bottles of that wine in cabinet 1, and column c has the number of bottles of that wine in cabinet 2, the subsheet to create the list of wines in cabinet 1 will be testing the b column of that row, and the subsheet to create the list of wines in cabinet 2 will be testing the c colum.

Stage 2: set up an array, using Lookup(,,1) to all slots in the column with the names of the wines in the main sheet whose corresponding entry on the subsheet is YES

Stage 3: Now use the set_value() function to set a column (pushed over to the next page by adding some blank columns in between) to the array created in stage 2.

I hope I have explained it clearly enough.

It works!!

--
You received this message because you are subscribed to the Google Groups "Colton Software Fireworkz" group.
To unsubscribe from this group and stop receiving emails from it, send an email to colton-software-fi...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/colton-software-fireworkz/5b0a8dae5ajohn%40jaharrison.me.uk.

--

Michael
myb...@gmail.com