Ejuice calculator (added quantity calculations)

Scubabatdan · May 8, 2013

A question that was asked and I thought I would share the answer...

xxJ6xx said:
Hi, I've been using your v13 ejuice calculator for a little while, and I was wondering if there was a way to add flavors to the flavor list and have them show up in the drop-downs on the main PG and VG pages? I'm using Excel, and the few times I've tried the entries never show up under the correct m company, or the flavors get scrambled with the wrong companies. Any advice?
~Jason

When adding a flavor, right click on the numbers to the left and "insert" a row, then add the flavor you want. This will automatically adjust the names manager for that flavor. You can't insert on the last flavor, or the first flavor in a column, but can in the middle and then cut and insert where you need it. Always insert a row for the new flavor in the "group" of the manufacturer.

Dan

dmall · May 8, 2013

Scuba, while you are answering questions, on the VG calculator page, the formulas in section 7 do not work at all. I have tried to copy paste from the PG calculator page but that doesn't work either. Can you give me the correct formulas for that section?

Scubabatdan · May 8, 2013

dmall said:
Scuba, while you are answering questions, on the VG calculator page, the formulas in section 7 do not work at all. I have tried to copy paste from the PG calculator page but that doesn't work either. Can you give me the correct formulas for that section?

Section 7 of the VG calculator like the PG page work off of VLookup, it pulls the information from the recipes page. Then it multiplies the percent pulled by the amount of flavor in section 2 I.E.

Peach Flavor 42.68% * 1ml = 0.43ml

Drops are calculated by multiplying the ml from above by the drops per ml defined for flavoring in section 2 I.E

0.43ml * 33 = 14 drops

It then totals all the ml and the drops at the bottom using SUM(M20:M24) and SUM(N20:N24)

If the Vlookup does not pull a flavor from the recipes page in the "Select a Recipe Name from List --->" cell then you probably need to redownload the file as something has gotten corrupt in the names manager.

Are you using excel?

Dan

dmall · May 8, 2013

Scubabatdan said:
Section 7 of the VG calculator like the PG page work off of VLookup, it pulls the information from the recipes page. Then it multiplies the percent pulled by the amount of flavor in section 2 I.E.

Peach Flavor 42.68% * 1ml = 0.43ml

Drops are calculated by multiplying the ml from above by the drops per ml defined for flavoring in section 2 I.E

0.43ml * 33 = 14 drops

It then totals all the ml and the drops at the bottom using SUM(M20:M24) and SUM(N20:N24)

If the Vlookup does not pull a flavor from the recipes page in the "Select a Recipe Name from List --->" cell then you probably need to redownload the file as something has gotten corrupt in the names manager.

Are you using excel?

Dan

I am using open office cause I have a MAC, I have re-downloaded it several times with the same result. On the VG page in section 7 all I get is error 509. The PG page works fine. It's crazy! When I copied and pasted the formulas from the PG page it works but only if I have whatever recipe I want on the PG page. I'm at a loss.

The following is the formula I have in line 20 Section 7: =IF(VLOOKUP(FormRef 1;JuicesPick;3;FALSE())>0;VLOOKUP(FormRef 1;JuicesPick;3;FALSE());"")

I think that I need you to send me the actual formula that you have on your VG page section 7. Line 20

dmall · May 8, 2013

dmall said:
I am using open office cause I have a MAC, I have re-downloaded it several times with the same result. On the VG page in section 7 all I get is error 509. The PG page works fine. It's crazy! When I copied and pasted the formulas from the PG page it works but only if I have whatever recipe I want on the PG page. I'm at a loss.

The following is the formula I have in line 20 Section 7: =IF(VLOOKUP(FormRef 1;JuicesPick;3;FALSE())>0;VLOOKUP(FormRef 1;JuicesPick;3;FALSE());"")

I think that I need you to send me the actual formula that you have on your VG page section 7. Line 20

Let me clarify VG page, Section 7, Row 20, column K, is the formula I would like for you to send me. Thanks

Scubabatdan · May 8, 2013

dmall said:
Let me clarify VG page, Section 7, Row 20, column K, is the formula I would like for you to send me. Thanks

Here is the formula on the VG page, Section 7, Row 20, column K
=IF(VLOOKUP(FormRef,JuicesPick,3,FALSE)>0,VLOOKUP(FormRef,JuicesPick,3,FALSE),"")
formula on the VG page, Section 7, Row 20, column L
=VLOOKUP(FormRef,JuicesPick,4,FALSE)
formula on the VG page, Section 7, Row 20, column M
=$E$21*L20
formula on the VG page, Section 7, Row 20, column N
=M20*$C$21

In the names manager, JuicesPick =Recipes!$A$2:$N$192
Hope this helps,
Dan

Scubabatdan · May 8, 2013

Ahhh I think I see the problem, in the names manager both the PG and the VG page have the fields assigned as FormRef, let me change the VG to FormRfVG and see if that corrects things.

Hang on will correct it and reupload it.
Dan

Scubabatdan · May 8, 2013

Ok changed the FormRef on the PG page to ForRefPG.
And changed the FormRef on the VG page to FormRefVG.
Download it again, link in my sig, and see if that fixes you!
Dan

Menthologist · May 11, 2013

Dan your a legend, i've been trying to sort that ForRefPG myself, God i hate spreadsheets. Thanks for the update.

P.S Hows the ceramic wicks coming on, All the talk over here. Your the man.

dmall · May 11, 2013

Scubabatdan said:
Ok changed the FormRef on the PG page to ForRefPG.
And changed the FormRef on the VG page to FormRefVG.
Download it again, link in my sig, and see if that fixes you!
Dan

You are the best! Now it works! Thank you so much cause I could not figure it out.

dmall · May 11, 2013

Scuba, one more question, in section 6, how do I add more manufacturers, I want to add Flavor West and vaping Zone Flavors. I added a Flavor West flavor to the "Flavor List for Drop Downs" page but it doesn't show up in the manufacturers drop-down list in Section 6. Sorry for being such a pest but I really love the this spreadsheet and want to use it more with the flavors that I have. Thanks in advance.

dmall · May 11, 2013

dmall said:
Scuba, one more question, in section 6, how do I add more manufacturers, I want to add Flavor West and vaping Zone Flavors. I added a Flavor West flavor to the "Flavor List for Drop Downs" page but it doesn't show up in the manufacturers drop-down list in Section 6. Sorry for being such a pest but I really love the this spreadsheet and want to use it more with the flavors that I have. Thanks in advance.

Nevermind I just found the master list. Sorry

dmall · May 11, 2013

Okay Scuba I have a whole new problem. I inserted rows into master manufacturer list and added my vendors - great now those show in the drop-down menu of Manufacturer in Section 6.
I inserted rows on the list of flavors below the master manufacturer list and added flavors as you have your list. No Problem, it should have taken on the properties of the list.
When I go back to the PG or VG page section 6 and choose the new manufacturer it shows but when I try to choose the flavor that I have added it says: err:502
There must be some other filter that you have on that list that will not let me select the flavor that I have added to the drop-down list page.
Help again please!

Scubabatdan · May 11, 2013

Menthologist said:
Dan your a legend, i've been trying to sort that ForRefPG myself, God i hate spreadsheets. Thanks for the update.

P.S Hows the ceramic wicks coming on, All the talk over here. Your the man.

They are going goo, I must point out I am only a tester on the manufactured wicks. I only had a hand in the creation of the fish wicks

I eagerly await the release of the BD13 wicks, they are awesome.

dmall said:
Scuba, one more question, in section 6, how do I add more manufacturers, I want to add Flavor West and Vaping Zone Flavors. I added a Flavor West flavor to the "Flavor List for Drop Downs" page but it doesn't show up in the manufacturers drop-down list in Section 6. Sorry for being such a pest but I really love the this spreadsheet and want to use it more with the flavors that I have. Thanks in advance.

dmall said:
Okay Scuba I have a whole new problem. I inserted rows into master manufacturer list and added my vendors - great now those show in the drop-down menu of Manufacturer in Section 6.
I inserted rows on the list of flavors below the master manufacturer list and added flavors as you have your list. No Problem, it should have taken on the properties of the list.
When I go back to the PG or VG page section 6 and choose the new manufacturer it shows but when I try to choose the flavor that I have added it says: err:502
There must be some other filter that you have on that list that will not let me select the flavor that I have added to the drop-down list page.
Help again please!

LOL, yeah that is a PITA to mess with, I tell you what I will edit it and add 3 blank vendors as placeholders so they can be changed by the end user. And create a sub list associated with the blank vendors category. That work?
Will post back when I get the changes made.
Dan

dmall · May 11, 2013

Yeah I see, its so strange, it will allow me to add flavors of manufacturers that you already have and it will include the new manufacturers name that I added to the master list, but it will not add the flavor name of the new manufacturer to that drop down list. I have checked the validity and it matches exactly what the validity is in the other selections but no go can't get it to work. Sorry for the trouble.

LOL, yeah that is a PITA to mess with, I tell you what I will edit it and add 3 blank vendors as placeholders so they can be changed by the end user. And create a sub list associated with the blank vendors category. That work?
Will post back when I get the changes made.
Dan[/QUOTE]

Scubabatdan · May 11, 2013

Ok I have added 6 "Empty" vendors categories, and sub flavor list.
Select one and change the name I.E. Empty1 to MyFlavors, then change all of the flavor list "Empty1" to "MyFlavors" and add your flavors by inserting like I explained.

Hope this helps,
Newest file link is in my sig.
Dan

Scubabatdan · May 11, 2013

If everyone would tell me the manufactures names you want added, I could just add that manufactures name and flavors to the list

Dan

Scubabatdan · May 11, 2013

Ok updated the capellas flavor list. I will be working on updating Manufactures flavor list to reflect current flavors.
Dan

dmall · May 11, 2013

Okay Dan,
I opened up the updated list. In cell 10A I changed empty1 to Flavor West. Then went down column A to cell 594 Emply1 and changed to Flavor West then to column B and changed flavor to Vanilla Custard. Went to VG page, Section 6 and select drop-down for manufacturers and was able to select Flavor West great right. But when I went to drop-down for flavor it still gives error code 502. I give up. I don't want to continue to bother you. Thanks for trying to help

dmall · May 11, 2013

I forgot to tell you that I can insert rows and add flavors to the original manufacturers and they show in the drop-down flavor list.

Ejuice calculator (added quantity calculations)

Vaping Master

Senior Member

Vaping Master

Senior Member

Senior Member

Vaping Master

Vaping Master

Vaping Master

Senior Member

Senior Member

Senior Member

Senior Member

Senior Member

Vaping Master

Senior Member

Vaping Master

Vaping Master

Vaping Master

Senior Member

Senior Member

Similar threads

Users who are viewing this thread