Collection cell information, this should be easy (maybe)

[DSLA]

I few cells that are quite big, but there is only 1 line of information on them each.

The cell is A9 to A95, it's a merged cell structure.

I would like to copy the value inside the cell, but not the cell itself (because of the big size).

Also i would like to automate it, so it could collect the information automatically, not depending on the specific size of the cell. Could this be done with Vlookup or something?

Can someone help me with this one?

 

[JamieDuncan]

If you enter the formula

=A9

into any cell it will only show the value without changing size,
is this satisfactory?
if not can you please give more details

 

[DSLA]

But the is more than only one large cell in the same row that has the one row value.

For example in the row A there is 1 value in a9:a120 and another in a155:a200

I wan't to be able to look for them, because they are not always in those specific cells

 

[JamieDuncan]

Are the values in the cells constant?
Is there any data directly linked to these cells?

What end result do you want?

Do you have specific cells elsewhere that you wish to display these values after they have been found?

 

[DSLA]

In the A column there can be even 4 values total in 4 different large cells.

There is no data linked to the cells.

I want only to copy the value inside the 4 large cells, but they are not always in the same places (cells)

I have specific cells that i want the values to be copied to

 

[JamieDuncan]

Try this,

=LOOKUP("Value1",A:A)

Value1 being the value you are searching for, the formula will search entire A column for the value in it.

Is this what you needed

 

[fairwinds]

Hej!

Try:

=INDEX(A1:A1000;SMALL(IF(A1:A1000<>"";ROW(A1:A1000));1))

Confirmed with Ctrl + shift + enter.

This should give you the first value.

Changing the 1 at the end of the formula to 2 will give you the second e.t.c

 

[DSLA]

I've entered this: =INDEX(DATA1!A1:A1000;SMALL(IF(A1:A1000<>"";ROW(A1:A1000));1))

It get the data from another sheet, but in the same file.

The result is 0, and no error prompts.

What does this mean: Confirmed with Ctrl + shift + enter.

 

[fairwinds]

You must reference the other sheet in all places:

=INDEX(DATA1!A1:A1000;SMALL(IF(DATA1!A1:A1000<>"";ROW(DATA1!A1:A1000));1))

After entering the formula, place cursor in formula bar and press Ctrl + Shift + Enter simultaneously. (Not just Enter)

If done correctly, formula will then look like:

{=INDEX(DATA1!A1:A1000;SMALL(IF(DATA1!A1:A1000<>"";ROW(DATA1!A1:A1000));1))} in the formula bar.

Ctrl + shift + enter makes the formula an array formula. Read more about those in help file.

 

[DSLA]

Thank you the formula worked.

Is there a way to bypass the CTRL + SHIFT + ENTER, so i would go automatiocally?