Using Names for cell references and would like to streamline things

[AmyDetjen]

Hello!

I'm pulling a ton of sales data together for my small company. I'm using the NAMES function (mostly because it's the only way I know how to do this efficiently). I have coded all the appropriate sheets with the names, such as JAN23OBMX15 where JAN23 is the month/year and OBMX15 is the SKU for the product. Then I can call up that date/product combo anywhere I need it.

I coded approximately 100 products for 36 months, using copy/paste and keyboard shortcuts to make it as fast as possible. So now I'm pulling the data together (the fun part) and here's the problem. In the example below, where I have "n/a" in a field, it indicates that none of that exact product were sold in that month. No sales in that month means I did not create a NAME for that month/product combination, so it comes in as an error (see box under Short, 2BLMX150). That's how it should work. 

Here's my question. Is there any way to get Excel to ignore the errors without me having to manually change each error to "n/a". Maybe with Conditional Formatting? It certainly isn't difficult, but very boring. Plus, if I delete the value of the cell (for example: JAN21MAQ5PK*5) and then double-check everything and that product actually DOES exist, it won't be called in, because I replaced the tag with "n/a". I could even ignore the errors in the table if the ADD function at the bottom of the columns still worked, but it won't, since there's an error in the table.

Any ideas?

Thanks in advance, you smart people!

[#2]

@AmyDetjen 

Your description of using named variables sounds like more maintenance effort than is necessary, but without details (like a sample workbook), I can't offer recommendations on that.

It seems that you are replacing Excel's #N/A errors with text "n/a" values manually.  But that change of value can probably be done in the formulas, using Excel's ISNA function (which, unlike the ISERROR function, is specific to this type of error).  So your updated formulas could be like:

=LET( result, <your_current_calculations>,
   IF( ISNA(result), "n/a", result )
)

[#3]

@AmyDetjen 

To stick to your original question:

"Here's my question. Is there any way to get Excel to ignore the errors without me having to manually change each error to "n/a"."

Use the IFERROR function like this:

=IFERROR( your_formula, "n/a")

Now, having solved that, I really wonder why you chose this way of naming each month/product combination. If you have all your sales information in a single table you can easily extract, filter and/or summarise it by product, by customer, over time, whatever. You could use pivot tables, Power Query, Power Pivot or other built-in functions. Everything you do now seems very manual.

[#4]

@AmyDetjen 

My immediate reaction is don't do it like that.  In their paper

Microsoft Word - Ruth McKeever_ARXIV.doc (eusprig.org)

the authors showed that this type of naming convention does not reduce the errors that characterise almost all spreadsheets.  

I need to see what your Names actually refer to, but this type of name which encrypt distinct items of information is not a good idea.  It is better to apply Names at a level that applies to the range that contains all such data and then use formula (XLOOKUP/FILTER or  INDEX/MATCH for legacy Excel) to pick out the specific values required from the range.  Aggregations such as subtotals and counts become much easier if data is identified at a more abstract level.

[#5]

@SnowMan55 

Hi Snowman, Thanks for the good advice. I'm sure that the way I'm doing it is not the most efficient, but as I said, it's the only way I know how. I'm not a programmer, I'm a retired editor who answers phones part time for this small company and have nothing to do but wait for the phone to ring. So I volunteered for this project. The fact that it's time consuming and dull is nobody's fault but my own.

Now I will use my time to better advantage and learn about ISNA and whatever the next suggestion is! I needed a launch point for my education, and you guys got me there. Thank you so much!

Amy

[#6]

Hi Peter, Thanks for your response. You gave me just what I needed which was a place to start researching a more efficient way to do this. I only know the basics of Excel and this is like a hobby task for me... nobody minds if it takes me forever (except maybe me).

My research starts today! Thanks again, Amy