Edit multiple formulas faster...

Question

Level 1

4 points

Edit multiple formulas faster...

Man editing formulas in multiple cells is very tedious. Is there. way to copy/paste faster especially when cell are similar?

I have multiple cells...

Table 1 Pivot::$B$8
Table 1 Pivot::$B$9
Table 1 Pivot::$B$10
Table 1 Pivot::$B$11
...etc

and i want to wrap them with, say "IFERROR"....

IFERROR(Table 1 Pivot::$B$8,"NO TRADE")
IFERROR(Table 1 Pivot::$B$9,"NO TRADE")
IFERROR(Table 1 Pivot::$B$10,"NO TRADE")
IFERROR(Table 1 Pivot::$B$11,"NO TRADE")

Doing this over and over again sucks. Any ideas?

Thanks.

MacBook Pro 15″, macOS 10.15

Posted on May 23, 2024 1:22 PM

Reply

Answer 1

Badunit

Level 7

26,656 points

May 23, 2024 7:56 PM in response to PixelCantina

Numbers really locks down the cell references to pivot table cells. There is no way to get rid of those $'s to make the formulas fillable.

I am trying to figure out under what instance there would be an error in a pivot table or in a formula that references a pivot table. The once case I know of is if a formula references a cell of the pivot table and that cell got removed when the pivot table was refreshed. Interestingly, the formula will come back to life if that cell gets reinstated in another refresh (though I wouldn't count on that). So... I am assuming to the left of these formula is a column of stock ticker symbols and some of them may not be in the pivot table. If that is the case, you can use XLOOKUP to get the column B data and handle the "no trade" at the same time and not rely on cell reference errors fixing themselves.

=XLOOKUP(A,Table 1::Pivot::$A,Table 1::Pivot::$B,"NO TRADE",0)

This formula is fillable down.

If that isn't what's going on, maybe there is a better/different answer.

Reply

Answer 2

PixelCantina Author

Level 1

4 points

May 23, 2024 9:18 PM in response to Badunit

Yes i'm referencing a cells in the pivot table that are not 'present'. In the pivot table there are dates that are referenced, but if there is no date in the main table, then the pivot table does not show a cell for it. I wish it would just show an empty cell either way. That's why i have the "iferror" there. Because when there is data, then it will show.

But again my main problem here is editing the formulas more efficiently than copy/paste each cell. So imagine those cells i mentioned...

"IFERROR(Table 1 Pivot::$B$8,"NO TRADE")

if i want to change "NO TRADE" to something else like "NONE", then i have to open each cell and paste that into it. See, all these cell are similar enough to be able to paste into multiple somehow. You can't "Find&Replace" in a formula.

It would be awesome to be able to have multiple cells show formulas simultaneously so you can edit and compare all at once!🤔

Reply

Answer 3

Badunit

Level 7

26,656 points

May 24, 2024 10:55 AM in response to PixelCantina

Without seeing your spreadsheet I'm just taking stabs at what it looks like and possible solutions to the problem.

Is there a column of dates next to these formulas? I am assuming so because otherwise "no trade" seemingly has no purpose without a date to associate it with. If there is a column of dates, you can use XLOOKUP as I discussed in my earlier reply. It is a drag-fillable formula (vertically) so you will only have to make a change to one formula then drag it to the rest of the column.

I am not that well versed on pivot tables but it appears to me that the only way to create a reference to a cell in a pivot table is for that cell to be present. After that, you can delete the data and refresh the pivot table, which removes the row/cell from the pivot table and the reference becomes #REF. Later you can put the data back, refresh the pivot table, and the reference magically fixes itself. If this is what you are doing, I don't think I would rely on it. Plus, the only way to edit a formula like that is to first put the data back into the pivot table so the cell shows back up and fixes the reference.

There is a shortcut (Shortcut App) that will do find/replace in formulas so you can replace "NO TRADE" with "NONE" in multiple formulas but it won't solve your original problem of incrementing the row numbers in cell references.

Reply

Edit multiple formulas faster...

Similar questions