Share via

I continue to get #VALUE error when referencing a merged cell from one worksheet to another.

Anonymous
2012-09-27T18:52:55+00:00

Data Types = General (I've also tried text but same error) on both worksheets

Merged cells are 1 column X 3 rows on both worksheets

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2012-09-28T15:49:05+00:00

Yes, it is automatically getting the entire range of a merge cells. And you made a good job for putting only the first cell in the Formula.

Sometimes Excel is hard to figuring out what cause the error and what to do. if meet problem again, dont hesitate to open a thread here, it's free. ☺☺☺

Good Luck!

Jaeson

Was this answer helpful?

20+ people found this answer helpful.
0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2015-10-28T00:04:07+00:00

    I had the same problem and had to do a few things to resolve it, so if just putting the first cell in doesn't help, try: 

    1. Make sure the cell with the formula is formatted as General (R click, Format, select General from the list, click OK). 
    2. That worked for all of my sheets except one (I am referencing the same cells and replicating across 3 other sheets, all merged cells). To fix it, I deleted the formula, unmerged the cells in both the sheets  with the formula (call it sheet 2) and the referenced sheet (call it sheet 1), formatted them all as general, and put the formula back in to the first cell (sheet 2), then applied the fix already stated here: just referenced the first cell (of sheet 1) in the formula (e.g. ='Sheet 1'!L7). I was then able to merge and center both sheets again, and the data was replicated to sheet 2. 

    For whatever reason, that worked. I didn't have to do this with the 2 other sheets, I just used the fix already stated:  I fixed the problem by entering only the first cell in the merged cell.

    Was this answer helpful?

    10 people found this answer helpful.
    0 comments
  2. Anonymous
    2012-09-28T12:15:37+00:00

    Hi, Jaeson. I wasn't using a formula, just referencing the cell as ='2B'!L7:L9. I fixed the problem by entering only the first cell in the merged cell. I'm not sure why this occurred; it works on some of my entries (from one worksheet) but when I move to the remaining worksheets I have to only use the first cell as the reference. Excel is sometimes a challenge (even though I'm pretty sure that I have done something to cause this issue). :)

    Thanks for your help anyway.

    Tammie

    Was this answer helpful?

    8 people found this answer helpful.
    0 comments
  3. Anonymous
    2012-09-27T20:35:45+00:00

    Hi,

    What formula are you using that caused Error?

    Thanks,

    Jaeson

    Was this answer helpful?

    0 comments