Overwrite Input Based On Multiple Criteria
Nov 22, 2006
I have a spreadsheet that's generated from a download. I need to modify one column of cells based on multiple criteria. Started to try with a nested If statement, but soon passed the limits allowed. Not sure if some kind of VLookup or VBA formula. To summarize what I'm looking for: If column A has a value of any of the following (10D, 11Z, 10Z, 2CS), then I need to change the Rate in column D based on the Years left to maturity in column B. The Rate percentage to be used is based upon the table shown in the attached spreadsheet. In addition, there is an exception if Column A has a value 2CB and an inventory # of 9300276 or 9300277 in column C, then I need to change the rate in column D based on the table as well. Otherwise the rates stay as originally imported.
View 3 Replies
ADVERTISEMENT
Sep 4, 2006
I have a workbook with 2 - worksheets "DATA" and "LETTER".
The "DATA" sheet has columns "First Name" "Last Name" "Address"...etc.
The user can enter in either a "X" or a "x" in cells B6:B100 on the "DATA" worksheet.
After the user enters a X or x and hits the macro button I need it to copy certain cells from "DATA" worksheet to "LETTER" worksheet. I have listed a example below.
In this example if a "X" or "x" is entered into B6 on "DATA" worksheet and the macro button is hit, then: ....
View 9 Replies
View Related
Apr 29, 2014
I have here an example of how I might extract data from a source workbook and paste it onto a template workbook, using autofilter to find the rows that are needed. It is currently set-up to take rows that match "AREA 1" or "AREA 2" in column A, along with a number of other criteria in other columns.
What I want to do is add into this macro, a way for the user to be able to choose the filter options will be used for column A of the source workbook. So if they want only "AREA 1", they can choose just that, or if they want "AREA 2", or perhaps both "AREA 1" & "AREA 2", or further additional options. They need to be able to specify just one, multiple, or all of the filter options with regards to column A of the source data.
In effect what I need is a user input box or form with a dropdown menu that will give the filter choices based on column A of the source data, and then for the code that copies the data across, to do so based on which choices the user makes in the filter menu of the input box.
I have uploaded examples of the source data workbook and the target template workbook. The macro exists within the template workbook.
[Code] .....
How it may be possible for the user to be able to specify, one, multiple, or all available filter options for column A of the source data, via a user input box or form.
Attached Files :
Sales Analysis Template1.xlsm
SOURCEDATAEXAMPLE.xls
View 2 Replies
View Related
Mar 18, 2013
I maintain the data flow at my work. We send and receive the data using excel files with specific formatting that I then upload to the database. Each time I send or receive the excel file I must log them, this is what my code question refers to.
I use RDBMerge to merge all the contents of the 100 plus excel files into one worksheet. The first part of the macro cleans up the merge data for use in the log (i have attached an example of the clean data and finished log).
The blue shaded area of the "Raw_Data" is what the clean data looks like, the yellow column is what current macro records for each record.
As you can see by the example the Raw_Data is only two files LL_LLL_BOB_ToLLLLL_20121228_01 & LL_LLL_BOB_ToLLLLL_20121230_01, each with more that one record.
The log code in column "H" Is based on this criteria:
First Letter of the Unique ID in column "E" - O, M, or L
Program Type in Column "F" - U or R
1. O-U = U
2. O-R = RU
3. M-U = U2
4. M-R = R2U
5. L-R = R
You will note that Columns G-R of the "Log Sheet" correspond to the "Record Type" found in Column "G" of the "Raw_Data" sheet.
This is the area where my skill at using scripting dictionaries fails.
The results for the log list each file only once, but the log code for each corresponding "Record Type" in columns G-R of the "Log Sheet" must contain each unique instance of the code. In other words
if LL_LLL_BOB_ToLLLLL_20121228_01 contains an O-U with an "A" Record Type and an M-R with an "A" Record Type; then, on the log sheet there needs to be the codes "U/R2U" in the cell intersection of the LL_LLL_BOB_ToLLLLL_20121228_01 record row and "A" column (which is column "G")
So, If the File contains one of each code for each Record Type the corresponding cell must house one of each code separated by a "/" without any spaces. This means the cell value could no code, or one code and all the variations in between to all five codes. Also, for ease of human reading the log codes should be concatenated in the 1-5 order that I listed them in (U/RU/U2/R2U/R)
Here is my code so far.
VB:
Option Explicit
Sub test()
Dim dic As Object, a, i As Long, rng As Range, e, w, n As Long
Set dic = CreateObject("Scripting.Dictionary")
[Code].....
View 1 Replies
View Related
Jun 29, 2014
I have a directory which contains many files, they are all names based on their locations. eg. Burwood-File1.xls,Burwood-File2.xls,Burwood-File3.xls etc
I have a master that which will contain the branch in the first column.
I have defined the directory location in a separate sheet as well as email template.
When I click on the Send Email button I want ti to attach the files that match the Branch name.
The Branch list will constantly grow.
View 2 Replies
View Related
Feb 24, 2014
Im struggling to find a formula to populate cells based on values to be checked in two fields (ICODE, COMPANY) as shown below.
In the example here, all records where ever the ICODE = 49, i need to populate the fields(columns) AD1,AD2,AD3 or AD4 with the product name of the same company where the ICODE <> 49. There could be between 1-7 products per company.
CURRENT SHEET
ICODE PRODUCT COMPANY AD1 AD2 AD3 AD4
89PENS ABC STATIONERY CO LLC [code]....
The attached example file may be a better alternative to view this problem.
View 2 Replies
View Related
Feb 1, 2012
Worksheet 1: In column A I have a people's initials. In coumn B I have text boxes with miscellaneous text. (The same person could have multiple rows within this sheet.)
e.g.
AAA blue
BBB orange
AAA round
CCC smelly
AAA elongated
Worksheet 2 I want to show:
A2 = initials, B2 = first text box associated with that person, C2 = second text box (different row) associated with that person (if applicable), D2 = third text box (different row) associated with that person (if applicable), etc.
e.g.
AAA blue round elongated
BBB orange
CCC smelly
View 3 Replies
View Related
Mar 26, 2014
Have been going round and round on this! I have a set of data that shows sales dollars by weeks. Currently, that data is in a Pivot table. I'd like to have a macro that selects the weeks (1-52) based on my input. For example. If I want to see weeks 5-52, I want to be able to input "5-52" somewhere and have a macro run that will select weeks 5-52 in the pivot table instead of having to go in and manually select each.
View 1 Replies
View Related
Dec 9, 2011
I am trying to create a UserForm in Excel 2010 that will look for a part from our database (or on a specific Sheet, possibly a hidden sheet), then add specific data from that part's row into a new line.
More Specifically:
| Part # | Description | Category | SubCategory | Cost | Labor Hours | Weight | Etc |
I want to be able to input the Part #, and have it automatically add specified information to sheets 1 and 2 and those two differ. 1 is our Quote Sheet, and 2 is our bill of materials.
Also, I need each piece of information to go to a specific column (ex. Cost always goes to Column K)
What I have now is a broken UserForm that references a DOC file with a Table, but I need something a good but more complex.
Ideally, what I want is to have 4 ListBoxes where you choose Category --> SubCategory --> Make --> Model then have several CheckBoxes below that would determine whether to add a part (Cost, Labor, Weight).
The reason for this is because we have no need for the Cost or Labor Hours in our Bill of Materials, but need that information for our Quotes. And we generally don't need Specific Dimensions in our Quotes, but DO need them in the Bill of Materials...
Can I make it so that it will insert Pre-Specified information into both sheets at the same time?
View 1 Replies
View Related
Aug 7, 2013
I'm starting a dashboard, where on the front page I have two combo boxes on the left, and three empty fields to the right. I'd like the three fields to the right to auto-populate table-based values depending on the chosen criteria from BOTH fields (by store and month/date). I've attached a sample of what I've got so far. I've only provided three tables for this example, and I have a table with the same column/row titles for each metric and I have three different metrics I'd like to auto populate: COGs, Sales, and GM% or in the example, metric 1, metric 2, metric 3. No pattern in the table values, just wanted to populate the fields quickly. All fields are organized by store/month-date and I've set up a link to my combo boxes on a calculations tab.
View 2 Replies
View Related
May 14, 2014
I track distribution using 1 workbook for each sales rep. Each workbook has a separate worksheet (scorecard) for each of their locations showing which of our products are used in that location, for various functions. Each of the locations has a segment (bar, nightclub, casual dining, etc.), and a class (AA, A, B, etc.). All of the scorecards are identical, and are in the same file folder on our shared drive.
Sample:
Location A Class:AA Segment: Sports Bar Rep: John Smith
Product: Product X Product Y Product Z
Well: 1 0 0
Back Bar: 0 1 0
Cocktail Menu: 0 1 1
I was unable to find an HTML maker so this is not an actual sample but what I'm looking at. The above data indicates that for location A (Sports Bar, Class AA), Product X is used as the well, Product Y is on their back bar and their cocktail menu, and Product Z is not used at this location at all.
I need to set up an analytic workbook where I can sum data based on multiple and grouped criteria, and for multiple reps ie- display a scorecard that shows totals for nightclubs and sports bars, class AA, A, and B, for a particular rep; OR display a scorecard that shows totals for nightclubs, class A for a group of reps. Ideally I would like to do this using drop down menus that allow the user to select multiple criteria in each of the drop downs, having the data auto-populate based on those selections.
View 2 Replies
View Related
Sep 23, 2013
Now the problem, Each client has a sheet in a workbook. In each sheet, I input the number of days of service available to that particular client in a single cell. We'll say cell [G8] is the cell used for this input in all sheets. If the client has 30 days of service available then we type [30] in cell [G8]. Each sheet is a 100 day template. I want to shade in dark grey and lock from input the days NOT available to the client based on the days of service input in cell [G8]. If 30 days of service are available in cell [G8] then the remaining 70 days on the 100 day template would be shaded dark grey and locked from input. That is, day 31 through day 100 of the template would be locked and shaded. Each individual day has multiple cells in a column. I want to lock and shade each column of cells in each day not available.
View 5 Replies
View Related
Mar 18, 2014
I've got a matrix combining actions on the left hand side (Col A) and owners on Cols B to F. I have drop down list for each action/owner combo: N/A, OK, KO, TBD.
Owner #1
Owner #2
Owner #3
Owner #4
Owner #5
Results
Action #1
OK
TBD
N/A
N/A
N/A
OK
[code]....
I would like to create a formula in Col G that does the following:If any of the values in columns B to F equals OK, then OKIf any of the values in columns B to F equals KO, then KO I've been tinkering with multiple IFs and quite rightly got bogged down as too many criteria to take into account...
View 4 Replies
View Related
Jan 23, 2012
So, another question with regards to this sales sheet that i am setting up. I want to be able to take the information below and for each of the people listed (i.e. by their initials) i would like to...
1.) Take their associated % (Columns C, E, G)
2.) Multiply the % by the Revenue (Column H)
3.) Sum up the total for each of the names (Locatedin Columns B, D, F)
4.) Only sum up the ones that occur in Month 1 of the Year 2012
I have tried multiple ways with a Sumproduct but none of them have worked. I would love to be able to do this with a single formula without having to add another column, for instance, to track what MonthNum it is. I tried using some kind of offset, but i could never get it to work.. For instance to look through the range, find the initial go over 1 column to the right and multiply that % by the revenue if the Month is 1..
Date (A)Rec1Name (B)Rec1Pct (C)Rec2Name (D)Rec2Pct (E)Rec3Name (F)Rec3Pct (G)Revenue (H)1/1/12AD25.00%RU25.00%BF25.00%1000.001/1/12AD25.00%RU25.00%PNF25.00%2000.001/1/12BF50.00%00.00%00.00%1200.002/1/12BF50.00%00.00%00.00%1000.002/1/12BF50.00%00.00%00.00%1000.003/1/12AD25.00%RU25.00%00.00%1000.003/1/12AD25.00%RU25.00%00.00%1500.004/1/12AD16.67%RU16.67%BF16.67%1500.004/1/12BF50.00%00.00%00.00%1500.00
View 3 Replies
View Related
Sep 25, 2013
I would like a macro that select all cells based on multiple criteria. It's a big sheet, but I've made it smaller in the following example.
In row 1, there are 2 headers, in B1 (electricity) and C1 (gas) In column A, there are 3 houses, in A2, A6 and A10. The blocks of cells in between (e.g. B2:B5) have 2 headers, 'period' in B2, and 'account number' in B4. B3 and B5 have a list of options in them.
I want to select cells that meet certain criteria. Either electricity or gas, and either period or account number. So, if in cell D1 I choose electricity and in D2 period, I would like to select B3, B7 and B11. If I choose gas and account number, I wish to select C5, C9 and C13.
View 1 Replies
View Related
Feb 22, 2014
I have a spreadsheet with 5 columns. A1 to A5
Entries are made into columns A2 to A5 but only ever one entry across all cells
If an entry is made into A2 to A5 the I need a "P" to be placed into A1
View 4 Replies
View Related
Dec 23, 2013
I am looking for a way to say
IF A2>9 then B2 returns bronze
IF A2>24 then B2 returns Silver
IF A2> 49 then B2 returns Gold
IF A2>99 then B2 returns Platinum
View 9 Replies
View Related
Jul 16, 2014
Add Multiple Values Based on Criteria
Search for the word "Clear" in the Task column. If the word "Clear" is found, go to Value column. Add all values starting from "Clear" down to the end of the list. If it is larger than 10, return the word "OK". Otherwise "Poor". If larger than 20, return the word "Good". The length of the list is variable.
Here is the sample worksheet (Please click).
View 7 Replies
View Related
Dec 15, 2009
if in column A in some row is text "today" then delete this row and delete all rows until in row B wont be empty cell. I have start (If in A is "Today" then ... ) but thas's all
View 5 Replies
View Related
Oct 1, 2011
I'm trying to create an Excel based dashboard for my company. One part of the dashboard is a Top 20 Accounts based on territory, region, or district. Each of these three areas has source data in a seperate table. I need the top 20 vlookup to pull from the territory table if I enter a territory name in cell B20, pull from the region table if I enter a region name, and so forth. There are 10 different regions, 2 different districts, and 51 different territories.
I'm thinking that the answer somehow involves an IF statement. e.g. If cell B20 equals a region name, then perform the vlookup on the region table. If it contains a district name, pull from the district table. Otherwise pull from the territory table. I'm also thinking I may want to list all of the territories, regions, and districts in their own seperate column so the formula can say something like "If cell B20 is found in column M, pull from the territory table, if it's in column N, pull from the region table.
View 3 Replies
View Related
Aug 29, 2013
An example of my data is:
Column A Column B Column C
ABC DEF Answer 1
ABC GHI Answer 2
ABC JKL Answer 3
I need a formula that checks both column A for text (ABC) and column B for text (JKL, two different criteria) and give 'Answer 3'. There will a lot of data and it will not always be the bottom answer.
View 2 Replies
View Related
Dec 7, 2006
I am attempting to create a macro that will delete an entire row, based upon multiple criteria. The data looks something like this:
-----Column A-----------Column B---
1.-- Computer_X ------- Software_A
2.-- Computer_X ------- Software_B
3.-- Computer_X ------- None
4.-- Computer_Y ------- None
5.-- Computer_Z ------- Software_A
6.-- Computer_Z ------- None
I would like to delete the entire row if:
1) Column B entry is "None"
AND
2) Column B has entries other than "None" that match Column A
So I do not want to delete a row with a unique name in Column A and "None" in Column B. I only want to delete the rows that have "None" in Column B, and multiple entries for the match in Column A.
Using the data example above, Row 3 and 6 would be deleted. Row 4 would not be deleted, since Column A (Computer_Y) had only one row of data, with "None" entered in Column B.
View 9 Replies
View Related
Dec 15, 2008
Ihave the following headings in a database
CSAMeeting DateMeeting typeCompanyInhouse Products/FundsJV Product 1JV Product 2VenueSales Manager2nd Manager (JP, KMcI SS)Inv. ManagerFilenote IssuedWorking DayNo. of AttendeesCancelledPack RequiredCompliance Required DivisionReason for no ComplianceDate sent to
CompliancePack/Literature
UsedPack Path
i want to use the Dsum to produce the total amount of managers met for a cvertain sales person for a certain type of meeting. I trued thr following but it is returninga #value
=DSUM(A27:V1028,13,AND(3=$BH3,9=BI$2,120))
where 13 applies to the number of managers column
3 is the meeting type
9 is the managers name
12 ensures that its only counted if teh meeting note is done.
View 9 Replies
View Related
May 22, 2008
I need a formula that will take this information and automatically insert a row, a total, and then another row every time the Product (Column C) or the Account Number (column B) changes. The totals should be under Columns D and E only. But I don't want it to duplicate the results when both the product and the Account number change.
Beginning data: ...
View 6 Replies
View Related
Apr 23, 2013
In the below data set i have the data ranked by branch and center by value. I am trying to create a summary page that looks at the data set by branch center and rank and brings back the debtor code. The letters above headers represent the columns.
a b c d e f g
BR
CNTR
DEBTOR NAME
2012
Rank
[Code].....
View 1 Replies
View Related
Apr 13, 2014
I am trying to formulate a commission form which looks up the name of the sales person and then displays a list of the deal names withing Q1.
View 1 Replies
View Related
Dec 7, 2013
What I require : Column F to be blank as long as the following is true:
1) Column A (Order) cannot be blank.
2) Column E (Status) has to be equal to either "Completed" or "Successful".
3) Column D (Sales) has to have the same date as Column C (Contact), but due to bad data quality each order number can have several rows and as long as the Sale date from one of the other "Duplicate" rows equals the contact date it needs to be counted. This is as long as there is not already a row for the Order that already has Sales / Contact day matching.
In the attached spreadsheet, I have highlighted the rows that should be blank in column F in green. My attempts at formulas to count this are in the columns highlighted in red.
WORKAROUND.xls
View 1 Replies
View Related
Feb 10, 2014
I have created a sheet that calculates a score. Based on that score a determination is made to do one of three things - Nothing, Peer/Manager Review, Review Required.
In addition it was requested now that I make an overall decision for "Review Required" based on multiple triggers. My thought was to create a table from outputs of each element on another sheet, and then trigger off the table. What I am stuck on is using the multiple choices to trigger the message - Below are the choices of which each or a combination of each should trigger "Review Required"
Calculate Review Required
Total Score 'Review Required
Element 1 "
Element 2 "
Element 3"
Element 4"
Element 5"
Element 6"
Element 7"
Element 8"
Element 9"
Element 10"
Element 11"
Element 12"
Element 13 "
Element 14 '20 or more = reveiw
Element 15 '20 or more = reveiw
Element 16 '20 or more =review
Element 17 'Sum 20 Total of above if less than 20 in each
View 5 Replies
View Related
Mar 30, 2009
I need to remove any rows containing "EEE" in the size column.
I also need to remove any discontinued ("D" in the "Type" column)styles that are currently not in inventory. For example 05-5490 does not have any available in either width "D" or "EW" and should be removed; however, 28-112 does have stock and should remain in the spreadsheet. Anything with an "F" in the "Type" column should be left alone.
View 13 Replies
View Related
Jan 6, 2010
I have a worksheet with several columns of data that I have to summarize elsewhere on the sheet. Here are the columns:
ID
Status
Joined
Name
Name can take on 4 possible values. For each value in Name, I want to count the number of cells in a column that fit certain criteria. Here are the counts I want to make:
Status OK: Status is a date.
Joined OK: Status is a date and Joined is a "1".
There are going to be about 500 records to count. I've already implemented these counts in a Windows scripting language that interfaces with Excel, but I know there must be a more direct and faster way to do it entirely in Excel, whether with VBA or writing functions into cells.
View 3 Replies
View Related
