A trigger to retrieve number list

Tags:

Score

khank February 16, 2011 at 19:06:42 Pacific
Specs: Macintosh

On worksheet NUM SORT I have 9 columns - Row 1 are the header per column (from 0 to 9). On worksheet FINAL LIST looking for a formula to "TRIGGER" the retrieval of the particular list of numbers from "NUM SORT" worksheet. I know it'll be an some sort of an array.
Example: I use the trigger of "1" in worksheet "FINAL LIST" A1, at A2:A27 the list of 25 numbers are retrieved from "NUM SORT".
What would the formulas I use to accomplish this automatic task?

Reply ↓ Report •

Score

DerbyDad03 February 16, 2011 at 21:17:19 Pacific

Give each of the ranges (lists) in NUM SORT a Name. i.e. Make them a Named Range. As an example, I named my lists One, Two, Three, etc.
In Final List, enter a 1 in A1.
Then place this formula is A2 and drag it down to A27:
=OFFSET(CHOOSE($A$1,One,Two,Three),ROW()-2,0)
(Obviously, you'll have 9 Named Ranges where I only have 3)
The CHOOSE function will pick the name of the Named Range based on the order they are in the values.
The OFFSET function will return the value that is OFFSET from the first cell in the Named Range by the number of Rows and Columns used as the OFFSET arguments.
Since the first formula is in Row 2, ROW()-2 will equal 0 and the first value in the Named Range will be returned. As you drag the formula down, ROW()-2 will increment and you'll get the second value, then the third, etc.
Note: If A1 is empty, the CHOOSE function will fail and you'll get a #VALUE error. This can be prevented by wrapping the formula in an IF function:
=IF($A$1>0,OFFSET(CHOOSE($A$1,One,Two,Three),ROW()-2,0),"")
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Reply ↓ Report •

Score

khank February 16, 2011 at 22:57:42 Pacific

DerbyDad03,
Thanks for the info - its 2am in Michigan and I've got to get some sleep.
I will work on this in the a.m. and get back to you ASAP on how it worked out.
The "IF" function will be the way I go.
To bad I can't attach a sample of what I'm doing.
Thanks,
Ken Hank

Reply ↓ Report •

Score

khank February 17, 2011 at 12:03:27 Pacific

I change the headers to read ONE, TWO, THREE, FOUR, FIVE, etc.. And I change the headers to read 0, 1, 2, 3, 4, 5, etc.
I tried with the header names and with header numbers.
I tried in the A1 position on a separate page and on the same in column M.
I tried it as an array and straight copy down
=IF($A$1>0,OFFSET(CHOOSE($A$1,ZERO,ONE,TWO,THREE,FOUR,FIVE,SIX,SEVEN,EIGHT,NINE),ROW()-2,0))
and
=IF($A$1>0,OFFSET(CHOOSE('FINAL SORT'!$A$1,ZERO,ONE,TWO,THREE,FOUR,FIVE,SIX,SEVEN,EIGHT,NINE),ROW()-2,0))
All I get t "#VALUE!",
I don't know what I'm doing wrong, but I tried of wasting my time at this point.
Please advise on what I'm doing wrong.