Sal Paradise
Well-known Member
- Joined
- Oct 23, 2006
- Messages
- 2,457
You were all so helpful yesterday I'd hoped that you could help me with two more questions, one Excel related, and two VB related.
The first is using =COUNTIF or =SUMIF with an 'or' operator
Column A contains regions that products will be delivered to.
Column B contains prices.
If I want to have one sum for each region, it's easy. If my data in column one were:
Africa
America
Antarctica
Asia
Australia
Europe
Then I could just do
=SUMIF(A1:A65536,"Africa",D1) and whatnot for each of the regions.
However, if I were to want to combine, for instance, Asia and Europe, how would I go about it? I can obviously create a SUMIF for each of the two regions, and then sum the sumif by adding the two resulting cells together, but that just seems wasteful. I can't do =SUMIF(A1:A65536,OR("Africa","Asia"),D1) either, because the 'or' statement will only return true or false. The help files seem to be utterly useless in determining this piece of information, and a cursory check of the search function made me think I'm the only one dim enough to not know, or the only one stubborn enough to want to do it in one step.
The second question involves VB and user settings. Let's say that I have two users in a spreadsheet, "John" and "Jane" (as evidenced by what name comes up when I open a non-shared spreadsheet that they are in). If I were to want to hide column V from "Jane" and hide columns "A" and "C" from "John" how would I go about it? How can I test for user name in VB?
Is it really as simple as:
With a similar thing to unhide hidden rows when you close the workbook? Is it truly that simple?
Finally, with a UserForm, is there a helpfile/tutorial/other thread that I can look at to figure how to automatically generate a UserForm with the proper amount of CheckBoxes based on something?
For instance. I exchange drafts of documents back and forth with several other companies. When those companies come, looking at my records, I can predict pretty accurately which documents will be exchanged in which direction on that day. Therefore, I want to be able to click a button with the Printer's name, and have the program generate a userform with the appropriate printer's name, and the appropriate amount of checkboxes for inbound and outgoing files.
I can test for which files will likely be going in and out on a given day, but I can't figure out how to apply that information to a userform of dynamic size. If I just make 10 incoming and 10 outgoing, it will cover all but the very heaviest days, but it will also have far too many checkboxes on the average day. Conversely, if I don't give enough options, it will tend to cause problems as well.
So to get more specific, let's say I have two arrays.
The first array is OutA(x) where X is the amount of files that I've tested for that seem like they're likely to go out.
The second array is InA
where Y is the amount of files that I've tested for that seem like they're going to come in.
I want to generate a Userform with the printer's name, an 'in' column and an 'out' column, with X rows/checkboxes in the 'out' column, and Y row/checkboxes in the 'in' column. I want it to automatically generate based on the strings stored in the arrays.
Is this easily possible? Unfeasible for someone of my (albeit low) level? Or should I go and research it more?
The first is using =COUNTIF or =SUMIF with an 'or' operator
Column A contains regions that products will be delivered to.
Column B contains prices.
If I want to have one sum for each region, it's easy. If my data in column one were:
Africa
America
Antarctica
Asia
Australia
Europe
Then I could just do
=SUMIF(A1:A65536,"Africa",D1) and whatnot for each of the regions.
However, if I were to want to combine, for instance, Asia and Europe, how would I go about it? I can obviously create a SUMIF for each of the two regions, and then sum the sumif by adding the two resulting cells together, but that just seems wasteful. I can't do =SUMIF(A1:A65536,OR("Africa","Asia"),D1) either, because the 'or' statement will only return true or false. The help files seem to be utterly useless in determining this piece of information, and a cursory check of the search function made me think I'm the only one dim enough to not know, or the only one stubborn enough to want to do it in one step.
The second question involves VB and user settings. Let's say that I have two users in a spreadsheet, "John" and "Jane" (as evidenced by what name comes up when I open a non-shared spreadsheet that they are in). If I were to want to hide column V from "Jane" and hide columns "A" and "C" from "John" how would I go about it? How can I test for user name in VB?
Is it really as simple as:
Code:
Sub WorkBook_Open
If UserName = "John" Then
Column("A").Hidden = True
Column("C").Hidden = True
ElseIf UserName = "Jane" Then
Column("V").Hidden = True
EndifWith a similar thing to unhide hidden rows when you close the workbook? Is it truly that simple?
Finally, with a UserForm, is there a helpfile/tutorial/other thread that I can look at to figure how to automatically generate a UserForm with the proper amount of CheckBoxes based on something?
For instance. I exchange drafts of documents back and forth with several other companies. When those companies come, looking at my records, I can predict pretty accurately which documents will be exchanged in which direction on that day. Therefore, I want to be able to click a button with the Printer's name, and have the program generate a userform with the appropriate printer's name, and the appropriate amount of checkboxes for inbound and outgoing files.
I can test for which files will likely be going in and out on a given day, but I can't figure out how to apply that information to a userform of dynamic size. If I just make 10 incoming and 10 outgoing, it will cover all but the very heaviest days, but it will also have far too many checkboxes on the average day. Conversely, if I don't give enough options, it will tend to cause problems as well.
So to get more specific, let's say I have two arrays.
The first array is OutA(x) where X is the amount of files that I've tested for that seem like they're likely to go out.
The second array is InA
where Y is the amount of files that I've tested for that seem like they're going to come in.I want to generate a Userform with the printer's name, an 'in' column and an 'out' column, with X rows/checkboxes in the 'out' column, and Y row/checkboxes in the 'in' column. I want it to automatically generate based on the strings stored in the arrays.
Is this easily possible? Unfeasible for someone of my (albeit low) level? Or should I go and research it more?
