Reference to cell with text is returning #VALUE

[Jorist]

I have a cell that references a different cell that that consists of H9:J9
that is formated for and contains text.

The referenceing cell shows #VALUE instead of the actual text that is in the
cell?

Any ideas as to why this is happening? Any help would be appreciated.

 

[T. Valko]

What does your formula look like?

A simple link formula can only reference a single cell. Like this:

=H9

You can't reference a range of cells like this:

=H9:J9

That is why you're getting the error.

 

[Jorist]

I have a series of merged cells H9:J9 that contain text. I am trying to
reflect the contects of this merged cell in another cell on a different tab.

I am using the following reference:

='Current Market Conditions'!H9:J9

This is retruning the #VALUE error. I am using the same syntax on another
series of merged cells

='Current Market Conditions'!B38:K38

which is returning the text found in this merged cell series correctly.


Any ideas as to why the first one is giving me a #VALUE error and the second
one is not?


Thanks.

J

 

[T. Valko]

When referencing a merged cell, reference the top left cell.

A1:C5 is a merged cell.

=A1

='Current Market Conditions'!H9:J9

Reference cell H9:

='Current Market Conditions'!H9

='Current Market Conditions'!B38:K38
which is returning the text found in this merged cell series correctly.

That's just "dumb luck". It depends on what cell the formula is in. Try
entering that formula in cell AA100.

Reference cell B38

='Current Market Conditions'!B38

 

[Jorist]

Thanks. I thought that I had tried that but I guess not.

Just as as side not to anyone reading this posting:
The formula that I used was obtained by typing = and then navigating to
the cell on the page that I wanted to reference. This is where the formula
='Current Market Conditions'!H9:J9 was created automatically by Excel. This
formula appears to be incorrect. ('Current Market Conditions' is the name of
the page where the reference cell is located.) Making the correction
suggested by T. Valko, the formula worked perfectly.

Thanks again.

Jorist

 

[T. Valko]

You're welcome. Thanks for the feedback!