Apologies for the convoluted title but I couldn't think of a simpler way to put it without losing some fo the problem.
Background:
I have a sheet that populates two tables by running queries on an SQL database we operate from. It contains information relating to moving goods around a warehouse and we derive performance statistics from it.
The user enters a date into a certain cell and the query runs to fetch all the movement transactions for the day and night shifts working that day (theres a bit of manipulation to accomodate shift start times as well) ultimately it returns two tables that cover 12 hours worth of transactions each.
Depending on a large variety of factors, the number of rows returned varies massively from day to day. The inbuilt excel Table functionality copes with this very well.
Now to the problem:
I want to now do some fairly rudimentary counts and sums to generate the performance figures for each shift. The first thing I want to do is to work out who was working on that shift, a list of distinct values in the column storing the operative who generated the record. The best way I've found so far is via this formula:-
"=IFERROR(INDEX($B$6:$B$10,MATCH(0,COUNTIF($D$5:D5,$B$6:$B$10),0)),"") " - courtesy of myonlinetraininghub.com
This works very well for a normal data but doesn't seem to handle tables well, I get a zero output to the cell despite the Fx tool showing me the first name in the list. When I simply set the 'data' ranges to the fixed locations on the sheet I still get the zero's output. If I set the data ranges to the full column (e.g. N:N) it includes lots of blank rows which stop this formula from working as it cannot handle blank spaces.
My attempt at table notation in the above formula:-=IFERROR(INDEX(Day_Shift_Stock_Movements_Filtered_by_Date[trn_user_full_name],MATCH(0,COUNTIF($G$4:G4,Day_Shift_Stock_Movements_Filtered_by_Date[trn_user_full_name]),0)),"")
Given the variable lengths of the tables and this formula's inability to handle blanks I am looking for a way to either modify this formula to work with veariable length tables or else an alternative method altogether.
Does anyone have any ideas how this might be achieved?
Thanks in Advance
Rob
Background:
I have a sheet that populates two tables by running queries on an SQL database we operate from. It contains information relating to moving goods around a warehouse and we derive performance statistics from it.
The user enters a date into a certain cell and the query runs to fetch all the movement transactions for the day and night shifts working that day (theres a bit of manipulation to accomodate shift start times as well) ultimately it returns two tables that cover 12 hours worth of transactions each.
Depending on a large variety of factors, the number of rows returned varies massively from day to day. The inbuilt excel Table functionality copes with this very well.
Now to the problem:
I want to now do some fairly rudimentary counts and sums to generate the performance figures for each shift. The first thing I want to do is to work out who was working on that shift, a list of distinct values in the column storing the operative who generated the record. The best way I've found so far is via this formula:-
"=IFERROR(INDEX($B$6:$B$10,MATCH(0,COUNTIF($D$5:D5,$B$6:$B$10),0)),"") " - courtesy of myonlinetraininghub.com
This works very well for a normal data but doesn't seem to handle tables well, I get a zero output to the cell despite the Fx tool showing me the first name in the list. When I simply set the 'data' ranges to the fixed locations on the sheet I still get the zero's output. If I set the data ranges to the full column (e.g. N:N) it includes lots of blank rows which stop this formula from working as it cannot handle blank spaces.
My attempt at table notation in the above formula:-=IFERROR(INDEX(Day_Shift_Stock_Movements_Filtered_by_Date[trn_user_full_name],MATCH(0,COUNTIF($G$4:G4,Day_Shift_Stock_Movements_Filtered_by_Date[trn_user_full_name]),0)),"")
Given the variable lengths of the tables and this formula's inability to handle blanks I am looking for a way to either modify this formula to work with veariable length tables or else an alternative method altogether.
Does anyone have any ideas how this might be achieved?
Thanks in Advance
Rob
