Forum Discussion

ArchieSmith's avatar
ArchieSmith
Copper Contributor
Dec 22, 2022

Office Script Indirect referencing

Hi everyone, 

 

I am trying to copy values from one cell to another using Office Scripts (which uses TypeScript), however these cells may change in row number and are therefore not fixed. 

 

Does anyone know a way that you can use the contents of one (fixed location) cell to obtain a location of another (non-fixed location) cell? this would be similar to how the 'INDIRECT()' Function works in excel. 

 

I have managed to make a formula in excel that outputs the non-fixed cells' grid locations with this formula:

For example this outputs 'H42' and will change if more rows are added.

I then want to use this in my copy paste script and this is the best way I thought it would work:

 

 

(the value of cell H42 which is displayed in A1 is going to be copied to cell M42 which is displayed in A2)

(ws has been defined earlier in the worksheet as the active worksheet)

 

However this doesn't work and the error box says the following:

 

If anyone knows how I would be able to do this I would be very grateful!

 

Thanks, 

 

Archie

 

excel
Office Scripts
  • rzaneti's avatar
    rzaneti
    Iron Contributor
    Jan 20, 2023
    Hi Archie,

    If I understood properly, you can achieve this result with two lines of code:

    let value_to_paste = ws.getRange(String(box_hval)).getValue() //assign the value from cell H42, referenced in A1 to variable value_to_paste
    ws.getRange(String(box_mval)).setValue(value_to_paste) //paste the value from value_to_paste in cell M42, referenced in A2

    You just replace the "ws.getRange(box_mval).copyFrom..." line for it.

    Let me know if I can help you with something else 🙂

Resources