Cell text contents dependant on other cell text contents

Question

Level 1

4 points

Cell text contents dependant on other cell text contents

Hi,

I am a landscaper and have built a numbers file to help me cost projects.

There are lots of variables when costing, I wondered if it would be possible to insert a formula that inputs pre-set text or numbers in a cell dependant on text in another cell.

For example, If a certain type of paving is used, let's say porcelain, I could type that into a cell and then all the variables that affect the cost of installation would automatically change to suit that product, eg product cost, cutting difficulty, additional adhesive requirements etc.

If I could pre-set this for all materials that are routinely used I can save myself a lot of time down the line!

Is this possible?

Thanks for your time

iMac 27″, macOS 10.13

Posted on May 6, 2021 11:46 AM

Reply

Answer 1

Top-ranking reply

Barry

Level 9

59,951 points

May 6, 2021 12:04 PM in response to RobertWMEXT

Hi Robert,

Should be possible, using a filter or a set of lookup formulas and lookup table(s).

As with most situations, the devil is in the details.

To start, you'll need to assign categories to the variables affecting the costs so that you can design an organized costs table to provide the text that is to be inserted into each location on your costing table when a specific product is chosen.

Provided with that information, and a screen shot showing how you want it displayed on the table should lead to a solution.

Regards,

Barry

Reply

Answer 2

Badunit

Level 7

27,982 points

May 6, 2021 4:51 PM in response to RobertWMEXT

The thing that crossed my mind was the costing formulas that go along with the different variables. It should be easy to do a lookup to put text in cells to identify the variables but the numbers you enter for those variables will go into a formula to calculate the cost for that product/service. If that formula is different for different categories of product/service and with their different sets of variables, we'll have to work out how to apply different formulas. That should be an interesting task. I am wondering if that could be done efficiently with an IFS statement in each cell that has a formula. The IFS statement would have all the possible formulas for that cell. Thinking out loud.

Can you provide a few examples of the service/product category and the variables and formulas that go along with them to calculate the cost?

Reply

Cell text contents dependant on other cell text contents

Similar questions