Fill Cells Based On Corresponding Cells Matching 3 Criteria
Jul 1, 2008
I am working on a spreadsheet for a shoe company. I have separate columns for the size, model, color, and item number of a shoe. I get everything except for the item number from a written document; I then have to find the item number for the shoe from another excell document called the Master List.
I was hoping there would be a way to have Excell auto-fill the item number for me. For example, if a shoe is a Red, Athens (the shoe model),size 12, its item number (which can be a pain to find) listed in the row of the Master List is aaabbb. So I want to just enter in the size, color and model number, and have Excell find the item number for me, and fill it in.
I have enclosed an example. Sheet 1 is the sheet I would be working on. Sheet 2 is a portion of the Item master list, which is actually 50k lines.
View 8 Replies
ADVERTISEMENT
Apr 4, 2009
I just stumbled onto this forum while Googling around, and it looks great. I'm pretty new to VBA coding so please bear with me. Effectively what I'm looking to do in this mock-up file, is anywhere the currency is "EUR" in column A, I'd like the relative cell in Column G to change to "INSERT TEXT", else I want it to untouched (i.e. to keep the comment it currently has).
View 6 Replies
View Related
Jan 1, 2007
I have a userform with text boxes and a combobox referencing a named range in the spreadsheet. What I'm trying to accomplish is when I click on a name in the combobox, I would like the text I'm adding to this name to be on the same row. Currently a new row is created with all this information instead of being added to the existing row and I end up with duplicate names in the combobox.
View 6 Replies
View Related
May 23, 2014
I am trying to build a staff roster. The staff rotate over a 4 week cycle. the name of the staff member, and their shift needs to be looked up from the key then matched with the particular week. the name and shift then need to populate specific cells.
I have attached the worksheet so you can see what i am trying to achieve.
View 2 Replies
View Related
Jan 27, 2005
how can I automatically fill cells with certain colors based on the value of the cell. (i.e. I want to search an entire workbook and fill cells with values between 80 and 99 green, 60 to 79 yellow and 0 to 59 red.)
View 9 Replies
View Related
Jan 8, 2014
I have a sheet containing a list of client name,the date they've been assigned, the type of account they have, the status of the account, and the last update date.
What I'm trying to accomplish is to extract the client name and the type of account on that table that shows as "complete" in status. However, it gets complicated since I need to separate the data that had been 'completed' under the same month, with those completed the other month.
For example, I have been assigned 2 clients this January. I closed one in the same month, and the other closed at February. The first client would be listed under the first table (Completed within the month) and the other would be on the second (Completed on previous months)
Since this would be monthly, I am thinking if it would be more efficient to include the table per sheet (as one sheet corresponds to a month), or consolidate everything to a single sheet... I'm not sure which one would be best.
View 2 Replies
View Related
May 28, 2009
How would I write a sumif function to only add consecutive cells that match the criteria? Here is the formula I am using right now, but it adds all of the cells that match the criteria, not just the consecutive ones. Also they need to be consecutive cells from the current cell (in the formula below i would be referring to A80)
=IF(A81<>A80,SUMIF(A$5:A80,A80,H$5:H80),"")
More data:
Column A is a group of dates, but not in order, they are slightly mixed up
Column H is a monetary value.
View 8 Replies
View Related
Oct 3, 2009
How do you load an array with values from cells matching a criteria?
I want to look for the number 1 in row 5 and put the Adjacent values in Row 2 in an array.
So here my array should have 1,3,6
Dim CountOne As Integer
CountOne = Application.WorksheetFunction.CountIf(Worksheets("Tool Administration Form").Range("B8:AE8"), "1")
Dim All As Variant
Dim rCell As Range
Dim ArrCnt As Integer
Dim lArrVar As Long
ArrVar = 0
Do
For Each rCell In Worksheets("Tool Administration Form").Range("B8:AE8")
If rCell = 1 Then
ArrCnt = ArrCnt + 1
All = Cells(-3, 0).Value
End If
Next rCell
Loop Until ArrCnt = CountOne
I keep getting errors... I don' tknow how to fill the array properly wiht the values in the cells.
Example:
A B C D E F G
1
2 1 2 3 4 5 6 7
3
4
5 1 2 1 3 3 2 1 4
View 9 Replies
View Related
Nov 14, 2006
I want to read information from the "Mapping" sheet, then find information in the "GLEX" sheet that correspond to the read information, then copy the information that was found in "GLEX" to the "Calc" sheet and add it all together and display the sum in a particular cell in the "Report" sheet.
The "Mapping" sheet is a mapping of information to show what makes up a particular total and where it must be entered in the "Report" sheet. The "GLEX" sheet is the output from a financial system and the source of the information that must be "reformatted" to the "Report" sheet. The information is only an extract as the whole totals to around 10MB. I have also deleted the "Report" sheet as it is fairly big on it's own, but the principal will be the same, I think. (If i should upload the "Report" sheet,)
Step 1:
"Column A" and "Column B" of "Mapping" are the starting points. The macro must read the values in "Mapping" "Column A" and "Column B" and remember them. Then it must find the row in "GLEX" "Column A" where the information from "Mapping" "Column A" matches and where the information from "Mapping" "Column B" matches with the information from "GLEX" "Column E".
Step 2:......................
View 5 Replies
View Related
Jun 2, 2007
I am using the below code to count records in a range that meet multiple criteria.
myCount=0
For Each dCell In Range("MyRange")
If dCell = var1 And dCell.Offset(0,-1) = var2 Then myCount = myCount + 1
Next dCell
Is there a faster way to count multiple criteria in a VBA procedure than what I'm doing, but without using worksheet formulas? I tried "WorksheetFunction.Sumproduct" like you would in a worksheet formula, but that didn't work (unsurprisingly).
View 4 Replies
View Related
Aug 28, 2007
On my worksheet (which I have stripped down significantly to be able to upload here), I am looking for a way to copy certain cells over when the TCR (machine) numbers match. So, on the spreadsheet, if there is a 1 on the De La Rue sheet in the A column, it will then copy the value that is in cell B (directly next to it) and paste it into the balance sheet tab in column J in the row that has the same number in it. It would then do this for all machines in the balance sheet.
Then it would go to the "Ecoin Amounts" sheet, and do the same match, copy, paste, but this time it would be if the machine number matches, it takes the value from column D and the value from column H and pastes these into the balance sheet sheet in columns C and E respectively.
As you can see from the balance sheet tab, I tried using a VLOOKUP function, but when I import my initial data to the sheets, and manipulate it the way that I want, it gives be a reference error. I could possibly circumvent this by writing the VLOOKUP amounts as part of my macro, but I wanted to see if this could be done any easier.
View 2 Replies
View Related
Apr 1, 2008
Well, after reading "Best Practices", it looks like I've got lots to learn about Excel and VBA Hope I don't break too many rules. see code below. I have a form that uses a set of dynamically filled combo boxes. The difficulty I'm having is in filling the combo boxes with correct data when updating an existing record. As per the code below, the Activecell. offset(0, #) references in the "if" statement in the For..Next loop process correctly, but Activecell references that fill the combo boxes seem to behave like an absolute references to Row 1, which are the Column Names. I've tried creating an Activecell.offset(0, 0) before every fill combo box statement and then using Activecell.offset(i, #) to reference the relevant cells, but to no avail. Obviously, I'm missing something here...
Private Sub cboUserName_change()
Dim usrnam As String
Dim found As Boolean
Dim i As Integer
Dim intRowCount As Integer
usrnam = cboUserName.Value
found = False
If usrnam <> "" Then
ActiveWorkbook.Sheets("Data").Activate
Range("A1").Select
intRowCount = Range("A1"). CurrentRegion.Rows.Count - 1 ................
View 2 Replies
View Related
Jun 29, 2006
I have two columns with the same data just totally different orders the third column (associated with the second) has data that I want to sort. I want to keep the order of the first, rearange the second so they match, and have the 3rd column follow the second to the proper location. i need to keep the order of column 1 so i can post into a massive spreadsheet. Theres gotta be a quick formula for this i just have no clue
View 2 Replies
View Related
Jul 15, 2014
I am trying to copy a row based on the value of a cell.
I have two sheets in my workbook and on sheet 1, I have a part number and a description. On sheet 2, I have part numbers again, but this time I the description is broken up into the format I need.
What I am trying to do is have excel search on sheet 2 for the part numbers, then copy the information that corresponds to the part number into the correct column.
I have tried using Vlookup. But if the part number in row 2 on sheet 1 match the one in row 8 on sheet 2, this will copy over the data from row 2 whereas I need row 8.
If this would be more doable using VBA, that is fine by me. I haven't been able to figure out anything in VBA or in excel formulas up to this point.
View 7 Replies
View Related
Oct 4, 2011
I need to be able to fill a cell in the same row based on values in two other cells in different columns.
Current Layout. Call these columns A, B, and C
I have 3 different states I work with, OH, IL and PA. There are different tax exemptions based on whether or not the class is RES or COM.
Conditions:
State = PA, Class = RES, Tax Exempt = 1
State = PA, Class = COM, Tax Exempt = Null (not "0")
For all other states, Tax Exempt will always = 1
I need the VBA code that will autofill column B with the proper Tax Exempt status based upon columns A and C.
The code I have now just looks at the first value in columns A and C then fills all of column B with that number.
View 6 Replies
View Related
May 1, 2009
refer to attached worksheet. If value in D7 is less than zero; then D8 = 0%
If D7 > 0, then D8 = value in C8, and this value must also be copied into 9 adjacent cells in row 8 from D8 onwards.
View 4 Replies
View Related
Mar 9, 2013
I have a list of names in Column B (Starting at B5) with assignments to them in Column A. I want the people who receive the file, to enter their name in B1 exactly as it appears multiple times in sheet. And hope to use conditional formatting to highlight (change the back ground color) of each cell their name appears in.
I've used a number of formulas in the Conditional formatting including "=(ISNUMBER(MATCH($B5:$B100,$B$1,0)))", Countif's and "Not(isnumber)..." but can't find a formula that picks up the whole text.
View 3 Replies
View Related
Mar 3, 2009
I am trying to create a spreadsheet for an online gift registry. They require that the spreadsheet have the photo's url's in a column. I already have the spreadsheet filled with my data. In the spreadsheet, Column D is filled with unique numbers, some with parenthesis, (ex. 52011, 52011(2), 34132, etc.)
I also have a folder full of images that are similarly formatted as such
"...imagesrand_name_52011.jpg". (They will be moved eventually to a webserver.) Each number in the column may or may not have a corresponding image. And the images may or may not have a corresponding number in the spreadsheet. Is there a way to generate a url automatically in a column that corresponds to the image with the matching number? And if it doesn't, just leave it blank?
View 4 Replies
View Related
Oct 24, 2013
Having a bit of trouble trying to get excel to pick up text in one sheet (sheet 2) and populate cells in another (sheet 1) if the row (row 1) labels and columns (column a) in both sheets match. hope that makes sense? I've tried googling this to no avail, i've also tried index-match however i keep getting errors.
View 3 Replies
View Related
Nov 9, 2009
I have 2 columns, the second column has some empty cells in the end of the column
What I would like to do is to fill in those empty cells but only as many as the filled-in cells in the first column
(I have already tried the specialcells(xlCellTypeBlanks) but that did not work as the empty cells are not always "blank" as well)
View 10 Replies
View Related
Dec 21, 2009
I am looking for a way for Excel to fill in cells in a column that has data sporadically in it. I am importing a text file into excel and saving it as an Excel file.
In the example attached, I need the numbers filled in all the way down to the next number. I don't care about the text, just the numbers. I realize I can copy and paste, but these files can be several hundred rows long and it seems there should be a better way.
View 4 Replies
View Related
Sep 9, 2006
i have some problem in display the highlight cell to another worksheet. Below is in module, i would like to display the highlighter cell to another worksheet.
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 9/2/2006 by user
'
'
Range("I351").Select
ActiveWindow.SmallScroll Down:=-12
Range("I349").Select
ActiveWindow.ScrollRow = 1
Range("E1:E400").Select
View 9 Replies
View Related
Feb 22, 2008
create formula/condition to colour fill different cells based on multiple Criteria.
View 3 Replies
View Related
Mar 11, 2008
how to sum/count cells based on background color [url]
how to delete cells based on background color?
View 3 Replies
View Related
Dec 24, 2013
I have a set of sheets on a workbook that contain addresses, which are all combined on the first sheet (as you can see in the attached file). I would like to use a formula to compare the addresses on the Main sheet with the other sheets, then return in the D column of Main a colored cell indicating on which sheet the address was found on; different colors for different sheets. I tried to figure out some conditional formatting, but to no avail. Also, on occasion I have spreadsheets with 5 or 6 sheets containing these mixed addresses. Is there a solution that won't be limited to just a few sheets for comparison?
Excel Help.xlsx
View 8 Replies
View Related
Aug 13, 2014
I've got 3 pairs of columns and I need to sort through them and align the cells in columns E&F with those in A&B and C&D. The cells I need to match up are the times (columns A, C and E)
Example - convert this:
A...............................B..........C...............................D.........E...............................F......
BID TIME.....................BID.......ASK TIME....................ASK......TRADE TIME................TRADE
30/07/2014 14:21:04.....6.10.....30/07/2014 14:22:37.....6.13.....30/07/2014 14:21:04.....6.13
30/07/2014 14:21:06.....6.11.....30/07/2014 14:22:54.....6.13.....30/07/2014 14:22:37.....6.13
30/07/2014 14:22:37.....6.11.....30/07/2014 14:22:56.....6.13.....30/07/2014 14:22:54.....6.13
30/07/2014 14:22:54.....6.11.....30/07/2014 14:22:56.....6.14.....30/07/2014 14:22:56.....6.13
30/07/2014 14:22:56.....6.11.....30/07/2014 14:22:59.....6.13.....30/07/2014 14:22:59.....6.13
Into this:
BID TIME.....................BID.......ASK TIME....................ASK......TRADE TIME................TRADE
30/07/2014 14:21:04.....6.10.................................................30/07/2014 14:21:04.....6.13
30/07/2014 14:21:06.....6.11........................................................................................
30/07/2014 14:22:37.....6.11.....30/07/2014 14:22:37.....6.13.....30/07/2014 14:22:37.....6.13
30/07/2014 14:22:54.....6.11.....30/07/2014 14:22:54.....6.13.....30/07/2014 14:22:54.....6.13
30/07/2014 14:22:56.....6.11.....30/07/2014 14:22:56.....6.13.....30/07/2014 14:22:56.....6.13
............................................30/07/2014 14:22:56.....6.14............................................
............................................30/07/2014 14:22:59.....6.13.....30/07/2014 14:22:59.....6.13
I don't know VBA so hopefully there's a way of doing this with a basic Excel function.
View 2 Replies
View Related
Nov 22, 2012
I want to highlight the cell of Column 'A' of excel only when it does not contain the text like "Verify", "Validate" or "Evaluate" in its content if, the value in corresponding cell of Column 'B' holds the value 'Y'. Secondly, the column 'A' can't contains the words like 'Verify', 'Validate' and 'Evaluate' if the corresponding cell in Column 'B' holds value 'N'. So just need to highlight those discrepancies if its there.
Column A
Column B
Expected Action
Press F3
N
Its Fine
Verify this..
Y
Its Fine
this....
Y
Need to Highlight Cell of Column A as Value in Column B is Y but the column A does not contain any value like "Verify", Validate" or "Evaluate"
Verify This.
N
Need to Highlight Cell of Column B as the Value of Column A contains words like "verify", "validate", "evaluate" but corresponding cell value in column B does not hold value "Y".
View 3 Replies
View Related
Mar 2, 2014
I am using the cell validation command to create a drop down list on sheet1. Drop down is created from sheet 3 column A
I would like sheet 4 to be filled by information from both sheets. first line would have item chosen from drop down and the next line would be sheet 3 column B.
It is for a catering company. Sheet 1 (menu) is the creation sheet. I would like to use a drop down list to choose from a long list of appetizers. Those of which are on sheet 3 (app list) and held in column A (1-42). As that occurs, I would like sheet 4 (prep list) to generate a list of operations. A1 (prep list) would reference the item chosen and A2 would be the list of operations (sheet 3(app list) column B)
View 7 Replies
View Related
May 28, 2014
Data file with few columns. There are groups of similar ID numbers in Column J. For a group of similar ID numbers in consecutive rows there is only one row that has a number greater than 0 in its Column L cell and the rest of the cells of Column L for that set of similar IDs is filled with 0s.
First for that unique ID group I need to find out which row is it that has a value greater than zero in its Column L cell.
Then I need to use that value to fill the rest of the 0s in Column L corresponding to that set of Unique IDs.
The process continues with identifying similar IDs in Column J and this time doing the same thing for their Column M. I have attached a sample file that shows the data and how the results need to look like.
See here Fill Cells.xlsx
View 1 Replies
View Related
May 31, 2014
When I enter barcode X in cell A1, I want B1 to say supplier and C1 to say product name and D1 to say package quantaty etc.
And if I enter barcode Y in cell A2, I want B2 to say different supplier, and so on, hopefully you get the picture.
View 7 Replies
View Related
