Reference to cell with text is returning #VALUE

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.
0
Utf
3/3/2010 6:00:06 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
1447 Views

Similar Articles

[PageSpeed] 13

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.

-- 
Biff
Microsoft Excel MVP


"Jorist" <Jorist@discussions.microsoft.com> wrote in message 
news:78E1877F-547A-4AD3-B417-C50933E220FA@microsoft.com...
>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. 


0
T
3/3/2010 6:28:08 PM
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" wrote:

> 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.
> 
> -- 
> Biff
> Microsoft Excel MVP
> 
> 
> "Jorist" <Jorist@discussions.microsoft.com> wrote in message 
> news:78E1877F-547A-4AD3-B417-C50933E220FA@microsoft.com...
> >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. 
> 
> 
> .
> 
0
Utf
3/3/2010 9:10:02 PM
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

-- 
Biff
Microsoft Excel MVP


"Jorist" <Jorist@discussions.microsoft.com> wrote in message 
news:33E24829-D823-4CFD-8229-75F1A1ADE81B@microsoft.com...
>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" wrote:
>
>> 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.
>>
>> -- 
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Jorist" <Jorist@discussions.microsoft.com> wrote in message
>> news:78E1877F-547A-4AD3-B417-C50933E220FA@microsoft.com...
>> >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.
>>
>>
>> .
>> 


0
T
3/3/2010 9:33:00 PM
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" wrote:

> 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
> 
> -- 
> Biff
> Microsoft Excel MVP
> 
> 
> "Jorist" <Jorist@discussions.microsoft.com> wrote in message 
> news:33E24829-D823-4CFD-8229-75F1A1ADE81B@microsoft.com...
> >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" wrote:
> >
> >> 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.
> >>
> >> -- 
> >> Biff
> >> Microsoft Excel MVP
> >>
> >>
> >> "Jorist" <Jorist@discussions.microsoft.com> wrote in message
> >> news:78E1877F-547A-4AD3-B417-C50933E220FA@microsoft.com...
> >> >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.
> >>
> >>
> >> .
> >> 
> 
> 
> .
> 
0
Utf
3/4/2010 7:19:01 PM
You're welcome. Thanks for the feedback!

-- 
Biff
Microsoft Excel MVP


"Jorist" <Jorist@discussions.microsoft.com> wrote in message 
news:8F246E44-5BD0-41AB-91F9-F1FD47EEA99F@microsoft.com...
> 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" wrote:
>
>> 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
>>
>> -- 
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Jorist" <Jorist@discussions.microsoft.com> wrote in message
>> news:33E24829-D823-4CFD-8229-75F1A1ADE81B@microsoft.com...
>> >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" wrote:
>> >
>> >> 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.
>> >>
>> >> -- 
>> >> Biff
>> >> Microsoft Excel MVP
>> >>
>> >>
>> >> "Jorist" <Jorist@discussions.microsoft.com> wrote in message
>> >> news:78E1877F-547A-4AD3-B417-C50933E220FA@microsoft.com...
>> >> >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.
>> >>
>> >>
>> >> .
>> >>
>>
>>
>> .
>> 


0
T
3/4/2010 9:54:42 PM
Reply:

Similar Artilces:

00/01/1900 In the date cell when linked cell is empty?
Hi I have 52 worksheets all linked up to a total sheet that is working very well thanks to RD All I need to do now is sort out the date this is copied from sheet1 onto the totals page and works fine when the date is put into sheet1. However if the cell is blank on sheet2 the date is put into the totals sheet as 00/01/1900 when the date is put in sheet2 all is well with the right date entered into the totals sheet. So how do I get the totals date to stay blank until the weekly sheets have a date put into the sheet please. the format I am using is below many thanks for all the help I ...

Need to reference a block of cells from another sheet.
Dear All, apologies if this is probably a mundane question, but I didn't know what 'search terms' to put in google to return any results.. I've got a Spreadsheet with 12 tabs (sheets), I need part of tab 12 (sheet) to appear on tabs 1-11 (sheets), so that if I change data in tab 12 (sheet), tabs 1-11 (sheets) are changed too.. the cells themselves will never move location, just the data within them.. Is it possible? Regards Paul. On your sheet1 select the cell that should receive the data from Sheet12; Hold down the Shift key and Click on Sheet11 << this shoul...

Cell Value as Named Range Reference
Little bit of a quirky question... Trying to use a cell value as a reference in a formula, where that cell value is the name of a named range. So, assume A1:A3 is a named range: Rng1. And, B1:B3 is Rng2. I want to get the correlation vale for A1:A3 and B1:B3 So, cell D1 contains "Rng1" and D2 has "Rng2"; and cell D3 has the formula: =correl(D1,D2). But I get an error. Have also tried using Indirect to no avail. Any help would be hugely appreciated. Thank you. =CORREL(INDIRECT(D1),INDIRECT(D2)) -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "ste...

I need to increase a number which is held in a cell by 1
How do i increase a cell which has a number in it, eg, cell a8 has 4 i want to run a If statment that looks at another cell and if true i want cell a8 to increse to 5 You need VBA. The code is simple in itself (Range("A1").Value = Range("A1").Value + 1), but what will trigger the addition to happen. -- HTH ------- Bob Phillips "Steve" <Steve@discussions.microsoft.com> wrote in message news:106609F3-2295-4FDC-8ED2-CA9E29D2FA3B@microsoft.com... > How do i increase a cell which has a number in it, eg, cell a8 has 4 > i want to run a If sta...

Need function to return numbers at beginning of text
I have a column of cells that contain an id and description in one column. I need a formula that will give me only the id. The id can be either 4 or 5 numbers. Some can be followed by a letter and some can be followed by a dash and then a number. What they all have in common is that the id is followed by a space and then the description. So, the =LEFT formula will not work. I need on that can give me all characters before the space regardless of whether that's after the first 5 characters or after 7 or 8 or however many there are before the space. Anyone know of such...

refering to a name range area as a reference value multiple times on a worksheet at different locations
i was wondering if i had posted this in the right section as it could apply to both please could you take a look at the following post http://groups.google.com/group/microsoft.public.excel.misc/browse_thread/thread/f088a5737dd26570 thank you Is there some reason you can't post your question here instead of asking us to look elsewhere? -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Blinds Nottingham" <bradrail.blinds.awnings@googlemail.com> wrote in message news:d8768e43-e035-45eb-856a-0ccdc843441d@g1g2000yqi.googlegroup...

moving to next cell using "Enter"
With Excel XP (Windows EX also) Excel seems to have a problem remembering what direction to move the focus when the Enter key is hit. Is there a fix or a way of keeping it the same? Thanks -- Joseph Meehan Dia duit Hi, Go to Tools->Options and choose Edit tab. In that choose the direction of the Enter key to the way you want. Govind. Joseph Meehan wrote: > With Excel XP (Windows EX also) Excel seems to have a problem > remembering what direction to move the focus when the Enter key is hit. Is > there a fix or a way of keeping it the same? > >...

What is IVA# in Distribution Reference and Reference?
We did an inventory adjustment and now we get a unknown amount in Debit under the Inventory account and Credit for COGS account in the Transaction Entry Zoom. We're trying to figure out where that amount came from but we couldn't zoom more than what it's currently shown. In both fields, Reference and Distribution Reference, it's shown IVA#######... that is unknown to or setup by us in anywhere, i.e. Audit Trail, Source Document. Does this amount affect the dollar in the accounts mentioned? And can anyone please help to where we could drill down the IVA details?...

protecting a group of cells not a whole worksheet
I would like to protect a group of cells and not a whole worksheet but I haven't been able to figure out how to do this. I think this would be the best way for what I am wanting to do. I have a spreadsheet that I send to 13 people. They make changes and send back to me and then I have to up-date the changes. If I could protect all the cells except for the one they enter in - then I could save the file on a shared drive and it would eliminate me up-dating. Is there a way to do this? Hi select the cells for which you want to allow entries. After this goto 'Format - cells - Protection&...

How to move cells to a another Cell?
Hello, I hope someone can help me on this. I am trying to move cells from a group into a top cell, the initial view of column A (very simplistic) of the cell looks like this: 1 2 3 4 1 2 2 2 3 3 3 1 1 2 3 What I want to do is to move 2 3 4 from the first group to the cell that contains the 1 (first entry) and continue with the rest of the cells so at the end columnA will look like this: 1 2 3 4 1 2 2 2 333 1 123 The cells vary in lenght and I have several spreasheets with LARGE amounts of data that I have to processed, so I am hoping that there is a macro (VB) that is able to do th...

Image in a cell
Is it possible to place an image in a cell? If so, can you tell me how to do it? No. Images are placed in the Drawing Layer "above" the worksheet. Cells can contain formulae or data. If you size an image to exactly cover a cell, you can set the Move and Size with Cells property (right-click on the image, choose Format Picture/Properties and select the appropriate radio button) so that the image remains "on top" of the cell. In article <6463B5FA-3D07-43B9-90EE-7A5143977C1B@microsoft.com>, "Mark" <Mark@discussions.microsoft.com> wrote: > Is ...

how do I merge two adjacent cells and keep the data from both cell
To take it a step further, I also need the formats to remain the same. ie...the left column data is Left-text-justified, and the right column is Right-text-justified. =A1 & " " & B1 -- Regards Dave Hawley www.ozgrid.com "johncaleb" <johncaleb@discussions.microsoft.com> wrote in message news:E182C58A-A577-43ED-9C90-46E58767E7CB@microsoft.com... > To take it a step further, I also need the formats to remain the same. > ie...the left column data is Left-text-justified, and the right column is > Right-text-justified. ...

more than 4 color choses for cell/colum
I an new to excess so please show me how this is to be done I would like to put a letter in a cell but all in the same colum. "A" and get a color ( dont matter what color ) "B" and get a color ( dont matter what color ) "C" and get a color ( dont matter what color ) "D" and get a color ( dont matter what color ) "E" and get a color ( dont matter what color ) thanks jladika@millenniumimagingfl.com jo -- Message posted from http://www.ExcelForum.com Hi only possible with VBA as conditional formating only supports 3 conditi...

Change Value axis to category axis
Is there a way to change places of the category (x) axis and the value (y) axis? If so, how? I am using Excel 2003. -- Ronnie You can only make this switch if you change a column chart to a bar chart. But if you want a line chart that's rotated, you can fake it: http://peltiertech.com/Excel/Charts/DotPlot.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Ronnie" <Ronnie@discussions.microsoft.com> wrote in message news:AA95159B-371E-4F1F-8C77-6B5F530BF6E5@microsoft.com... > Is there a...

macro button refers to prev. workbook?
Hi, When I create a macro in Excel, and assign a button to it, it works fine the first time, but when I re-open the workbook for a second time, and try to run the macro again, the macro seems to refer to the previous workbook...For some reason, the macro saves the name of the previous workbook... Sub test() ' Dim name As String Dim adress As String Sheets("sheet1").Select Range("A1").Select name = ActiveSheet.Range("B6").Value adress = ActiveSheet.Range("B4").Value ActiveWorkbook.SaveAs Filename:="C:\WINDOWS\Desktop\Temp...

Data Validation in cell
Hi I have a sales order form with the equipment on the second sheet as "PartNo" "Description" "Cost" selected each column of data then clicked in the cellname box and called them "PartNo" "Description" "Cost" On the sales order form I have the columns "Qty" PartNo" "Description" "Serial No" "QtyDes" "Price" Total" I have set up data validation for the description as a list with =Description what I want it to do is display the part no in the part number Column and the cost in...

Empty Cells #3
I export data from a program called OpenView Service Desk into a spreadsheet. This spreadsheet is then imported into Access and queries performed on the imported data. This data is then copied and pasted into a different spreadsheet where calculations are performed. In one particular spreadsheet there are 2 columns, one complete with data (no empty cells) and the other which may contain empty cells. I need to count the number of "empty cells" however the problem is, these cells, although they appear empty, are not. I need to but cannot find the content(s) of this cell(s). How do I fi...

deleting duplicated cells in a list via advanced autofilter?
I have a list which I would like to filter and deleted duplicate records. A1: A250= account numbers B1:""= values C1:"" = loan balances D1:"" = notes I'd like to be able to delete the duplicate account numbers but stil show the value, loan balances, and notes that belong to the accounts. Can anyone help. Thanks. Pete: -- petevan ----------------------------------------------------------------------- petevang's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2503 View this thread: http://www.excelforum.com/showthread.php?threadi...

The messaging interface has returned an unknown error. If the problem persists, start Outlook again.
Microsoft Article Q172350 sort of helps me with my Delema... I have a Windows XP SP2 with Office 2003 SP1. The client is able to Print her own Calendar but when she tries to Print a Shared Calendar she has Access to and can Read, She gets the Error: "The messaging interface has returned an unknown error. If the problem persists, start Outlook again. " I tried to do a Office repair and this did not help. I do not really want to put Office SP2 on machine as of yet... Any idea... thanks in advance... Michael Re: The messaging interface has returned an unknown error. If th...

Column and Row reference
How do i hide the column and row reference number in a sheet, when viewing the worksheet i do not to show the top and right hand side references Dave Tools>Options>View. Uncheck "row and column headers". Good idea to spend a few minutes browsing through the various Tools>Options tabs to see what else is available to toggle on/off. Gord Dibben Excel MVP On Mon, 19 Jul 2004 15:53:02 -0700, "Dave" <Dave@discussions.microsoft.com> wrote: >How do i hide the column and row reference number in a sheet, when viewing the worksheet i do not to show the top an...

How do Macro to EDIT cells?
Am using MS Excel XP (2002). All I want to do is a short macro to, in effect, press F2 (to EDIT the cell), then let's say delete the first character of the cell and change it to let's say X, like this... Before: _Mary Jones *Joe Smith %Peter Rabbbit After: XMary Jones XJoe Smith XPeter Rabbbit I just want to put the cursor on a given cell, click CNTL-A to run the macro, and have it change one cell at a time. There are reasons why I can't use the search and replace. Problem is... if I record the macro (turn it on, press the key sequences, F2, Home, Delete, X, GoDownOneCell, ...

Missing References
I had a problem today with a missing reference - Microsoft Forms 2 on a colleague's machine at work (Office 2003; VBA 6.5; XP). I've read Chip Pearson's "Missing References in VBA" which is a geat explanation about how to fix problems like this. Oddly the library didn't seem to be flagged as "missing" and was in fact present in the system32 directory but didn't show up in the list of available references. Now to the questions: I'm not clear on whether if I write code in a project that references a library which is not part of the &qu...

Which function to use? Trying to ref a cell based on another.
I'm starting to get confused by the vocabulary in Excel. I'm not necessarily looking for someone to just give me a solution to my problem. I'd just like some advice on which functions I should be reading up on. I'm still a newbie to the formulas. If Excel would let me, the closest I could express what I'm trying to do is something like this. =IF(A3=sheet2!A$:A$, sheet2!H$:H$ Of course this doesn't work b/c I don't know what I'm doing. I know this is simple to some of you gurus out there, everybody starts somewhere. To explain what I'm trying to...

Cells do not Retain Formatting After Pasting.
I have very a simple spreadsheet with lists of names, addresses, telephone numbers etc. I have preset the formatting for the telephone number column, to "text" and centre alignment. When I paste in a telephone number from Outlook Express such as 01282830756 it changes the format and I lose the first 0 and the alignment is from the right. After pasting I have to reformat to text and centre alignment. This is a real pain and time consuming. Is there any way to cure this and force Excel to retain the formatting I have set? Robert Paste the values only. Edit, Paste Special, ...

importing numbers as text
I have a .csv file containing numbers with leading zeros. When I open that file in excel, I lose all the leading zeros and I cannot switch the cell format to text to save them because they are lost immediatly upon opening the file. How do I get excel to not drop the leading zeros? You could format the cells (whole column) after you've imported it or you could rename the .csv file to .txt and you'll see the text to columns wizard--where you can specify text. caleb wrote: > > I have a .csv file containing numbers with leading zeros. > When I open that file in excel, I...