Is it possible to modify the sub "Continue" below so that the "If s.Name <> wsKeep" line compares multiple variables? The code is for the purpose of setting a workbook to a required state, i.e. hide all worksheets except ones stated in variables. I have further subs that would also benefit from me knowing how to do this.
View 2 RepliesI am working on a survey analysis and I'm having trouble comparing two columns.
Ex. 100 surveys
Column A being age (scale of 1-5)
Column B being willingness to pay (scale 1-5)
I am trying to correlate age with willingness to pay by saying =countif(a1:100 = "1" and (B1:B100 = "1" and "2"))
Basically checking to see if the 1st age group has a strong willingness to pay; I would repeat with B1:B100 = "4" and "5".
I'm making a Form with multiple pages and on every page there are atleast 36 Textboxes.
[Amount] [Description] [price]
I want to run a small sub updating the price on changing the amount. My code now looks like this:
Code:
Private Sub TextBox1_Change()
Call UpdatePrice
End Sub
Private Sub TextBox2_Change()
Call UpdatePrice
End Sub
Private Sub TextBox3_Change()
Call UpdatePrice
End Sub
I have to do this on 8 pages, 24 to 36 times, which makes the code extremely long. Is there a better way to do this?
Something like:
Code:
Private Sub TextBox1_Change(), TextBox2_Change() etc...
Call UpdatePrice
End sub
I have about 180 workbooks which I need to compile into a Master Schedule.
All the tab 1's are different, these feed into tab 2, which the data has the same formatting throughout. The 2nd tab has the same data for A:F 1 but cells A:2 - F:2 down to row 9 are populated from tab 1, therefore different in each. I am trying to get a Master schedule that lifts the data in the fed cells into a line by line spreadsheet?
Is there a way I can get excel to look at a folder, then every workbook in it, the at the 2nd tab in every workbook, then list the cells as described above? I am not after a consolidation of this data, but a full list?
I have this function that works on line 3 and if the conditions are met, the result is 1
=SUMPRODUCT(--(IfColor(B3,$A$76)*(SUMPRODUCT(--(D3D4)))))
Here how it works, if B3 is the same color as the reference cell $A$76 and D3 is different than D4 then the result is 1
I would like this function to work from line 3 to line 60 and return the total of lines where the conditions are met. I'm thinking of a =COUNTIF function but can't get something to work. If there is a simpler way, it's even better. The IfColor is a function I wrote in VBA,
i hv following code
(i use generate macro)
my question is how to arrange the code from one line to multiple like :-
following code show in excel macro environment is one striaght line.
' Create new var on yr , and replace 2006 to CY06.
ActiveCell.FormulaR1C1 = _
I have a multiple line texts I want to convert it in single line like for Example:
The following contents is to be converted in a single line
contents:
[aaaaaaaa] [bbbbbbb]......so on
[cccccccc] [ddddddd]......so on
[eeeeeeee] [fffffffffff]......so on
Result:
[aaaaaaaa] [bbbbbbb] [cccccccc] [ddddddd] [eeeeeeee] [fffffffffff]....so on
I would like to be able to compare the "standards" in Column A with the Assessment status in Column B and the Assessment period in Column C in order to generate a compact list in another workbook (ideally) or tab (if not). A list might typically contain all the standards that have been M (mastered) during a given Assessment period. I realize that filters would achieve this to a large extent but I was hoping to automatically populate another worksheet or tab.
View 3 Replies View RelatedI'm trying to do is develop a formula to:
- look at one column in sheet 1 and if a value in that column equals a value in another column in sheet2. then, display an X
I have a need to populate a summary worksheet using two variables to find data in two or more other worksheets.
I find writing out what I want helps some times so let me try it here.
So my variables are:
Product (there are 22 products)
Supply Less than (inset number)
These are the two criteria I want to use to produce a result.
The next issue is I have 300 stores that carry said 22 products. Each store has a unique number 0001, 0002, 0003 etc. So in a separate worksheet I have a list of the store numbers, and then the products. So each product has the store's number to the left in Column A, Column B has the product name, Column C has the quantity on hand.
What i would like to do on the summary page is select the product, and then select the supply less than or equal to 'x' and then have the stores with the selected product less than or equal to x display below.
The last part of this is then to display (data from an other sheet) on the summary page which contains the quantity of the product selected available at the warehouse for that store.
I'm looking to work out an IF statement based on a series of dates entered to give 1 of 3 possable outcomes. Where:
A1: todays date (exmaple =NOW())
B1: due date (exmaple 10/02/09)
C1: completion date (example 12/02/09)
D1: status (overdue, outstanding or completed)
D1: =IF(A1>B1,"overdue",IF(A1<B1,"outstanding",IF(C1<=>A1 & B1,"completed")))
I know the last part is totally wrong (symbols arranged in that manner), but to clarify i would like the date entered in C1 to override the other statements in the fomula to make it read "completed". If no date is entered in C1 then the formula will return either "overdue" or "outstanding" depending on the other dates in A1 and B1.
I'm looking to find a formula to calculate the red cell (actually all the cells in the "type" fields) by determining:
1) If the Product in the table matches the one in row 3
2) If the dates in table columns H, J, L, N and P took place in the month before the billing date
3) If those two conditions are met, sum the matching columns in I, K, M, O and Q.
For example for the red cell would currently equal $11,380.02 as the only revenue collection dates in range would be cells I1 through I3.
I have two excel files. The data is arranged by column. I want to take 3 or more cells (from three different columns) from a particular row and match them up with three cells (in 3 different columns but in the same row). The three cells should be in the same row. However the matching row may appear in a different order in each of the two lists. When Excel finds a match, I want it to return true. How can I go about doing something like this?
I have attached a file explaining what I mean. I am trying to match the three cells in list one with three cells in one of the rows in list 2. However the number of cells in each list is different and there are some cells in list 2 which are not in list 1 and vice versa. There may also be extra columns, which I have not included for the sake of simplicity.
I have data in column A which is the correct data. I also have data in other columns that I would like to compare to column A. If the data is the same, then it moves beside it, if not then it leaves a blank cell.
I have looked for macros and formulas to do this for ages but i cant seem to find one. Here is an example
BEFORE
A B C D
1 1 2 1
2 4 3 2
3 5 4 4
4 7 5 6
5 - - 7
6 - - -
7 - - -
AFTER
A B C D
1 1 - 1
2 - 2 2
3 - 3 -
4 4 4 4
5 5 5 -
6 - - 6
7 7 - 7
See the attached spreadsheet. the range D5:D20 is dependent upon the value entered in D2. the range K4:K64 contains all values I would like entered into D2. For each value entered in D2, the range E21:H21 calculates 4 statistical values. I would like these entered into the 4 columns in Table 2 (L,M,N,O). The first value has already been done. Can I automate this somehow to plug in all the threshold values I have listed in Table 2 (column K)???
View 2 Replies View RelatedI have a spreadsheet containing donation data for a non-profit organization with over 25,000 rows. The spreadsheet contains a lot of data, and is sorted by donor name, donation date (when the donation was made) and the feedback date (feedback on how the donation was used).
I'm looking to analyze how many transactions were made by a donor within 10 days of receiving feedback for a previous donation. The condition to check for is
(donor name is the same) AND (donation date >= feedback data for a previous donation) AND (donation date <= feedback data for a previous donation + 10 days).
How do I best perform this comparison? For instance, a donor may have made 20 donations, so I need to compare these donations amongst themselves and flag the ones where the donation date within 10 days of the feedback date for any other donation. I need to perform this exercise for each individual donor.
I am trying to find compare the value in one cell with an array of cells. My problem is when I try to carry the formula down to the next line - my array changes as well. For example:
Before
After
01915
[Code].....
So, if Before is Column A and After is Column G, the formula I have right now is =INDEX(A2:A3349,MATCH(G2,A2:A3349,0)). When I try carry the formula down to G3, G4 and so on, the array changes as well.
In one excel book I have 2 sheets.
1. One sheet (request type wise) contains request type and the phase which it belongs to. eg request type A belongs to phase 1 and request type B belongs to phase 2. (like wise there are 212 request types divided into 8 phases)
2. The second sheet contains raw data (request type) where the request types are randomly arranged.
What I am trying to do is:
Compare the raw data of sheet 2 with standard data in sheet 1 and paste the phase to which it belongs to with a help of macro/code, since the standard data (212 request types with 8 phases) is huge.
I'm am trying to take multiple column-groups and sort them by product ID number.
I am trying to put together a sales record for the past 26 months for around 2000 different items and sorting them manually would obviously be a nightmare.
I would like to sort them so that all the drugs with the same NDC (prod ID) have the correct data for each month displayed on the correct row for that NDC. Right now I have each set of data for that month sorted by product ID number but some months we used certain products and others we did not. Is there any sort of Macro that I could use? What I am trying to do I attached two files. The "before" and my desired "after".
starting point.xlsx
end product.xlsx
[1] compare (row) ranges across two columns with an unequal number of rows (column A [number] to column C [number])
[2] save each result of [1] where there was a match in column B
[3] for each row where there was a match (now stored in B), compare the value from the same row in column D (date) with the same row of column F (date), and store the result in column E
[4] count and message box the final number of matches from column E
Data Example:
A B C D E F
----- ----- ----- ----- ----- -----
12345 match 12345 11/1/2011 match 11/1/2011
77777 34345 ... ...
A and C numbers match, so check same row date in D that does match date in E, place "match" in E, then count and display.
Sub other()
Dim range1, range2, range3, range4, range5, range6 As range
Dim x As Variant, y As Variant, z As Variant, a As Variant
[Code].....
Can I put two variables into a SumIF? forexample I want to sum Column C if Column A is equal to Apples and Column B is equal to Oranges, then sum Column C. Is there a quick formula?
View 3 Replies View RelatedI'm using Excel 2010 on Windows 7.
I have 2 worksheets. One has Employees and the devices they have. Last name, First Name, Device each in their own colulmn. Many have more than 1 device so they have multiple entries on seperate rows.
Another worksheet has Employees and thier location. Last name, First Name, Location. Again, all on seperate columns.
It would look something like this
Sheet1
Lastname
Firstname
Device
Johnson
[Code]...
So I'm tasked with combining them into 1 sheet with last name, first name, device and location. The issues I'm having are:
1) A team member could have multiple devices
2) A last and/or first name can appear many times, so a simple Vlookup against lastname won't work - it has to somehow also compare against both.
I need of a formula that will look through a list of text strings, spread out through two columns) and compare each one to a user defined entry. When it finds a match against this entry I would then need it to compare another set of text strings on the same row as the entry that it previously matched against.
If it was to find a match on only the original text string it would need to return a true result and if it matched on the original text string and also one of the other text strings on the same rowit would need to return a false result.
I currently have a formula to compare all text strings against the user defined entry and if it is contained in the first 2 columns it returns a true result and if it is contained in both ranges of text strings it returns a false result but i need it to apply the rules per row.
Below is the formula that I am currently using if this helps as a starting point. If you need a subset of the data I can supply this.
Current formula:
=IF(SUM(IF('HBO Frequency Projections.xls'!NOT_HOMEBASED"",IF(ISNUMBER(SEARCH('HBO Frequency Projections.xls'!NOT_HOMEBASED,C5)),1)))>0,"NOT HBO",IF(SUM(IF('HBO Frequency Projections.xls'!HOMEBASED"",IF(ISNUMBER(SEARCH('HBO Frequency Projections.xls'!HOMEBASED,C5)),1)))>0,"HBO","NOT HBO"))
I am looking for a syntax to ask a question with three variables
As an example
If
Blade size = 114 (only 3 blade size choices)
and
the Panel Size is between 500 and 700 ( This will also be dependant on the blade size)
and
the tilt bar is clearview (Three choices)
then
the result will be x = 182
The answers will depend on the above variables: 154, 159, 179, 177, 182 and 202
Screenshot 2014-06-26 14.21.41.png
Screenshot 2014-06-26 14.23.53.png
Screenshot 2014-06-26 14.26.11.png
I need to count up the scores from a questionnaire I have developed, which I am having trouble coming up with a formula for.
I have 5 different types of sales channels in column A. In columns C, E and G there is the call method they use (inbound, outbound or both) with a 'Y' / 'N' option next to it. Basically, I need a formula that if a sales channel has a Y next to Inbound, then the value 'Inbound' to be returned to the destination sale and if it has a Y next to Outbound then the value 'Outbound' returned to the destination cell and so on.
I have attached a spreadsheet to make my problem easier to understand.
I'm trying to use two variable to calculate a count.
In my attachment I have Name, Balance, and Disabled for the data.
I would like to find out how many names has a negative balance and have a "Yes" for Disabled
In the Summary part, I can find out the count of who is disabled, but can incorporate the negative balance.
This code is functional but very redundant and I figured that has to be an easier way to go about this.
View 5 Replies View RelatedI have in cell c40 a data validation list with source equal Royal (=Royal) has been created.
Royal is a namebox that has a defined list of 7 options (6 actual options plus one that says "select via drop down") :
FA4 = Select via Drop Down
FA5 = option 1
FA6 = option 2
FA7 = option 3
FA8 = option 4
FA9 = option 5
FA10 = option 6..............
The concept is that FA5 is associated with a value defined in FB5, FA6 is associated with FB6, so on
What I want to happen is when I select a value via the drop down selection in C40 eg. "Option 1" that in cell D40 the value associated with Option 1 (found in FA5) returns the value found in FB5 ($10).
How do I do this? I have tried a number of "if" statements but no luck.
I'm trying to count a range but I have multiple criteria which I would like to use, I have attempted to use countif/sumproduct with no avail. The data is spread over two columns, one contains the rank of various employees and the other is the number of hours they have worked. I am trying to count how many employees fall in to set hour ranges.
View 2 Replies View RelatedWriting a formula for the info below. Here is what I need basically. If B2>2, then I need it to count all central and field, STK and NSTK in column D.
location
days
stock type
Central
3
nstk
field
1
stk
[Code] ...........