Collection cell information, this should be easy (maybe)

DSLA

Active Member
Joined
Jun 6, 2005
Messages
296
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?
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

JamieDuncan

Board Regular
Joined
Aug 23, 2006
Messages
132
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

Active Member
Joined
Jun 6, 2005
Messages
296
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

Board Regular
Joined
Aug 23, 2006
Messages
132
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

Active Member
Joined
Jun 6, 2005
Messages
296

ADVERTISEMENT

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

Board Regular
Joined
Aug 23, 2006
Messages
132
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

MrExcel MVP
Joined
May 15, 2003
Messages
8,638

ADVERTISEMENT

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

Active Member
Joined
Jun 6, 2005
Messages
296
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

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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

Active Member
Joined
Jun 6, 2005
Messages
296
Thank you the formula worked.

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

Forum statistics

Threads
1,136,926
Messages
5,678,605
Members
419,775
Latest member
joh93

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
Top