> >
>

# Excel Function Help

#1
07-29-09, 05:55 PM
Member
Join Date: Jul 2006
Location: Central MN
Posts: 556
Excel Function Help

I work for a paint company. We brought in a new paint system that breaks and ounce into 64 parts for tinting. Our existing tint machine is set up for 48 part ounces. We cannot replace the tint machine because we still need it, nor can we buy a new one. SO I made a spreadsheet that will convert the formulas. I was asked to add a label to the spreadsheet. before I get to my question, I will attempt to diagram what we have now.

A B C D E F G
1 Colorant OZ 64 -blank- Colorant OZ 48
2 LB -data- -data- LB -same as "B" -calc-

We have 13 colorants. we enter the formula given in columns B&C in the rows of the colorant. No color uses all 13 colorants. column F is the same as B as the oz do not change. column G is the result of the calculation to convert the 64 to 48.

Now here is what I need: on the 2nd sheet I need to create a label. I want to have the data that is greater then zero in F & G to be transferred to the label. For example if LB is used then F or G will be at least .38 and thus greater then 0. So I want LB (colorant), F & G to transfer to a the label. If LB is not used then I do not want it to transfer. I have limited space so I can only take the colorants that are used. These will change with every color.

I thought about using an IF function, but I kept running into a problem of having 2 or 3 positives.

Thoughts?

p.s. I don't have access and don't know how to create a database at all so I would prefer Excel if possible.

Edit: The spacing of the columns and rows did not turn out. the A-G are supposed to be columns, and the other stuff is supposed to be what is the respective cell, i.e. A1 has the word "colorant"

#2
07-30-09, 11:46 AM
Member
Join Date: Feb 2008
Location: WA
Posts: 149
you can do nested if statements, example
=if(LB="", if(F>0.38,F,G), "")

I'm not sure I fully understand what you need, but that shows the concept. "" is used for blank cells.

#3
08-03-09, 12:44 PM
Member
Join Date: Jul 2006
Location: Central MN
Posts: 556
I am slowly figuring this out. I am wondering if it is possible to nest IF AND & OR all in the function:

below is what I am trying to do, but it is not working:
...if(AND(g15="a",OR G16="b",G17="c"),"e","")

I know the above doesn't work but I am trying to have the IF look to see if G15 equals "A" and If G16 or G17 are true then return "E". I don't want both to be true, just one.

#4
08-04-09, 07:49 AM
Member
Join Date: Feb 2008
Posts: 188
Originally Posted by bclacquer
I am slowly figuring this out. I am wondering if it is possible to nest IF AND & OR all in the function:

below is what I am trying to do, but it is not working:
...if(AND(g15="a",OR G16="b",G17="c"),"e","")

I know the above doesn't work but I am trying to have the IF look to see if G15 equals "A" and If G16 or G17 are true then return "E". I don't want both to be true, just one.
Try this:
=IF(AND(G15="a",OR(G16="b",G17="c")),"e","")

#5
08-04-09, 09:03 AM
Member
Join Date: Jul 2006
Location: Central MN
Posts: 556
that worked alone but not in my massive nested formula. I keep getting an error at "or." Could it have something to do with the fact I have 7 "If"s already?

Could someone point me towards a good breakdown of how to use VBE? I think that may be better for me. I started trying to set up a formula, but did not know how to save it or implement it.

#6
08-04-09, 10:09 AM
Member
Join Date: Feb 2008
Posts: 188
Originally Posted by bclacquer
that worked alone but not in my massive nested formula. I keep getting an error at "or." Could it have something to do with the fact I have 7 "If"s already?

Could someone point me towards a good breakdown of how to use VBE? I think that may be better for me. I started trying to set up a formula, but did not know how to save it or implement it.
Can you send your complete formula or xl document (even though it doesn't work) and perhaps we can look at the nested loop problem.

Here is a simple example of an if then else statement using VBA.

Sheet1:

A1 = a
A2 = b
A3 = c

VBA:
Private Sub Worksheet_Activate()
If Sheet1.Cells(1, 1) = "a" Then
Sheet1.Cells(2, 2) = "ok"
Else
Sheet1.Cells(2, 2) = "not ok"
End If
End Sub

Result:
B2 = ok

Now if you changed a1 to something other than a and ran the code in vba, you would get the result of "not ok".

#7
08-04-09, 11:28 AM
Member
Join Date: Jul 2006
Location: Central MN
Posts: 556
here is what I have so far (broken for easy display):
IF(AND(F12="a",F13="b"),"b",
IF(AND(G21="a",F13<>"b",F14="c"),"c",
IF(AND(G21="b",F14="c"),"c",
IF(AND(F12="a",F15="d",F13<>"b",F14<>"c"),"d",
IF(AND(F13="b",F15="d",F12<>"a",F14<>"c"),"d",
IF(AND(F15="d",F14="c",F13<>"b",F12<>"a"),"d",
if(AND(F12="a"or(F13<>"b",F14<>"c",F15<>"d")),"e",""))))))))

I have to have this cell look to see if the cell above it contains data and then look at the results from the another function. Based on the results this cell will display B C D E F or G or blank. the cell directly above it will display A B C D E F or G based on the data input in another area. If the upper cell displays A then the second cell will display either B C D E F or G. But if the upper cell displays B then the second cell must show C D E F or G.

This will be for about 7 cells stacked vertically. Each will display data based both on input from another area and the data in the cell directly above it.

right now I am working with the letters A B C D E F and G on a spreadsheet with very little extra data. It makes it easier to figure out the formula. eventually this will be inputted into a spreadsheet the converts data and then will display like I want sheet 2.

#8
08-04-09, 11:45 AM
Member
Join Date: Jul 2006
Location: Central MN
Posts: 556
I was thinking. I think this is too many IFs but maybe I can combine them.

If F21=a & E13=b, then b
If F21=a & E14=C then C
If F21=a & E14=D then D
Etc.

this would have to go on until we get through F21=G and E=G

of course then the next cell would have to look at F21=a & F22=B before it could equal c

I am using F21-F27 for A-G.

#9
08-04-09, 02:39 PM
Member
Join Date: Feb 2008
Posts: 188
Still a little confused with what you are trying to achieve, so I uploaded an example with a label formula, based on your first post.

To make it easier, upload your file to the same place. Perhaps then someone may be able to look at the data and help you out.

To get my example, click on the link below.

paint_example.xls

#10
08-05-09, 10:18 AM
Member
Join Date: Jul 2006
Location: Central MN
Posts: 556
ok
I uploaded 2 files they can both be found HERE.

The one called "convert 64 to 48th" is the one my stores are using and the one I will be putting the function into when it done. The data is entered under the OZ and 64th column. The other side converts. I want to pull the data from the 48th section to the label on the next page. I have already done (on another file) and can do it, but the colorants end up all over the label. I want them to start the left and fill down then fill the right side if I have that many colorants.

The second file "ici 844" is the file I have been working with to try to figure out this formula.

#11
08-05-09, 12:00 PM
Member
Join Date: Feb 2008
Posts: 188
Originally Posted by bclacquer
ok
I uploaded 2 files they can both be found HERE.

The one called "convert 64 to 48th" is the one my stores are using and the one I will be putting the function into when it done. The data is entered under the OZ and 64th column. The other side converts. I want to pull the data from the 48th section to the label on the next page. I have already done (on another file) and can do it, but the colorants end up all over the label. I want them to start the left and fill down then fill the right side if I have that many colorants.

The second file "ici 844" is the file I have been working with to try to figure out this formula.
I have downloaded the files, and will take a look at them.

#12
08-05-09, 11:16 PM
Member
Join Date: Feb 2008
Posts: 188
I have modified your file to what I think is what you want.

When you launch the document, you will see your convert form. Filling out the values in the Ounce and 64th will convert and copy the ounce and 48th values to your second sheet.

What you needed to do what increment the rows once there was a value on the sheet. That's why you were getting the colorants all over the label.

Hopefully this is what you want. Just download the file from here:

Free File Hosting Made Simple - MediaFire

:Rbwest

#13
08-06-09, 10:22 AM
Member
Join Date: Jul 2006
Location: Central MN
Posts: 556
Thanks

Thanks. How did you do that? and is there a way to bring up the pop up form after I close it to print my formula without closing the spreadsheet and re-opening it?

Also, as you have it right now, it is too big. I can only fit 6 colorants on one side of the label. That is why I had two columns for the colorants. Theoretically, we could use every colorant on the tint wheel.

Finally, I may also have to do this on Open office. With the same Macro work? or should I stick with a retail version of excel?

#14
08-06-09, 01:29 PM
Member
Join Date: Feb 2008
Posts: 188
Originally Posted by bclacquer
Thanks. How did you do that? and is there a way to bring up the pop up form after I close it to print my formula without closing the spreadsheet and re-opening it?

Also, as you have it right now, it is too big. I can only fit 6 colorants on one side of the label. That is why I had two columns for the colorants. Theoretically, we could use every colorant on the tint wheel.

Finally, I may also have to do this on Open office. With the same Macro work? or should I stick with a retail version of excel?
I added a button to sheet2 and another button to the form for opening and closing. Placing entries in the right column would take more programming. At least this should give you a start.

Not sure what you mean about being too big. The calculated values are transfered to the spreadsheet where there is a blank entry. You can resise the grid to what ever you like.

Basically it is done in the visual basic editor (VBA). Because of the scripting language, it won't work in Open Office. If you were just using formulas on a spreadsheet, then Open Office will work.

Here's the updated copy with the two new buttons.

Free File Hosting Made Simple - MediaFire

#15
08-06-09, 03:25 PM
Member
Join Date: Jul 2006
Location: Central MN
Posts: 556
I mean if I enter more then 6 colorants, the label becomes too large for printing. We are using a dymo label printer. It may not be such a big deal if I can figure out how to modify the script. I could have the formula run down the right side of the label and the identifying information on the left side.

Do you think there may be a formula or function out there that could do the same thing? I don't have a problem with not using open office, but my company may want to use it if possible.

I also have a dumb question, why can't I use my number pad to enter data?

I really must thank you for your help. it is greatly appreciated.

#16
08-06-09, 05:54 PM
Member
Join Date: Feb 2008
Posts: 188
Originally Posted by bclacquer
I mean if I enter more then 6 colorants, the label becomes too large for printing. We are using a dymo label printer. It may not be such a big deal if I can figure out how to modify the script. I could have the formula run down the right side of the label and the identifying information on the left side.

Do you think there may be a formula or function out there that could do the same thing? I don't have a problem with not using open office, but my company may want to use it if possible.

I also have a dumb question, why can't I use my number pad to enter data?

I really must thank you for your help. it is greatly appreciated.
So it is 3 rows on the left and 3 rows on the right = 6 colorants or is it 6 rows on the left and 6 rows on the right = 12 colorants?

Look at the "module1" in the Modules folder in the visual basic editor. That sub routine handles the count and finds the next blank row. What you would have to do is place a flag so that once the left has filled, then reset the counter but place the data at Sheet2.Cells(a, 5). Once the right side has been filled, then do not allow any more additions by advancing the flag one more time.

As far as the num pad goes, I had placed some code to only accept numbers (asc values from 48 - 57). You can remove that code by clicking on the "Convert1" form in the forms folder of the visual basic editor. Once the form is being displayed, then double click on each of the white text boxes. You will see the default of "change". Pull down the combo box where you will see "Key Down". It will then display that code for you. Just delete it and all the keys will work. I tried it here and my key pad works. Make sure that your keyboard's Num Lock is turned on.

Good luck and have fun!

:rbwest

#17
08-06-09, 05:59 PM
Member
Join Date: Jul 2006
Location: Central MN
Posts: 556
12 total colorants, however, I was thinking about that and talking to some others about it; I don't foresee a need for all 12 colorants. If I can pull the colorants up a few rows so that 6-9 will display that should be good enough. I will play with it to see if I can add all 12 just to learn the tricks.

Thanks again.

I will let you know if I run into issues. if you want, I can also provide a finished product for you to check out.

On a related note, do you know of a good book or class that could teach me how to do this?

#18
08-06-09, 06:08 PM
Member
Join Date: Feb 2008
Posts: 188
Originally Posted by bclacquer
12 total colorants, however, I was thinking about that and talking to some others about it; I don't foresee a need for all 12 colorants. If I can pull the colorants up a few rows so that 6-9 will display that should be good enough. I will play with it to see if I can add all 12 just to learn the tricks.

Thanks again.

I will let you know if I run into issues. if you want, I can also provide a finished product for you to check out.

On a related note, do you know of a good book or class that could teach me how to do this?
Books on VBA programming would help. There may be some out there specifically for Excel. If I have time, I will see what I can do.

:rbwest

#19
08-06-09, 07:33 PM
Member
Join Date: Feb 2008
Posts: 188
Here's the updated file to do the 6 entries on the left and 6 entries on the right. I also added a print button which will give you a print preview. If ok, then print. If you don't want the titles for printing, then go in to the editor, double click on the form to open it, then double click the "print" button. From there just change the code:

Worksheets("Sheet2").PageSetup.PrintArea = "a1:g12"
to
Worksheets("Sheet2").PageSetup.PrintArea = "a7:g12"

Get the update from:

Free File Hosting Made Simple - MediaFire

:rbwest

#20
08-07-09, 08:29 AM
Member
Join Date: Jul 2006
Location: Central MN
Posts: 556
Can I delete the print button? I need my co-workers to enter the color information and the base. I did a list of all the bases so they could just select from a drop down menu.

More importantly, is it possible for me to protect the sheet? I like to have them only be able to adjust the base and color (sometimes they mess stuff up). Also, is it possible to input just a 64th into the form. I found I have to put a zero in the oz column for the 48th to show on the label. And finally, can I modify the code so I can throw in a column between B&C and F&G for the letter Y. I was thinking I could do If B7>0,"Y","" that should give me a "Y" in what will be C column if there is an ounce. I tried going into the editor and where it says "b=b+1 and changing that to b+2 but that didn't seem to do anything.

I am sorry for all the questions, but this is the first I have ever worked with VBE. I am trying to figure out exactly what you did so I can mod it, and maybe change some of my existing label spreadsheets.

Here is my setup as it stands right now.

#21
08-07-09, 08:52 AM
Member
Join Date: Feb 2008
Posts: 188
Originally Posted by bclacquer
Can I delete the print button? I need my co-workers to enter the color information and the base. I did a list of all the bases so they could just select from a drop down menu.

More importantly, is it possible for me to protect the sheet? I like to have them only be able to adjust the base and color (sometimes they mess stuff up). Also, is it possible to input just a 64th into the form. I found I have to put a zero in the oz column for the 48th to show on the label. And finally, can I modify the code so I can throw in a column between B&C and F&G for the letter Y. I was thinking I could do If B7>0,"Y","" that should give me a "Y" in what will be C column if there is an ounce. I tried going into the editor and where it says "b=b+1 and changing that to b+2 but that didn't seem to do anything.

I am sorry for all the questions, but this is the first I have ever worked with VBE. I am trying to figure out exactly what you did so I can mod it, and maybe change some of my existing label spreadsheets.

Here is my setup as it stands right now.
You can modify or do what you want with the code (program). I was simply just giving you a hand. Go in to the editor, and double click on the form "Convert1", highlight the print button, then press your delete key to remove it. I thought that it would be an additional option for you to print instead of closing the form, highlighting the area to print, then print.

The reason for having to input both Oz and 48th, for taking the results to the spreadsheet. Once you loose focus from one text box to the other, it checks to see if both are filled in. If they are, then it will copy the results to the spreadsheet. You could modify the code to see what would happen if you took away the "48th" textbox check statement.

If you add any new columns between A & G, then you will have to modify the "copy" portion of the code to reflect where you want it to copy to.

Try protecting the sheet as you did before, and see if it still works.

Good luck,

:rbwest

#22
08-07-09, 02:27 PM
Member
Join Date: Jul 2006
Location: Central MN
Posts: 556
Works great. I do have a few other questions. As much as I appreciate what you have done, I ask that you not mod the file any more. I would like to do it myself to learn.

I was thinking that instead of going back to the label to input the base and color info I could put a box right on the form that the data could be put into and then transfered to the label. I tried to add a "listbox" to the form so I could do a dropdown list for the base selection but I am having a hard time determining the code. I googled it but all the solutions are for rather complex validations. I have a list made and a drop down selection on the label. Is it possible to do this?

Also, it should be possible (I think) to add a box to the form the color information could be imputed into and that put to the label.

Are these possible? If they are, then I modify the print button to print (not preview).

Edit 1: ok. got the list to populate. Now trying to get it to cell B4. Here is the code I have for the listbox:
Private Sub ListBox1_Click()
Sheet2.Cells (b4)
End Sub
it does not work. I get a "invalid use of property" error.

Last edited by bclacquer; 08-07-09 at 05:26 PM.
#23
08-07-09, 05:54 PM
Member
Join Date: Jul 2006
Location: Central MN
Posts: 556
damn. can't edit anymore. So I have the listbox. Is it possible to type another number into the list box other then the numbers in there? I know when I do this with regular cells, I leave a blank and then I can type whatever.

I also have a "color" box now. But still can't get either to transfer to the label.

Finally I deleted the "Print Preview" line on the print button. If I have a print area defined in the page set-up and it is also in the code, I should be able to skip the preview. I want to skip the print box as well if I can.

#24
08-08-09, 12:17 PM
Member
Join Date: Jul 2006
Location: Central MN
Posts: 556
I think I got it. On monday I will put it on the work computer and see how it does. If I have questions, I will post them.

#25
08-08-09, 12:23 PM
Member
Join Date: Feb 2008
Posts: 188
Originally Posted by bclacquer
I think I got it. On monday I will put it on the work computer and see how it does. If I have questions, I will post them.

That's great. Glad to see that you are doing it yourself.
Just to let you know that all that you hope to accomplish can be done (combo boxes, list boxes, check boxes etc.) It all can be added to the form, then code accordingly in order for the objects to function properly.

:rbwest

#26
08-10-09, 07:30 AM
Member
Join Date: Jul 2006
Location: Central MN
Posts: 556
I found 2 issues. I have been trying to figure out what you did, but I am having a difficult time. Perhaps you can explain it to me? If I understand then maybe I can fix these issues myself.

issue 1: when the 1 or greater is entered in the oz column but a zero is entered in the 64th column, no data is entered on the label.

issue 2: I cannot enter a .5 (1 half) in the 64 column.

On the plus side I figured out how to have the print button print without the print preview popping up.

finally, (I may be able to figure this out) I moved a bunch of buttons around and now when I select stuff with the "Tab" key it goes all over. Is that due to how the code is laid out vs the location of the button?

Thanks

#27
08-10-09, 11:57 AM
Member
Join Date: Jul 2006
Location: Central MN
Posts: 556
UPDATE (again)
Issue 1 Dealt with
Issue 2 still having problems with

But I have a question. Why is it that if I have say some data in the 64 column in LB but when I change it I get a second LB? I tried adding an Update button but that just adds a second line.

Last edited by bclacquer; 08-10-09 at 12:14 PM.
#28
08-11-09, 06:41 AM
Member
Join Date: Feb 2008
Posts: 188
Originally Posted by bclacquer
UPDATE (again)
Issue 1 Dealt with
Issue 2 still having problems with

But I have a question. Why is it that if I have say some data in the 64 column in LB but when I change it I get a second LB? I tried adding an Update button but that just adds a second line.
Issue 2: you will either have to add allowances for the asc value of a period in the code or take out the code all together from the "On Key Down" (which is a key check) area. If you recall, I added in key code so that you can only enter in numbers, not letters. That is why you can't place a period for ".5" in your text boxes.

If you loose focus after entering in data in both text boxes, it will copy it to a new line. Changing the data assumes that it is a new entry. You could track the entries in an array so that if you edit one, then it will place the new data where it was on the spreadsheet. (More complex and coding to do that).

The tab key works according to the properties set in the text box. You will notice each text box has a different number which is usually incremented when ever you add a new object. That can be changed by changing the number order on each object.

:rbwest

#29
08-12-09, 03:35 AM
Member
Join Date: Jul 2006
Location: Central MN
Posts: 556
I have a problem. I don't know if it is with the script or with the computer. I changed the tab order and then deleted all the Page Down lines. seemed to work great. then I went back to use it about an hour later and when I try to select something, ussually a box for the formula Excel freezes. Now at one point I had the file stored on a usb thumb drive and while trying to access it I got a DLL error when I attempted to view the code on the form. I had an earlier copy emailed to me and that worked for a while. Then it crashed.

If it is a script error, any idea of what could be going wrong? Personally I think the file somehow got corrupted. But I hope I am wrong.

#30
08-12-09, 06:49 AM
Member
Join Date: Feb 2008
Posts: 188
Originally Posted by bclacquer
I have a problem. I don't know if it is with the script or with the computer. I changed the tab order and then deleted all the Page Down lines. seemed to work great. then I went back to use it about an hour later and when I try to select something, ussually a box for the formula Excel freezes. Now at one point I had the file stored on a usb thumb drive and while trying to access it I got a DLL error when I attempted to view the code on the form. I had an earlier copy emailed to me and that worked for a while. Then it crashed.

If it is a script error, any idea of what could be going wrong? Personally I think the file somehow got corrupted. But I hope I am wrong.
It could be a corrupt file. I downloaded the latest file that you uploaded and took out the "Key Down" lines which I believe that is what you meant. Not "Page Down" lines, and all worked fine. The tab order seemed to be ok for the text boxes.

#31
08-12-09, 07:59 AM
Member
Join Date: Jul 2006
Location: Central MN
Posts: 556
If you get a chance in the next week or so, could you try this one? it is my final attempt (at least until my company decides they want to buy me some books). so far it is OK on this computer. If it works for you, then any other crashing is a bad computer. I would also value your input on any changes that could be made.

Thanks again for all your assistance.

#32
08-22-09, 05:37 PM
Member
Join Date: Feb 2008
Posts: 188
Bug fixes

Hi,

I have fixed the bugs from your final copy as well as enhanced the program so you can now edit each cell etc.

If you have any further questions, please PM me.

You can get the final updated copy from:

Free File Hosting Made Simple - MediaFire

:rbwest

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Pingbacks are Off
Refbacks are Off