Hi all,
I have a table with 8 complicated formulas using area (for example B2:B50) repeatedly and when I want create new "sheet", just duplicate it. Im using the same formulas there, but for different area (for example B2:B45). How can I mass edit of 8 formulas to change area from B2:B50 to B2:B47? I cannot use only whole "B" column, because I have some tables in each sheet below. Or any "dynamic area" or something like that?
Thank you
It would help if you could provide specifics. For example, it isn't clear why you can't just use the whole column "B". If you design your document well and separate other data into other tables on the same sheet then you will find your life much easier. And generally it is a good idea to avoid a lot of complicated formulas. You can almost always break your work down into smaller pieces.
Having said that here is a Find Replace in Formulas Automator Service (Dropbox download) that will do what you describe. Select the cells with the formulas you want to change, choose the 'Find Replace in Formulas' from the Numbers > Services menu, then at the prompt enter the existing value, e.g. B2:B50 and at the second prompt the value you want to replace it with, e.g. B2:B47.
To install the service double-click the download .workflow package and if necessary give permission to install at System Preferences > Security & Privacy and make sure Automator is listed and checked at System Preferences > Security & Privacy > Accessibility. In contains a short AppleScript.
SG
It would help if you could provide specifics. For example, it isn't clear why you can't just use the whole column "B". If you design your document well and separate other data into other tables on the same sheet then you will find your life much easier. And generally it is a good idea to avoid a lot of complicated formulas. You can almost always break your work down into smaller pieces.
Having said that here is a Find Replace in Formulas Automator Service (Dropbox download) that will do what you describe. Select the cells with the formulas you want to change, choose the 'Find Replace in Formulas' from the Numbers > Services menu, then at the prompt enter the existing value, e.g. B2:B50 and at the second prompt the value you want to replace it with, e.g. B2:B47.
To install the service double-click the download .workflow package and if necessary give permission to install at System Preferences > Security & Privacy and make sure Automator is listed and checked at System Preferences > Security & Privacy > Accessibility. In contains a short AppleScript.
SG
Can you post a screenshot?
I'm not sure whether when you say 'table' you mean a Numbers 'table' or and area within a Numbers 'table'. Sheets, as you probably know, have a blank canvas (no rows and columns) on which you place one or more tables, plus charts and other objects. Tables have a grid of cells arranged in rows and columns. To see what I mean, if you haven't already, I highly recommend having a a look at the templates at File > New in your menu.
In general if you have monthly data that is in the same format each month then you will be better off keeping all that like data in one table and using Categories (Organize menu), Filters and formulas like COUNTIF and SUMIF and their cousins to pull subsets and summary statistics from that table.
But seeing specifics of what you are trying to do will make it easier for people here to advise you of the best approach.
SG
Just for imagine, sheets represent fruit (apple, orange, bear etc.). In each sheet I have tables (from top to down), each table represent month, it has 15 columns and floating number of rows for each month. Each table has "top footer" where are formulas placed and evaluated. And problem is that table/area for apple/march orange/april apple/april etc. is different but use the same logic. Any idea for redesign?
Oh, I see...
When I use separate Tables on each sheet, I can use only "B" on formulas, but when I want to create sheet with summary, it is better to have it in one Table and use formulas to filter it and statistics.
Thank you very much for your time and help.
mass edit of formulas or dynamic area?
