Forum Discussion

GSM101's avatar
GSM101
Copper Contributor
May 22, 2023

Excel absolute cell

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"....
excel
Formulas and Functions
HansVogelaar's avatar
HansVogelaar
MVP
May 22, 2023

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.

  • GSM101's avatar
    GSM101
    Copper Contributor
    May 23, 2023

    HansVogelaar  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.

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      May 23, 2023

      GSM101 

      Sheet names with spaces must be enclosed in single quotes:

       

      ='TTT Sheet uno'!Data

      • GSM101's avatar
        GSM101
        Copper Contributor
        May 23, 2023

        HansVogelaar  I tried..


        what I'm doing wrong?

Resources