Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

to find-and-replace within a formula

Numbers 3.5.3

OS X Yosemite 10.10.5



Hi all: My monthly payroll processing is hobbled by the current strain of Numbers not providing a search-and-replace function that applies to formulas.


Here's a typical formula from my payroll spreadsheet:

=SUM(E5,July 2015::'pay stub - Marvin'::F5)


Now that it's July, I need to change each of approximately 60 within-formula instances of July 2015 in my payroll spreadsheet to August 2015.


Is this something apple scriptable or Automator-able? Alternatively, could I use a variable where "July 2015" sits in each formula and enter that variable's value one time per month (each payroll month is its own sheet) and update each month that way?


Up to this point I've been exporting to Numbers '09, applying search-and-replace-within-formula there, then returning the file to modern Numbers 3. It'd be great to improve my process and obviate these extra steps.


Thank you.

MacBook Air, OS X Yosemite (10.10.5)

Posted on Aug 26, 2015 2:18 PM

Reply
Question marked as Best reply

Posted on Aug 26, 2015 2:58 PM

Hi boris,


You are working a lot harder than you need to. If I am understanding correctly you want something like this:User uploaded file

This formula is summing E1 in this table with the cell F5 in the table "pay stub - Marvin" in the sheet "July 2015".

To change the sheet, change the entry in A1. To change the table, change the entry in B1.

With a popup in B1 for your employees you would not be doing so much typing either.


Does this address your situation?


quinn

3 replies
Question marked as Best reply

Aug 26, 2015 2:58 PM in response to boris parsley

Hi boris,


You are working a lot harder than you need to. If I am understanding correctly you want something like this:User uploaded file

This formula is summing E1 in this table with the cell F5 in the table "pay stub - Marvin" in the sheet "July 2015".

To change the sheet, change the entry in A1. To change the table, change the entry in B1.

With a popup in B1 for your employees you would not be doing so much typing either.


Does this address your situation?


quinn

Aug 30, 2015 6:12 PM in response to t quinn

Thank you for the reply, Quinn, and right off the bat I can grant entirely that I am working harder than I have to 😁


INDIRECT function has looked previously promising, but I could not implement due to an inability to wrap my head around the correct syntax.

I so appreciate your help and specific example, now let me go see if I can make your effort worthwhile.


Edit: Nailed it, quinn. Your assistance has made my monthly payroll – and life – a whole lot easier.

to find-and-replace within a formula

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.