returning an entire array of values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have wasted hours searching through microsoft excel help files trying to find a solution

I have 9 seperate excel arrays. depending on what number I put in my input box (1-9), I want an excel formula to return the whole array of the respective input number. (i.e. # inputed = three, I want a formula to return the entire array named "three") The closest I have gotten is the 'INDEX' function and I do not know how to write macros to complete. Do you know a function that will return an entire array
 
Hi
and what do you want to do with the returned array. That is what
formula do you want to use on this array?.
You may have a look at the INDIRECT function to return a reference
based on a cell entry

--
Regards
Frank Kabel
Frankfurt, Germany

danatureboy said:
I have wasted hours searching through microsoft excel help files trying to find a solution!

I have 9 seperate excel arrays. depending on what number I put in my
Click to expand...
input box (1-9), I want an excel formula to return the whole array of
the respective input number. (i.e. # inputed = three, I want a formula
to return the entire array named "three") The closest I have gotten is
the 'INDEX' function and I do not know how to write macros to complete.
Do you know a function that will return an entire array?
 
danatureboy said:
I have wasted hours searching through microsoft excel help files trying to find a solution!

I have 9 seperate excel arrays. depending on what number I put in my input box (1-9), I want an excel formula to return the whole array of the respective input number. (i.e. # inputed = three, I want a formula to return the entire array named "three") The closest I have gotten is the 'INDEX' function and I do not know how to write macros to complete. Do you know a function that will return an entire array?
Click to expand...
Post the code or formula that "can get it to work to return one cell of
the range", as you stated in your prior post on this subject.

Alan Beban
 
the formula that I used that returns one of the cells is "=INDEX(S12:X16,1,1)" As I mentioned I want it to return and display the entire array not just one cell.
 
I simply want to display this array. I dont care what formula I use as long as the formula will display the correct array based on user input.
 
Hi
not really sure what you're trying to do. If you want to return
multiple cells you also have to select multiple cells as target range.
You may porvide some more details (giove a specific example)

--
Regards
Frank Kabel
Frankfurt, Germany

danatureboy said:
I simply want to display this array. I dont care what formula I use
Click to expand...
as long as the formula will display the correct array based on user
input.
 
we will say in cell A1 I have a drop box that allows 9 values. In cell A5 I need a formula that uses A1 to pick one of 9 seperate 8x5 arrays where each target area is named according to its corresponding value in A1, and returns the whole array starting in cell A5.
 
Hi
try the following:
- starting in A5 select also a 8x5 range of cells.
- enter the following formula:
=INDIRECT(A1)
- enter this formula with CTRL+SHIFT+ENTER (and not only with ENTER)

--
Regards
Frank Kabel
Frankfurt, Germany

danatureboy said:
we will say in cell A1 I have a drop box that allows 9 values. In
Click to expand...
cell A5 I need a formula that uses A1 to pick one of 9 seperate 8x5
arrays where each target area is named according to its corresponding
value in A1, and returns the whole array starting in cell A5.
 
danatureboy said:
the formula that I used that returns one of the cells is "=INDEX(S12:X16,1,1)" As I mentioned I want it to return and display the entire array not just one cell.
Click to expand...

I still don't see what's going on, but if you array enter (i.e., enter
with Ctrl+Shft+Enter instead of just Enter) the following into A5:F9,
the values from S12:X16 will be displayed:

=S12:X16

=INDEX(S12:X16,2,0) will array enter the second row of S12:X16 into
A5:F5, and

=INDEX(S12:X16,0,3) will array enter the 3rd column of S12:X16 into A5:A9

Alan Beban
 
Back
Top