Return Cell ID then use it in a formula

[msdawg]

I am trying to simplify a large worksheet by using a formula which looks up a text value in a column, returns its location (ex. A7), and then plugs "A7" into a formula. Since the list is dynamic and the position changes, it has to be able to update and can't be staticly locked. I have everything up to the point where I am trying to plug in a text "A7" located in another cell into my formula.

Any help would be appreciated.

 

[PaddyD]

hi - welcome to the board!

your description isn't quite clear enough - care to describe your data & exactly what you are trying to achieve - a worked example would help.

 

[barry houdini]

There are a number of ways you might accomplish this depending on the formula in which you wish to plug the reference, can you post that?

Possibly one of

=INDIRECT("A"&MATCH("x",A:A,0))

or

=INDEX(A:A,MATCH("x",A:A,0))

 

[msdawg]

Thanks for the welcome.

To clarify what I'm looking for:

I am importing a large amount of data from a csv file and I am hoping to sort it automatically.

One row in column A is a header, then a series of data sets, then another header followed by it's data set, etc.

ex.

Apples
3
5
7
Oranges
4
6
8

I am hoping to use OFFSET to shift the data into a more usable format. However, I cannot just specify a start cell for the OFFSET because it will not always be the same starting point. I would rather look up the cell the header is located in, such as A3, and tell OFFSET to start from A3. It could easily be a different cell the next time.

I tried to use INDIRECT earlier, but I can't reference its resulting text as part of my formula.

 

[PaddyD]

given the actual data you're working with, what distinguishes between the headers & the data? how much data have you got here (number of records / number of columns you need to generate)?

 

[msdawg]

About six groups.

a header
then 11 columns and then about 2400 rows
next header
11x2400
etc
etc

Not really desirable to go through and hunt for the headers each time.

 

[PaddyD]

still no clear idea about your data - first of all we had one column ("One row in column A is a header, then a series of data sets..."), now we;ve got 11?

please describe it with an example.

 

[Rhodie72]

Looking at the base sample I think that What you are looking for is a way to distinguish numbers from text to locate the data. VBA can achieve this magnificently and very fast. If you can dump a CSV on the forum that would be great and then maybe I could help you achieve it.

 

[Fluff]

Do you realise this thread is 18 years old?

 

[Rhodie72]

Well, that's great. Resurrecting it from the dead like JESUS CHRIST! lol. Often these old posts are seen by others who may not have considered the alternatives...