Results 1 to 4 of 4

20101201, 23:36 #1
 Join Date
 Dec 2010
 Location
 Melbourne Australia
 Posts
 5
 Thanks
 0
 Thanked 0 Times in 0 Posts
Hello there. I am trying to use arrays and some of the more powerful functions in Excel to streamline the business in which I work. On the attached work book, there are 4 worksheets. Sheet incentives is a sample of the data which I have successfully returned from the results of each department. The data on this sheet is simply pasted in, but on my real work book, it uses the matrix on the Worksheet worksheet (sorry for the naming convention!) to calculate which employees deserve which incentives, based on the results of their departments. All good so far. The challenge I face is attempting to summarise the data. On the Manager worksheet, I first want to determine the units that Rick P manages. Conceptually, this is straightforward, as he has a U next to those units on the Worksheet worksheet. Next, I want to summarise all the employees for each manager, and show their names, codes (e.g. U for Unit Manager, VS for Vanilla Shared etc), and, importantly, their incentive. Once I have the name, the rest is pretty easy.
I got as far as the Sample worksheet. I found some help at http://office.microsoft.com/enus/ex...001226038.aspx but couldn't successfully translate the data from finding the row numbers (or corresponding values), to showing the column numbers and their corresponding values. I don't understand why using the fill handle to entend the series from F5 into G5 does not behave consistently, as when extending B15 to B16 etc. It seems to be about the last function in the following formula: =SMALL(IF($E$1:$K$1=$E$5,COLUMN($E$1:$K$1)),COLUMN (1:1)). What role is the COLUMN(1:1) function performing? How can I make this work?
Any help will be greatly appreciated.
(BTW, I used to be jurgens, but that user id is broken and so I created a newie!)
Justin

20101202, 09:24 #2
 Join Date
 Dec 2009
 Location
 Manchester, United Kingdom
 Posts
 116
 Thanks
 8
 Thanked 17 Times in 16 Posts
Hi Justin. "Good on you" for learning arrays!
In the original vertical example, "row(1:1)" was a way to get a number that incrementes as you copy or fill the formula down the column. Because the reference is relative it becomes 2:2, 3;3 etc. as you go down. You need this number with the small() function because you want the 1st match in the 1st result cell, the 2nd match in the 2nd cell etc.
When you transpose the example to work horizontally you correctly changed ROW to COLUMN, but forgot that columns are labelled A, B, C. Just change column(1:1) to column(a:a) and it will work as expected.
You could get the results in a column, with data in rows by using row(1:1) and filling down.

20101202, 19:06 #3
 Join Date
 Dec 2010
 Location
 Melbourne Australia
 Posts
 5
 Thanks
 0
 Thanked 0 Times in 0 Posts
Thanks Ian. It seems so obvious, once you explained it. I'll forge ahead now. I assume that in both cases, the references are relative, not absolute. In other words, we always use 1:1 or a:a regardless of the actual starting row or column. Thanks again.

20101202, 19:58 #4
 Join Date
 Dec 2010
 Location
 Melbourne Australia
 Posts
 5
 Thanks
 0
 Thanked 0 Times in 0 Posts
Oh dear. I'm still stuck! I successfully changed to a:a, which worked to return the column numbers. See F5:H5 on the Sample worksheet on the attached file. But when I tried to introduce the same logic used in B10:B12 to return the value, not the column number, I struck a problem. I don't understand why! The insert function wizard on the INDEX function of the formula in F12 shows the results of the Row_num as 5 and Column_num as 2. Is this a clue to the error? I assume that the intersection of these indices is cell B5?? Any help would be greatly appreciated.
Justin