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?

 

[fairwinds]

In some cases you can construct formulas with functions that will make it work without C-S-E.

Here however I think you need it.

My general suggestion for your case is to re-think your layout and, if possible, avoid merged cells.

 

[DSLA]

I'm testing it with unmerged cells, is there any changes to the formula when the files are not merged?

 

[DSLA]

help?

 

[fairwinds]

You said it worked! ???

What help do you want exactly?

 

[DSLA]

Yes it worked, but in the previous answer you said: "My general suggestion for your case is to re-think your layout and, if possible, avoid merged cells."

Now i have removed the merged cells and i asked if now there could be another way to handle the data collecting without the CTRL+SHIFT+ENTER.

 

[fairwinds]

OK, so C+S+E was the issue.

Try:

=INDEX(DATA1!A1:A1000;SUMPRODUCT(SMALL((DATA1!A1:A1000<>"")*(ROW(DATA1!A1:A1000));1+COUNTBLANK(DATA1!A1:A1000))))

Should work whether you have merged cells or not.

It is the bolded 1 you should change to get second e.t.c.