returning a value in a single cell from a merged cell

[slogs]

I have some accounts data (exported as a csv file with no header) and have copied & Pasted it into my current workbook from the workbook it exports into.

It appears to have merged cells with data in (such as supplier name, invoice date, invoice value). on the worksheet itself I can get it to return the merged cell data in a single cell by using = pointed to the block of cells.
For example E13:E17 have a value of 2171. In cell F3 if you type = and hold over E14 (E14 has a fuzzy yellow outline) and click, E13:E17 turn blue with a blue outline, push return and cell F3 returns 2171

However, if I do this on a new sheet within the workbook it returns #VALUE!

Can somebody please advise what I am doing incorrect

[FDibbins]

Hi, welcome to the forum

the data in merged cells is always (and only) in the top left cell. So if you have merged cells E13:E17, the data is in E13. E14:E17 are blank. So when you need to reference that merged range, even though it will show as E13:E17, most formulas will not accept that, you need to use just E13

Hope that helps?

[slogs]

Unfortunately not
If on the new sheet in cell A1 I type =, click on sheet, hold over E13 it flashes around the four cells and returns ='sheet2'E13:E17 and gives the #VALUE! error

[FDibbins]

Yes, and thats exactly what I said - "even though it will show as E13:E17, most formulas will not accept that"

You will need to either type the reference manually, manually delete the 2nd ref, or unmerge the cells.

Merged cells cause nothing but problems, (as you have found out), we always advise users to not use them, if at all possible

[slogs]

Got it
Great
Thanks

[slogs]

Unfortuately i have to use them as thats the way it exports from the accounts software.