create a cell reference to another worksheet

[Inese]

Hello! I want to create a cell reference to another worksheet, but in sheet1 from I want to get the information two cells have been merged. So when I use formula on sheet2 it shows result but with empty cell between values. I need to get values from sheet1 without spaces between cells as in sheet2 when numbers goes one after one. Example is given in excel file.
Thank you!

[Pepe Le Mokko]

The best way to deal wth it is to get rid of merged cells. They are nothing but trouble ( as you have noticed..) and one of XL's worst features

Read How to use merged cells effectively

[kvsrinivasamurthy]

In A1 in Sheet2 then copy down

=IFERROR(1/(1/(INDEX(Sheet1!$A$2:$A$10,2*ROWS($A$1:$A1)-1))),"")

[Inese]

Thank you for fast response! It works perfectly if I have 1 number! I'm working with different count of numbers in cell. How to change the formula if I want to write in sheet1 value what contains 123A000 (7 signs)?

[kvsrinivasamurthy]

Pl upload file with example data you like to have.

[Inese]

Thank you, I attached the excel example.

[kvsrinivasamurthy]

In A2 then copy down.

Please Login or Register  to view this content.

[Inese]

Thank you a lot! Works perfectly!!!! Have a nice day!