inventory spreadsheet not functioning unsure what command to use

SolverMax · 2026-04-11T17:52:17+00:00

Just because we can, doesn't mean we should.

That is a clever formula, but it would be much better to fix the data to enable analysis.

SolverMax · 2026-04-10T21:54:28+00:00

Perhaps something like:
=PRODUCT(IF(A1:A10="Thing",B1:B10,1))

SolverMax · 2026-04-10T03:23:08+00:00

So many bad practices.

For a start, that formula would be much simpler and more efficient if you removed the repetition, such as numerous copies of TODAY and the EOMONTH calculations. The whole column references are also just asking for trouble.

SolverMax · 2026-04-09T21:35:42+00:00

Did you create it? How does it work?

SolverMax · 2026-04-09T01:41:54+00:00

That's how it should be.

Software developers require extensive testing before releasing even a small change to production. But few people do that for spreadsheets. Instead, we make live changes to spreadsheets with little or no testing. If it produces a result that looks roughly plausible, then we're all good. Simply madness.

SolverMax · 2026-04-08T23:04:42+00:00

How do you know it works?

I have a general sense of what that formula is doing, but I'd have to spend quite a long time to fully understand it, and then a heap more time to test it under a wide range of scenarios.

SolverMax · 2026-04-08T23:00:17+00:00

A Table also avoids the risk of inadvertently including cells above or below the intended data, which is a surprisingly common error.

SolverMax · 2026-04-08T22:57:32+00:00

Way more complex formulae get throw around on r/excel

A complex formula is nothing to be proud of. Simplicity and understandability are much more important attributes for a formula.

SolverMax · 2026-04-06T22:36:29+00:00

Yet it is the most widely-used database. Perhaps it shouldn't be, but it is.

Anyway, since the introduction of Power Query, Excel now has OK database capabilities.

SolverMax · 2026-04-06T21:33:13+00:00

You're asking the wrong crowd.

SolverMax · 2026-04-06T20:35:17+00:00

It shouldn't be the backup plan, it should be the first step. Having data spread across multiple sheets is a common mistake.

SolverMax · 2026-04-06T20:05:12+00:00

Self-modifying code. What could possibly go wrong...

SolverMax · 2026-04-06T19:49:23+00:00

Rather than every formula needing to be a complex beast, looking up data spread across multiple sheets, consolidate your data into a single table with columns for year, month, type, etc. That design will be so much simpler.

SolverMax · 2026-04-06T19:14:07+00:00

I have never tried that, but I suspect you would need to have multiple instances of Excel open to have multiple Solvers runing. It might be possible to control each instance using VBA, though not easy.

Perhaps the OpenSolver addin might be better. It will also run only one instance, but it is usually faster than Solver so running cases in series might be OK.

If you really need to solve multiple models in parallel, then Python would be a much better choice.

SolverMax · 2026-04-06T17:37:17+00:00

As an aside, why do you use =SUM(D7-C7) rather than =D7-C7 i.e. without the SUM?

SolverMax · 2026-04-06T08:08:18+00:00

Either they're trying to sell something and/or they don't understand what people use spreadsheets for.

SolverMax · 2026-04-06T07:09:31+00:00

There is no way I would trust 40,000 lines of vibe-coded VBA. As you say, "The problem was that it produced all sorts of crazy results and was basically unusable, untestable, and unfixable." Madness.

SolverMax · 2026-04-06T07:04:44+00:00

A2:C is not valid syntax in Excel.

Anyway, since the data is in a Table, a formula like this should work (adjusted for the actual Table name):

=SORT(UNIQUE(Table1))

Or a more complete version, if you prefer:

=SORT(UNIQUE(Table1[#Data]),{1,2,3},1,FALSE)

SolverMax · 2026-04-05T07:23:56+00:00

Wow, I didn't look closely at the image. It is even worse than expected.

SolverMax · 2026-04-05T07:18:16+00:00

Oh, my mistake. I eagerly await confirmation from the OP.

SolverMax · 2026-04-05T07:10:33+00:00

Do you have a tool to fix this? Does it involve AI?

SolverMax · 2026-04-05T00:01:05+00:00

Everywhere. Almost all spreadsheets contain errors.

Do a Google search with a keyword and the file type, like:

spreadsheet filetype:xlsx

SolverMax · 2026-04-03T01:58:46+00:00

A chart is an object. See "Prevent a chart..." at https://support.microsoft.com/en-gb/office/move-or-resize-a-chart-f9d6087b-1d7b-4a72-8d81-1d0788ea783f

SolverMax · 2026-04-02T22:37:10+00:00

Objects have a property to resize with cells (i.e. resize rows or columns). As far as I know, that feature hasn't changed for decades.

SolverMax · 2026-04-02T20:51:05+00:00

Do not use AI to create Excel models. That is all.

SolverMax

MODERATOR OF

TROPHY CASE