Forum Discussion

Kaiyuan1860's avatar
Kaiyuan1860
Copper Contributor
May 13, 2024

automatically update the single cell value of one sheet when I drag down column of other sheet

Dear experts:

 

I wish when I drag down the column Q at the sheet 1, the value of the single cell B5 at sheet 2 (sheet name:c_calculated) will be automatically updated to the repsected row value from the column N at shee 1.

 

For example, if I have drag down to row 7 of column Q at the sheet 1, the value at cell B5 of sheet 2 will be shifted to the value from row 7 of column N at the sheet 1.

 

If I have drag down to row 19 of column Q at the sheet 1, the value at cell B5 of sheet 2 will be shifted to the value from row 19 of column N at the sheet 1.

 

Could our friends give me guideline on this?

 

My file can be downloaded throughed the following link, after it is guided to the link page, just click file->download->microsoft excel (located at the top left corner of the page)

https://docs.google.com/spreadsheets/d/1elsI_WsGmeaJ-JRJgzjnNBgekgPGYAZA/edit?usp=sharing&ouid=104482621481585823648&rtpof=true&sd=true

 

Kind Regards

excel
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    May 14, 2024

    Kaiyuan1860 

     

    For Excel

    =INDIRECT("Sheet1!N" & ROW())

     

    Google Sheets, Google Drive, and OneDrive do not support the INDIRECT function in the same way as Excel. While Google Sheets does support the INDIRECT function, the syntax may differ slightly, and it does not support direct cell references across different sheets in the same way Excel does.

     

    For Googlesheets

    =IMPORTRANGE("Spreadsheet_URL", "Sheet1!N5:N")

     

    Hope this will help you.

Resources