Create a Name Range based on Values on certain cells

[Jimmy3D]

Hello Everyone/Anyone
First I’m new to working with spreadsheets so please forgive me if I don’t use all the right terms to explain what I’m trying to do. Thanks.
I have a Inventory List of a lot of product that I have purchased for my 3d Printing hobby. On one of my sheets I list all the Filament I have brought with a cell that has the amount of filament left. What I’m trying to do is create a ‘Name Range List’ of only all the rolls of Filament that are not 0 (Empty). I know how to create a ‘Name Range’ of All the filaments but I want to exclude the ones that are not usable. I couldn’t find a WHERE option in all the Functions that are listed. Thank you

This is my current Name Range: OFFSET($Filaments.$A$4,0,0,COUNTA($Filaments.$A:$A)-1)

If you need more details please let me know.

[Villeroy]

@Jimmy3D

I couldn’t find a WHERE option in all the Functions that are listed.

A spreadsheet is not a database.

@Jimmy3D

OFFSET($Filaments.$A$4,0,0,COUNTA($Filaments.$A:$A)-1)

If something makes you think, you should mis-use a spreadsheet as a database, you should keep it smart and as simple as possible. It is easy to create a spreadsheet, but using it is a lot more difficult.

[Wanderer]

@Jimmy3D

but I want to exclude the ones that are not usable. I couldn’t find a WHERE option in all the Functions that are listed.

You can redirect the output of an (advanced) filter to another (named) area. This may fit your needs…

[JohnSUN]

Hi @Jimmy3D and welcome!
It’s a pity that you didn’t describe your problem in enough detail - you didn’t specify which version of office you’re using and in which column of your list the amount of the remaining thread is.
If you have a version of Calc that already knows the FILTER() function, and the amount is specified, for example, in the fifth column of the list, in column E, then the formula for the named range can be like this:

FILTER(OFFSET($Filaments.$A$4;0;0;COUNTA($Filaments.$A:$A)-1;5) ;OFFSET($Filaments.$A$4;0;4;COUNTA($Filaments.$A:$A)-1;1) )

Yes, it looks complicated, but it works.
If you are still forced to use the old Calc (for whatever reason), then perhaps you will find it useful to look at this topic - that problem is very similar to yours.

How to create "Shopping List" with markable Items in Sheet 1 and Final List (without Row Gaps) in Sheet 2?

Assumed we have this simple kind of table in sheet 1: [screenshot1] Now I want to automatically create a new table in sheet 2 where only the items/their rows marked with an “x” are contained. So when creating a shopping list, I just need to write an “x” to the desired articles within sheet 1 and only those articles will get forwarded into sheet 2. Of course, this would be possible with a simple IF(B3="x",C3,"") request, but in this case there are several empty rows between shopping items in sh…

My solution and the solution proposed by @Villeroy in that topic can be useful.

[Villeroy]

With the entire-column-A:A approach you’ve got to make sure that no data will ever get into the range below the actual list range.
Personally, I prefer separated lists expanding by insertion of cells and shrinking by deletion of cells.
list keeping.ods (80.0 KB)