CODEWITHSUNDEEP
ropenxlsx
kpl92
kpl92

Reputation: 65

Reference to cell in another worksheet

Is there any way to reference to a cell in another worksheet in package openxlsx - insert Excel formula? I haven't found it in the package documentation. Tried to do it like:

writeData(wb,
            "Sheet1",
            "='Sheet2'!B2",
            startCol = 1,
            startRow = 1)

But then I had to double click on cell in the Excel file to show the value from the referenced cell...

Upvotes: 1

Views: 274

Answers (1)

zx8754
zx8754

Reputation: 56004

We need to use writeFormula, see example:

library(openxlsx)

wb <- createWorkbook()

addWorksheet(wb = wb, sheetName = "Sheet1")
addWorksheet(wb = wb, sheetName = "Sheet2")

# add some data on Sheet2, so we can get some value using formula in Sheet1
writeData(wb = wb, sheet = "Sheet2", x = mtcars)

writeFormula(wb, sheet = "Sheet1", x = "=Sheet2!B2",
          startCol = 1, startRow = 1)

# test: view file without saving:
openXL(wb)

Upvotes: 1

Related Questions