Linking to value with a dynamic cell location?

[binar]

Fellow Forum Members,
I have a value that can appear anywhere on Sheet1, column A. In other words, it has no static cell position in Column A. It's location could be A1, A5, A19, etc... This value always has the following format: xxxxxxxxAAA (21 text characters always followed by AAA)

The problem is I need to link to this value from Sheet3 and since it's not a static cell location I have to manually setup the link myself.

Is there an Excel Function that can search through Sheet1, Column A for the text "AAA" and then return back a cell location of where it's at in Sheet1, Column A and then setup a link on Sheet3, A1? Such a function will eliminate the need for me to manually search through Sheet1 Column A and then manually link to it everytime the link needs to be reconnected. Does such a function involve using an Array? Any help will be greatly appreciated. Thanks.

 

[T. Valko]

Fellow Forum Members,
I have a value that can appear anywhere on Sheet1, column A. In other words, it has no static cell position in Column A. It's location could be A1, A5, A19, etc... This value always has the following format: xxxxxxxxAAA (21 text characters always followed by AAA)

The problem is I need to link to this value from Sheet3 and since it's not a static cell location I have to manually setup the link myself.

Is there an Excel Function that can search through Sheet1, Column A for the text "AAA" and then return back a cell location of where it's at in Sheet1, Column A and then setup a link on Sheet3, A1? Such a function will eliminate the need for me to manually search through Sheet1 Column A and then manually link to it everytime the link needs to be reconnected. Does such a function involve using an Array? Any help will be greatly appreciated. Thanks.

Let's assume Sheet1 cell A9 contains xxxxxxxxAAA.

What exactly do you want to appear on Sheet3?

 

[Jonmo1]

Perhaps

=INDEX(A:A,MATCH("?????????????????????AAA",A:A,0))

Yes, that's 21 question marks.

Hope that helps.

 

[binar]

Let's assume Sheet1 cell A9 contains xxxxxxxxAAA.

What exactly do you want to appear on Sheet3?

I need the same Text content located anywhere on Sheet1 Column A1 to appear on Sheet3 A1 without I having to manually search for the single instance where it ends with "AAA" and then setup a link. All the "xxxxxx" represent whatever text. I happen to have have data that is 21 characters, always tailed by "AAA" .

I have not tried out the INDEX function below yet. But I will post whether it works or not. Thanks to all for their help.

 

[T. Valko]

I need the same Text content located anywhere on Sheet1 Column A1 to appear on Sheet3 A1 without I having to manually search for the single instance where it ends with "AAA" and then setup a link. All the "xxxxxx" represent whatever text. I happen to have have data that is 21 characters, always tailed by "AAA" .

I have not tried out the INDEX function below yet. But I will post whether it works or not. Thanks to all for their help.

Ok, then jonmo1's formula will do what you want.

 

[Jonmo1]

Note,

My posted suggestion looks specifically for 21 characters followed by AAA.

So it won't find xxxAAA or xxxxxAAA, only xxxxxxxxxxxxxxxxxxxxxAAA
And not 25 characters followed by AAA


Depending on your requirements it can be simplified as
=INDEX(A:A,MATCH("*AAA",A:A,0))

However, this finds any string ending in AAA (regardless of how many characters there are to the left of the AAA).

 

[binar]

jonmo1,
Thanks a thousand times for your formula. It works great within the same worksheet. However, I am having a very difficult time tweaking it so that it takes into account "Sheet1" (where the source data is at) and "Sheet2" where the link itself resides. Everytime I try to add Sheet references within your formula it ruins it. Obviously, I feel really embarassed about not being able to do it myself, but it seems to be something I can't get to work. If you can tweak it I will be very grateful. Thanks.

 

[Jonmo1]

This goes in a cell on Sheet2

=INDEX('Sheet1'!A:A,MATCH("*AAA",'Sheet1'!A:A,0))

 

[binar]

This goes in a cell on Sheet2

=INDEX('Sheet1'!A:A,MATCH("*AAA",'Sheet1'!A:A,0))

Jonmo1,
Thanks for the revised formula. I was close, but apparently not close enough since I didn't figure it out on my own.

If it's not too much trouble can your formula be taken one step further to do the following. Currently, it targets "AAA" and these three unique target characters have to be located at the end of what ever length of text string.

Is it possible to modify the MATCH function so that it doesn't care where the "AAA" is located? Whether it's located at the beginning, middle or end of any length text string, it is able to find it by matching to the three unique characters (AAA) and then return the corresponding data so that it can be used as a link in a different worksheet.

Thanks a lot in advance if you decide to post a formula.

 

[T. Valko]

Jonmo1,
Thanks for the revised formula. I was close, but apparently not close enough since I didn't figure it out on my own.

If it's not too much trouble can your formula be taken one step further to do the following. Currently, it targets "AAA" and these three unique target characters have to be located at the end of what ever length of text string.

Is it possible to modify the MATCH function so that it doesn't care where the "AAA" is located? Whether it's located at the beginning, middle or end of any length text string, it is able to find it by matching to the three unique characters (AAA) and then return the corresponding data so that it can be used as a link in a different worksheet.

Thanks a lot in advance if you decide to post a formula.

Try this...

=INDEX(Sheet1!A:A,MATCH("*AAA*",Sheet1!A:A,0))