Numbers linking formulas to multiple cells

Question

Level 1

28 points

Numbers linking formulas to multiple cells

I have one formula for shipping that is on my master pricing sheet and I would like to link it to multiple cells on our product sheets so those individual products have the formula to calculate shipping for their individual rows. Then, as shipping rates change I only need to change the formula in the one cell on my master pricing sheet that will update shipping costs on hundreds of products. Right now I am linking back to the master pricing sheet's shipping formula by placing an "=" back to the master pricing sheet where the formula (as text) is located. This doesn't work. Many thanks for your assistance!

Mac mini, macOS 10.15

Posted on Dec 30, 2020 6:12 AM

Reply

Answer 1

Barry

Level 9

60,023 points

Dec 30, 2020 12:01 PM in response to Bill McGowan

Hi Bill,

A cell reference to a single cell on your master table will return the current result of a formula in that cell. If there are "hundreds of" cells referencing that single cell, each of those cells will get the same value from that master cell. Is that what you need?

What is the formula (i words) you are using for calculating these shipping costs? When shipping rates change, what data in that formula changes?

Regards,

Barry

Reply

Answer 2

Barry

Level 9

60,023 points

Dec 30, 2020 12:36 PM in response to Bill McGowan

Same formula, but replace 20 with a reference to the cell on the master table containing the minimum charge. Consider also replacing the 10 with a cell reference to a cell on the master table containing the current 'rate per (amount listed in A2).

Calculations need to be done for individual items but the two shipping rate data needed for those calculations can be recorded in a single location, referenced by each copy of the formula.

Regards,

Barry

Reply

Answer 3

Barry

Level 9

60,023 points

Dec 30, 2020 1:51 PM in response to Bill McGowan

Hi Bill,

Happy to help.

Barry

Reply

Answer 4

Bill McGowan Author

Level 1

28 points

Dec 30, 2020 12:18 PM in response to Barry

The formula is actually the one that you gave me yesterday to calculate shipping rates:

IF(A2=0,0,MAX(20,A2*10)

I need to paste this formula in about 200 different product tables, then copy it down about eight rows (in each of the tables). Today, when I started this very long process, I thought that I could put the formula on my master price sheet and link it to the hundreds of shipping cost cells on my many other sheets, then when I need to change shipping rates I would only need to edit the formula on my master price sheet.

Editing the formula would be to change the let's say "20" to "25" as our new minimum shipping charge.

Many thanks for your time and support!

Reply

Answer 5

Bill McGowan Author

Level 1

28 points

Dec 30, 2020 1:26 PM in response to Barry

Perfect! It works flawlessly! Barry, you have been my Numbers savior. Can I send you a Starbucks card?

Reply

Numbers linking formulas to multiple cells

Similar questions