Excel absolute cell

[GSM101]

Hi,
I'm trying to lock the cell "4" so graph would retrieve data from that specific cell all the time!
However, if add a row the formula in the series values is changing and "$A$4" becoming "$A$5".
Please see the attached images for more details. 

How would I lock the cell and keep the data only from "A4" ?
Thanks in advance.

[#2]

@GSM101 

On the Formulas tab of the ribbon, in the Defined Names group, click Define Name.

Enter a name consisting of a single word in the Name box, e.g. Data.

In the Refers to box, enter the formula =INDIRECT("Sheet1!A4")

Click OK.

Click anywhere on the chart.

On the Chart Design group of the ribbon, in the Data group, click Select Data.

Select the series, under Legend Entries (Series), then click Edit.

Clear the box, then enter the formula =Sheet1!Data (using the name that you specified earlier).

Click OK, then click OK to close the Select Data Source dialog.

[#3]

@Hans Vogelaar  Thanks for your reply!
You know what the problem is? 

I defined the name, it went without error.. but, when I'm trying to select data from graph and type: 
=TTT Sheet uno!Data  it gives me the error above.

[#4]

@GSM101 

Sheet names with spaces must be enclosed in single quotes:

='TTT Sheet uno'!Data

[#5]

@Hans Vogelaar  I tried..

what I'm doing wrong?

[#6]

@GSM101 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Alternatively, you can attach it to a private message to me. Thanks in advance.

[#7]

I did send you a private message.

[#8]

@GSM101 

See the attached workbook.

You can view the definition of the defined names in Formulas > Name Manager.

You can view the definition of the series by clicking on the chart, clicking 'Select Data' on the Chart Design tab of the ribbon, then selecting a series and clicking Edit.

[#9]

I managed to enter the formula but, it gives me 0 in return on the graph. I mean when insert a new row and put a new number instead of 66 for example it gives me 0..

[#10]

@GSM101 

I inserted a new row in row 3 on the QQQ Hold In sheet, shifting the value 5 down, then entered 80 in A3:

The chart on the Main sheet then looked like this:

Next, I inserted a new row in row 3 on the WWW Hold In sheet, shifting the value 28 down, then entered 5 in A3.

The chart now looked like this:

So it works as intended...

[#11]

It does, for me half is working half is showing 0 value... Don't know why.

[#12]

@GSM101 

Check the definition of the named ranges. Are you sure that they point to the correct cells?

[#13]

Yes, everything looks correct because it's not giving me the error.
However, it's reading the value but instead of actual number, is returning 0. What the problem might be??

[#14]

@GSM101 

I'm sorry, but I have no idea. As I have shown, it works correctly for me.

Does the workbook that I attached exhibit the problem for you?

[#15]

Yes, I took it as an example and defined the name in my workbook but, from all of them just one work correctly, the rest return value 0. I have to mention that I have in graph around 12 data tables, don't know if this matter.

[#16]

@GSM101 

So the problem occurs in your workbook, but does it occur in the version that I attached (without modification)?

[#17]

Yes, in your workbook looks functional, thank you for that. When I try to set the formula in other excel document it gives me no value in return... Only one gives me the right value, the rest 0 ...

[#18]

@GSM101 

If possible, I'd like to see a copy of the 'real' workbook...

[#19]

I've sent you a private message.

[#20]

@GSM101 

I'm looking at it now.