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

Copper Contributor

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=10448...

 

Kind Regards

1 Reply

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