• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sorting SUMPRODUCT? sustaining references

G

Guest

Guest
OK I will do my best to explain this... not sure what my problem would be described as but basically I have a list of names and a list of formulas that go with the names, and well what I am trying to do is sort the names alphabetically and have the formulas still correspond/reference to the correct cells. Most of the formulas I am using are like =(A1*6)+(B2*1) and such, couldn't figure out SUMPRODUCT to see if it would help have tried looking for multiple sorting options and couldn't find any solutions.


Here is a link to what I have put together so far its just a recipe list for a game I enjoy playing.

https://www.dropbox.com/s/vx3qus2o3u2chsv/Minecraft%20Recipe%20Formulas1.xlsx
 
Hi, Miznamo!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


Firstly let me say that the use of Excel for doing such a thing like a recipe list for a game is at least... how should I say... wonderful... even if it's about Minecraft. :p


Now, looking at your uploaded file I have several doubts which I'd like you to clarify:


a) I understand data in column A, the ingredients of the recipes


b) But I don't get what's column B for, all the formulas reference to cells in same column, how do you tie to the originally related element in column A?

Example: Refined Iron is function of Electronic Circuit, Machine Block, Forcefield EU injector? If you sort the list, you still want that the formula in B column were dependent on Electronic Circuit, Machine Block and Forcefield EU injector?


If I still don't get the point, consider uploading a sample file (including manual examples of desired output and highlight status before and after sorting).


Regards!
 
Hi, Miznamo!


Now I think I've got it.


- 1 unit of Refined Iron isn't composed by 1 unit of Electronic Circuit plus 8 units of Machine Block plus 4 units of Forcefield EU injector.


- 1 unit of Refined Iron is used (or is equivalent?) to 1 unit of Electronic Circuit or 8 units of Machine Block or 4 units of Forcefield EU injector.


- if yes to previous assert, aren't missing many final products?, i.e., Forcefield dome upgrade.

[pre]
Code: 
Forcefield dome upgrade
4 Advanced Alloy
1 Mixed Metal Ingot
^1 Electronic Circuit
6 Copper Cable
Each 6
6 Rubber
3 Copper
[/pre]
BTW, in this case "Each 6" what means?


- you want to sort the components alphabetically (and why not filtering them too?)


- and your goal is to know what components do you need to create another equipment stuff, or what could you build or assembly with the existing components? in last case you'll need a stock value for each one, additionally


Please confirm this and I'll try to prepare a sample grid, I'm still wondering but I think it'd be a matrix.


Regards!
 
Hi ,


I am not sure I have understood your problem , but try this :


1. Select your data range viz. A2:B42


2. Click on Formula , Create From Selection , Left Column , OK. This will create the required range names from the column A labels.


3. Click on Define Names , Apply Names , and check the checkbox options.


4. Now perform your Sort of columns A and B.


There appears to be one circular reference which you will have to resolve yourself.


Narayan
 
Hi, SirJB7


Ok so I think you almost got it, But refined iron is used to make, like for machine block it takes 8 refined iron to get 1 machine block same with the other two it takes 1 to create a Electronic Circuit and 4 to create a Forcefield EU injector.


Each 6 is 6 rubber and 3 copper create 6 copper cable you can't make more or less per craft like if you have 7 rubber and 4 copper you still only get 6 copper cable same as in if you have 5 rubber and 2 copper you can't make any. The only way to get more then 6 is have a complete double like 12 rubber and 6 copper you can make 12 copper cable.


I will post a link to the wiki so you can see a picture of what the crafting looks like

http://thetekkit.wikia.com/wiki/Forcefield_EU_Injector


Forcefield dome upgrade

4 Advanced Alloy

(1 Mixed Metal Ingot) is used for each Advanced Alloy so this would be 4

^1 Electronic Circuit

(6 Copper Cable) it takes 6 to make 1 Electronic Circuit

(Each 6) you can only make 6 at a time for the below ingredients

6 Rubber

3 Copper

^2 Redstone

^1 Refined Iron (another reason I wanted to get an excel set up so I don't overlook ingredients)


Not to worried about existing materials more wanting an overall look at what it takes to create specific items like this forcefield dome upgrade the ingredients would be: 4 Mixed Metal Ingots turned into 4 Advanced Alloy, 2 Redstone, and then 1 iron turned into 1 refined iron. then the 6 Copper Cable 2 redstone and 1 refined iron turned into 1 electronic circuit, then the 4 Advanced Alloy and 1 Electronic Circuit turned into 1 Forcefield dome upgrade.


Which will look like.

http://thetekkit.wikia.com/wiki/Forcefield_Dome_Upgrade
 
Hi, NARAYANK991


From what I understand from yours is it is basically giving the names values? do they still correspond to the adjacent values from the results of the formulas?
 
Hi, NARAYANK991


Really the only problem I have with that is it doesn't update when I sort it so the names for the locations are still the same as before the sort so thats where the circular comes from... though it was an idea and I enjoyed testing it and maybe I will find a way to make it work but until then I continue to search :)


It actually is exactly what I am after if anyone can figure out how to get it to update with the sorting.
 
Hi ,


The problem is that when you create a range name , Excel makes all the references absolute. So even though the formula remains the same , the cell it is referring to could have changed due to the sort.


Narayan
 
Hi ,


It can be done , but I think it will take some time. Let me know if you are still interested in getting the solution to this.


Narayan
 
Hi, NARAYANK991


I am still interested in this because I am the one who will be using it and adding to it as it needs adjusting. The problem I have with potentially doing it by hand is if I need to update it what kind of effort will I need to put in per additional recipe.
 
Hi, Miznamo!


Please give a look to this file:

https://dl.dropbox.com/u/60558749/Sorting%20SUMPRODUCT_%20sustaining%20references%20-%20Minecraft%20Recipe%20Formulas1%20%28for%20Miznamo%20at%20chandoo.org%29.xlsm


The idea is an square matrix with all elements in rows and columns headers storing the relationship between items (light blue) and its components (light orange), plus a wish lits of desired items to build (light violet).


You have 3 command buttons, one for clearing wish list, other for generating the list of parts needed and the last for sorting items alphabetically.


It's just a first step. I want you to:

a) validate the model

b) play a bit with 1st and 2nd buttons

c) don't use 3rd yet

d) don't add or delete items


If it's Ok, the next steps will be:

1) splitting Parts column in two: general parts (1st level of components), same as today, and detailed parts (detailed level of individual components)

2) handling "each" feature

3) performing a manual sort to avoid losing references


Waiting for your comments.


Regards!
 
Hi, SirJB7


Just trying to understand what you sent me... where are the formulas to calculate the required materials for the items? or is that not in there yet? It was a little overwhelming when I opened it cause I wasn't expecting a table like that.


... wondering if I can chat with one of you guys a little bit faster... feels like I am playing phone tag here, like skype or something.
 
Hi, Miznamo!


The idea is to have the things distributed as this:


a) Worksheet

1) Violet zone: wish list, or items wanted to be build

2) Blue zone: items, all of them, elemental and compound

3) Orange zone: components per item, if zero then elemental item


b) VBA code

1) clear list: empties wish list

2) generate list: here is the equivalent to the formulas you were asking for, there are no more formulas as VB code does the job (you can see it with Alt-F11 go to Modulo1 module and search for Sub GenerateList

3) sort items: still pending


Adding a new item will require to:

- copy the last used row to next one empty

- copy the last used column to next one empty

Deleting items will require to:

- delete the related column (firstly)

- delete the related row (lastly)


Regards!
 
Hi ,


It works !


I am copying below the steps I had mentioned earlier for ease of reference :


1. Select your data range viz. A2:B42


2. Click on Formula , Create From Selection , Left Column , OK. This will create the required range names from the column A labels.


3. Click on Define Names , Apply Names , and check the checkbox options.


4. Now perform your Sort of columns A and B.


At this stage , all the values will be wrong , since the cell references have got mixed up.


Selecting the entire table of data ( A2:B42 ) , again repeat steps 2 and 3 above.


All the range name references will be corrected , and your formulae will all be correct.


Narayan
 
Hi, Miznamo!


Download again the updated file from same previous link. What for?

Test sorting, then test adding and removing items, then test building a couple of items (change the preloaded values, they're just for sorting test purposes, otherwise you'd get an overflow error), test the grand total at C1 cell.

The only thing remaining is the "each" feature and adding filters (I just forgot it). I'll give a tweak later today, I hope.


Please advise if any issue.


Regards!
 
Hi, NARAYANK991


Much thanks for the effort its quite appreciated.

But I am going to go with SirJB7's idea, NARAYANK991 you made what I was after work and possibly solved problems of similar sort for me in the future.

SirJB7 took my idea and saw where to improve upon it and that is why I am going to use his solution instead. :)


But thank you again for spending your time and effort working on a solution for me :)
 
Hi, Miznamo!


Download again the updated file form same previous link.

Added a column for the "Each" feature and adjusted calculations. Please play around for a while to test it.

Just advise if any issue.


Regards!


PS: BTW, today's still Monday (GMT-3)... :p
 
You must log in or register to reply here.
Back
Top