Updating cells with formulas
The [highlight txt=’update cell formula’] instruction allows a user to update a range of cells with a particular formula. Only one formula can be configured into the instruction. However, the instruction, when run, would enter the formula into the cell at the start of the range and then ‘drag’ that formula down to end of the range (as you would do in Excel itself).[message type=”warning”]For the purposes of this article we are going to use a simple and fictitious Excel file. It has already been uploaded into a data step. You will want to replace this file with one of your own.[/message]
Before we get started
Please further create a ‘Manipulate a file’ or ‘Multiple file manipulations’ action step so that you can add and use instructions.
The target file that we are going to use resembles the following image.
1. Create an ‘Update cell formula’ instruction.
2. Link the file named “Target” as the target file.
3. Use Sheet1 from the target file.
4. Use an Advanced Range from cell B1 to cell B2.
5. Type “=A1-1” into the Formula field.
5. Save and run the step.
6. After the step finishes running, download and open the target file. As you can see, the cell at the start of the range was updated with the formula specified, and the formula was dragged down to the end of the range: