Forum Discussion
Solved
Office Scripts in Excel: Can I have a button in worksheet 1 that updates a cell in worksheet 2?
This may be trivial, this may be impossible. I was only made aware of the existence of Office Scripts yesterday and I really have no idea of their capability. I was trying to look through some samp...
Copying a value from one cell to another is quite simple. This copies the value of cell A1 on Sheet1 to cell E1 on Sheet2:
function main(workbook: ExcelScript.Workbook) { workbook.getWorksheet("Sheet2").getRange("E1").setValue(workbook.getWorksheet("Sheet1").getRange("A1").getValue()); }
Now, how to do that if I don't know E1 is E1?
let's say I have two columns on Sheet2: ID | Data
1 | This
2 | That
3 | The other
And I have another cell that references which ID we're working with (let's say $F$4).
How do I reference the correct cell (E1 in your code) so that it points to the row in Sheet 2 where the first column (ID) matches the value shown in $F$4?
Thank you.
You could record a script where you do a Find for an ID in just column A of Sheet2. That should give you code you can adapt to your needs
- I had thought you try that, but a) when I used find, the macro stopped recording and b) find what? Find require that I know what I'm searching for. "Adapt to your needs" is the problem I'm having.
bcsteeve Perhaps this gets you started?
let ws:ExcelScript.Worksheet = workbook.getWorksheet("Sheet1"); let rng: ExcelScript.Range = ws.getRange("A:A") let fnd: ExcelScript.Range = rng.find("2", {completeMatch: true, matchCase: false, searchDirection: ExcelScript.SearchDirection.forward}); console.log(fnd.getOffsetRange(0, 1).getValue());Replace the "2" with whatever string it needs to look for.