Index and linked cell with array format.

Tunarle

New Member
Joined
Jul 22, 2018
Messages
3
Hello to all,

Some data are saved, in a single cell with the following format:

{"test1";"test2";"test3"}, Let's say this array is in cell A1.

But at the same I want to use the Index function, in another cell, to get the value of my preference, from the array above.

Example: =Index(Range;2)


Is there a way to make this function to understand the A1 cell as a range? (Range=A1)

I am aware, that I can use the following syntax: =Index({"test1";"test2";"test3"};2).

I have tried with Indirect. But couldn't find a way.


I hope there is a method.
Thanks.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Hello, you could define a name (via ctrl + F3).

Call it "test" and enter this in the refers-to box:
= {"test1", "test2", "test3"}

Then, in your cell, you can write:
= INDEX(test, 2)
 
Upvote 0
Hello, you could define a name (via ctrl + F3).

Call it "test" and enter this in the refers-to box:
= {"test1", "test2", "test3"}

Then, in your cell, you can write:
= INDEX(test, 2)


Thank you for your reply. Yes indeed this is an option, if the cells that are in this format are few.

But, what if, column A, is filled till the 1000 row. Then I ll have to define 1000 different names?


Thanks again. Very useful your information.
 
Upvote 0
Thanks for your info.

I ve ended up with the following method:

Changed the format, of the cells:
{"test1";"test2";"test3"} -> test1;test2;test3

Extract the values in a help_worksheet, with the needed columns, and then retrieve what I want with index, as
Aladin Akyurek mentioned.

And use dynamic range(some cells with the array have to be populated with more values) where is needed, using name manager, as ParamRay said.


Cheers.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top