Find and Replace to edit references

Hi!

I am trying to edit the three cross-sheet references in all the columns from the master sheet of 2018 to master sheet of 2019. Since I need to repeat it for almost 1200 cells.

Is there a way to bulk edit like Find and Replace or something else? Even for selected cells will make it so much easier!

the original is :

=SUMIFS({DSMONEY18}, {MonthDS18}, @cell = 4, {DSVEND18}, @cell = "Vendor")

Replace with:

=SUMIFS({DSMONEY19}, {MonthDS19}, @cell = 4, {DSVEND19}, @cell = "Vendor")

Screen Shot 2018-12-12 at 12.13.16 PM.png

Find more posts tagged with

Formulas

Marketing

Comments

Paul Newcome

If you open up the formula as if to edit it then just click inside the range reference, you should see that Reference Another Sheet in the helper box has changed to Edit Reference. Once you edit that reference, it should update every occurrence in the sheet.

Emory

Hello,

What if you made a mistake when doing the reference for only certain cells?

=IFERROR(AVG(COLLECT({Stability Testing Corrections1}, {Stability Testing Corrections1}, @cell < 30, {Stability Correction Assigned1}, $TeamMember@row, {RT1}, @cell <> "Parent", {Stability Testing Corrections1}, @cell <> "", {Stability Submitted For Review Date1}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))), "")

In this case for example, {Stability Correction Assigned1} is used and is correct. But in many cells, I have mistakenly put this reference and it should have been {Stability Review Assigned1}

=IFERROR(AVG(COLLECT({Stability Review Delta1}, {Stability Review Delta1}, @cell < 30,{Stability Correction Assigned1}, $TeamMember@row, {RT1}, @cell <> "Parent", {Stability Review Delta1}, @cell <> "", {Testing Scheduled Date1}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))), "")

Is there a way to edit the reference in a highlighted section of cells? A Find/Replace for formulas would work if this were a tool in Smartsheet.

I did try exporting to excel, but the formulas do not transfer in the export sadly.

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…