Scroll bar for data based on a specific status

[Kujir]

Hi there I am new to these forums so sorry if I am not clear in what I am struggling to do.


I have been learning how to use scroll bars thanks to Chandoo.org and I have learned how to make a simple scrolling table in the hope that I could develop this for my own needs. The problem I have come across is I only want to show data in my scrolling chart based on the status of the data.


An example of some made up raw data: (there will be more than 10 records)

[pre]

Acc Num# Status	Reason	        Customer	Address	            Core
1	Closed	No Interest	James	        118 Milk Road	    10025
2	Closed	Duplicate	Peter	        119 Andy Street	    10058
3	Open	Unworked	David	        113 Bond Road	    10091
4	Closed	Duplicate	James	        17 Alpen Street	    10124
5	Closed	Interested	Peter	        18 Devon Lane	    10157
6	Closed	Interested	David	        1188 Govan Road	    10190
7	Open	Unworked	Graham	        117 Notting Road    10223
8	Open	Unworked	James	        19 Burbank Street   10256
9	Open	Unworked	Peter	        8 The Block	    10289
10	Open	Unworked	David	        99 Dancer Street    10322
+ MANY MORE

[/pre]
I am creating a dashboard and I want to be able to see the first 10 'Unworked' accounts but with a scroll bar so I can scroll through the rest of them. (There is no fixed number on how many there will be in the Unworked status)


When I created my basic scrolling table I was using the OFFSET function but that pulls through accounts with status that I do not want. Is there a way around this and is it possible?


Any help will be massively appreciated.

 

[Luke M]

Starts to get a little tricky, but doable. I'd suggest taking the scrolling idea and combine it with the technique I described here:

http://chandoo.org/wp/2011/11/18/formula-forensics-003/


Use the latter technique to generate your table. The tricky part will be that in the formula I described, you're going to tweak the ROW(A1) bit

=IF(COUNTIF($C:$C,"Unworked") < ROWS($E$2:E2), "", INDEX(A:A, SMALL( IF($C$2:$C$10 ="Unworked", ROW( $A$2:$A$10)), ROW(A1))))


Since you're using a scroll bar, we need to artificially increase that counter, so it would change to:

=IF(COUNTIF($C:$C,"Unworked") < ROWS($E$2:E2)-1+ScrollBarCounter, "", INDEX(A:A, SMALL( IF($C$2:$C$1000 ="Unworked", ROW( $C$2:$C$1000)), ROW(A1)-1+ScrollBarCounter)))

 

[Kujir]

Hi Luke,


Thank you for taking the time to respond to my query.


I have tried your formula:


=IF(COUNTIF($C:$C,"Unworked") < ROWS($E$2:E2)-1+M4, "", INDEX(A:A, SMALL( IF($C$2:$C$1000 ="Unworked", ROW( $C$2:$C$1000)), ROW(A1)-1+M4)))


(M4 is my scrollbar counter)


the results that I am recieving is:


#NUM!

1

#NUM!

#NUM!

#NUM!

#NUM!

#NUM!

#NUM!


etc..


As I don't fully understand your formula I am wondering if it is something I have done wrong?


Thanks in advance!

 

[NARAYANK991]

Hi Kujir ,


Have you entered the formula as an array formula , using CTRL SHIFT ENTER ?


The formula should preferably not use column references ; use a fixed column reference , or create dynamic named ranges to refer to data in the two columns ; change your formula as follows :


=IF(COUNTIF($C$2:$C$1000,"Unworked")<=ROW(A1)-1+$M$4,"",INDEX($B$2:$B$1000,SMALL(IF($C$2:$C$1000="Unworked",ROW($C$2:$C$1000)-MIN(ROW($C$2:$C$1000))+1),ROW(A1)-1+$M$4)))


M4 should be made absolute , since otherwise , as you copy this formula down , it will change to M5 , M6 ,...


Check the data in columns B and C to ensure that there are no error values anywhere ; even one error value will give an error output , since we are operating on arrays.


Narayan