• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Set up a dynamic list

3G

3G

Member
Hello-

Great website with lots of awesomeness on it!!!


I'm wondering if someone can offer me a solution to the following problem. I have a list of 125 documents, all of which are assigned to one of twelve departments. The list is organized by the document number in ascending value (Column A). Other columns include the Document name, and, in Column C, the department it is assigned to.


I've been tasked with setting up a summary of each department on a separate page, so, that as we add/remove/change the assigned department on the document in the list, the list for that department on their individual "summary" will change as well. I've been trying to use a VLOOKUP, but, it only pulls back the very first value that occurs in the list.


Any thoughts?


Thanks

3G
 
To create list of all document numbers assigned to a department:


=IF(ROWS(A$2:A2)>COUNTIF(Sheet1!C:C,A$1),"",INDEX(Sheet1!A:A,SMALL(IF(Sheet1!C$2:C$100=A$1,ROW(C$2:C$100)),ROW(A1))))


The above is an array formula that needs to be confirmed using Ctrl+Shift+Enter. Copy down as far as would ever be needed. Unused cells will be blank. Key parts:

Sheet1 - the sheet with your 3 columns of data

A$1 - the cell containing department number.

C$2:C$100 - the range of cells containing the departments. Adjust both callouts as necessary.
 
Thanks Luke! I'm a bit confused though. The references keep pointing to the shet I'm in, not the master? It's not pulling anything back?


Here's what I'm looking for...each department will have their own workbook that I'd like updated dynamically. So, let's take the department Legal for example. My data list (in a separate workbook) looks like this...


Column A Col B Col C

001 Form 100 Legal

002 Form 109 Administrative

003 Form 113 Executive

004 Form 045 Legal


What I'd like is to set up individual documents for each where if I change Column C, the individual document list is updated. For example, currently, Legal has 2 documents. Let's say I go in and change document 002 from Administrative to Legal, I'd like the individual Legal page to be updated showing that document 002 is now assigned in their list of assigned documents. Unfortunatly the data on the "master sheet" is going to change regularly, so, not having to update each individual sheet is going to be graet.
 
Just to be clear, this solution is for use within the same workbook. You can have different worksheets for each department. If you start splitting the data off into seperate workbooks, things get a bit tricker...


In the formula I posted, you need to change all the Sheet1 references to the correct sheet name (sheet with all the data in it). There are 3 places in the formula where this is done.


formula explained:

First part of formula checks to see if all the records (the COUNTIF) for that department have already been displayed (checking againt ROWS used).

The second part is using the INDEX function to return the document number. To determine which record to pull, it finds the Nth (where N is incremented by 1 using the ROW function) smallest row that matches our critera (found by the IF function).


Once you get it setup, you should be able to copy the formula to whichever sheet you want. The only input the formula needs is the department of concern, which (right now) the formula is assuming is in cell A$1.
 
Hi Luke-

I've tried to copy this formula (which is just plain awesome by the way!!!) to another section on the same page, to pick from a different worksheet than Sheet1. Additionally, the Department name is in Column G on Sheet2, not in C like Sheet1. My updated formula is as follows:


=IF(ROWS(B$9:B34)>COUNTIF('Issues Log'!G:G,C$3),"",INDEX('Issues Log'!A:A,SMALL(IF('Issues Log'!$G$6:$G$206,ROW('Issues Log'!G$6:G$200)),ROW(A1))))


Please note this "section" of my worksheet starts at B34, not B9. The Department name is still in C3 (I updated it from your formula as the page rearranged), so, the ONLY things that have changed are my worksheet, the column of the reference data. I've tried updating only these values+CSE, manually typing the formula + CSE, but I get an #VALUE error.


Any ideas?


Thanks again
 
Try changing this:

=IF(ROWS(B$9:B34)...


to this:

=IF(ROWS(B$34:B34)>


This section is saying "if the number of formulas (found by counting rows) exceeds the number or records, display blank"


Also,

SMALL(IF('Issues Log'!$G$6:$G$206,ROW('Issues Log'!G$6:G$200)),ROW(A1))))

you have 2 differetn size arrays (6:206, and 6:200)

They need to be the same size.
 
Gotcha! Question...what is the function of the very last ROW(A1) in the formula?
 
Ok...here's my formula updated:


=IF(ROWS(B$34:B34)>COUNTIF('Issues Log'!G:G,C$3),"",INDEX('Issues Log'!A:A,SMALL(IF('Issues Log'!$G$2:$G$206=C$3,ROW('Issues Log'!G$2:G$206)),ROW(A1))))


Before I CSE it, it returns the value of "1" which tells me, hey, at least it's pulling something back. However, once I CSE it, it breaks and goes to #NA. We're SO close! I've typed it no less than 15 times :-s
 
What is "CSE"?


To your previous question, the ROW(A1) is used to increment the SMALL function. The function syntax:

SMALL(array, Nth)

Where N is the nth smallest number you want. By using the ROW function, the formula will increment as you copy down, essentially saying:

Give me 1st smallest number

Give me 2nd smallest number

Give me 3rd smallest number

etc.
 
Thanks. Hmm...everything seems to be working on my end. The N/A error shouldn't be being generating by these functions, only a #VALUE error.


Just a thought, is there perhaps a N/A error in column G of the Issues Log?
 
You must log in or register to reply here.
Back
Top