Sorting Named Cells

Discuss the spreadsheet application

Sorting Named Cells

Postby Chef Silverman » Sun Nov 24, 2019 5:31 am

Hi folks,

Sorry if this has been answered in the past but I did try to search it and came up short.

I am a chef and use OOCalc for ordering, inventory and recipe costing. I have a master sheet that breaks down an ordering unit (i.e. case) into inventory units (i.e. bags) and then the inventory units into recipe units (i.e. ounces). The cell for a recipe unit for any ingredient is given a name such as Blue_Cheese or Chicken_Breast. Then when I go to cost out a recipe I can just have a separate sheet where it's basically 5*Chicken_Breast instead of looking up the correct cell with the price of chicken breast. That's all fine and good.

My problem is when a new item is entered between Blue_Cheese and Chicken_Breast. Let's say I add Calamari into the inventory and keep it alphabetical. If the named cells were A10 = "Blue_Cheese" and B10 = "Chicken_Breast" then inserting a row for the calamari (or adding calamari at the end and then sorting the columns into alphabetical order) leaves me with the problem of B1 is now Calamari but the name of cell B10 is still "Chicken_Breast".

Is there a way to have the names of the cells move along with the data inside of them? In my quick example above, my sort/insert leaves me with A1 = Blue Cheese, A10 = "Blue_Cheese"; B1 = Calamari, B10 = "Chicken_Breast"; C1 = Chicken Breast, C10 = "". I'd like the name "Chicken_Breast" to shift to C10 and then all I need to do is add a new name for B10 as "Calamari".

Thanks for any help in advance!

-Chef Silverman
Open Office 4.1.5 on Windows 10
Chef Silverman
 
Posts: 1
Joined: Sun Nov 24, 2019 4:43 am

Re: Sorting Named Cells

Postby RusselB » Sun Nov 24, 2019 6:18 am

Welcome to the Forums.
I'm having difficulty understanding how you have your spreadsheet set up.
Do you have Bliue_Cheese in A10 or do you have the cell A10 given a range name of Blue_Cheese... or both?
If you have an entry in A10 and another in B10, then inserting a row will make no difference to those entries. Inserting a column however would.
Likewise if you had entries in A10 and A11, then inserting a column will make no difference to those entries, but inserting a row will.
If you can, please upload/attach a sample of your spreadsheet so that your data organization (we don't need the true data, but some sample data would be helpful) can be analyzed.
See [Forum]How to attach a document here for assistance in preparing your document, including anonymizing it, and attach/uploading it to the forum.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5812
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Sorting Named Cells

Postby MrProgrammer » Sun Nov 24, 2019 7:33 am

Hi, and welcome to the forum.

Chef Silverman wrote:My problem is when a new item is entered between Blue_Cheese and Chicken_Breast. Let's say I add Calamari into the inventory and keep it alphabetical. If the named cells were A10 = "Blue_Cheese" and B10 = "Chicken_Breast" then inserting a row for the calamari (or adding calamari at the end and then sorting the columns into alphabetical order) leaves me with the problem of B1 is now Calamari but the name of cell B10 is still "Chicken_Breast".
If your data is in A10 and B10 (same row, different columns), then you can insert a cell before B10 and shift the contents of B10, C10, D10, … to the right. This should preserve the value of the defined name. If you find that it does not, attach a document demonstrating the difficulty (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself). Indicate where you make the insertion and what option you select in the Insert → Cells dialog. Indicate which defined name is not preserved.

If you put the new item at the end of your inventory and sort it, this will not preserve the value of the defined name.
[Tutorial] Formula Adjustments during Copy and Move
Formulas Don't Adapt to Sorting Their Reference Cells
In many situations, instead of a defined name you can use VLOOKUP to accomplish your task. Attach a document if you need further assistance. Indicate which cell needs the value for Chicken_Breast.

Summary: Inserting a cell, which is equivalent to moving cells, preserves values in dependent cells and defined names. Sorting does not for Good Reasons.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3945
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA


Return to Calc

Who is online

Users browsing this forum: No registered users and 10 guests