Identifying formulas vs text in a column

NitaP

Is there some way of highlighting which cells in a column contains formulas and which contain text or numerals?

Paul Newcome

Unfortunately this would be a manual process. What is the formula vs what is the text/number structure? Are you able to post some screenshots for reference?

NitaP

In one column I have this formula in most of the cells:

=INDEX({RM Low Stock Levels}, MATCH([Product Name]@row, {RM Low Stock Raw Material}, 0))

However, in some instances, there is no data to match from the other spreadsheet, so I have typed in a numeral because I need a number in the cell for a calculation in another column.

I would like some way to highlight the cells in which I've typed in numerals so I can go back later and put the formula back into the cell when there is a match.

Paul Newcome

Try putting the formula in every cell but wrap it in an IFERROR to output your number (or even a blank) so that there is no error present which then allow your other calculations to run.

=IFERROR(INDEX({RM Low Stock Levels}, MATCH([Product Name]@row, {RM Low Stock Raw Material}, 0)), number_of_choice)

NitaP

Thank you for the idea, but I don't think it's what I need. I don't just need the calculations to work, I need to identify which cells I've overwritten with a manual number so that later I can put the formula back into the cell. I'm thinking I may need to add a column with a checkbox to identify manual entries unless someone has a better idea.

Paul Newcome

Why are you overwriting with a number though and is it always the same number?

NitaP

The column is recording the minimum number of units that should be kept in inventory.

It's looking up that information from another spreadsheet with formulas that require raw materials.

However, I haven't yet put all of my formulas into the RM Low Stock Levels spreadsheet. It's a time-consuming process. So, for some raw materials the "Low Stock Amount" can't be calculated, but I still need to indicate how much to keep in stock. I've been guessing at what the minimum amount should be for those raw materials and putting in numbers to overwrite the formula in that column. And no, the minimum is not the same for each raw material.

Once I finalise a formula for production it will be included on the RM Low Stock Levels spreadsheet and then the formula can be used to calculate the low stock amount on the RM Purchases spreadsheet. I would need to be able to easily identify which raw materials need to be changed from a temporary number back to the formula.

Paul Newcome

Ah. Ok. I see now. So cells that do not have a formula in them will not have the little blue triangle on the right side of the cell. We can't really leverage that for additional highlighting, but it can be used as an indicator.

Otherwise you would need to include another manual step.

NitaP

Yes, I see the tiny blue indicator, and that's helpful, but it's not as visible as I'd like. So, I'll add the check box in a separate column and then use that with conditional formatting to get my attention. Thanks for your help to think this through.

Paul Newcome

Sorry I wasn't able to provide a better solution. Hopefully you'll be able to get the rest of those low stock amounts in sooner rather than later and not have to fight with manual vs formula.

NitaP

Yes, that's my goal. You helped me think through the options, so that helped me solve the problem.