In column A i have values that may repeat throughout Column A, then next to the value in ColB, there is a sequence number 1,2, 3 for each of the same value in ColA. Then the Text in ColC that i need.
What i Need is to be able to search ColA, Then get the correct Sequence number from ColB, and place the contents in ColC into ColD or into a new worksheet if necessary.
I need to combine these 6 spreadsheets into one single spreadsheet. It won't seem to allow me to paste it.
It needs to begin with 2012 and count down.
2011.xls2010.xls2009.xls2008.xls2007.xls
Im combing 2 cells into 1 but using a fraction..
I created 2 examples of what im trying to do..
Example.
A1 = 22
A2 = 11 3/4
=A1&"'-"&TEXT(A2,"# ?/?")&"''"
[b]22'-11 3/4"[b]
but when A2 = 11
i get this..
22'-11___''
on excel it has some space between the 11 and '' but i cant show that here..
i would like the '' to be next to the 11. is there any way to do that?
I have some cells that are summed with total cost.... now i need to sort the totaled cells? How do i sort a cell that has a formula based on multiple other cells?
View 2 Replies View RelatedI am trying to find a way to write a Macro that will auto color multiple cells based on what data is displayed in one cell. The cell I want to reference is a vlookup cell.
Basically this is a part label. And depending what part is selected from the list my vlookup will display its position on a vehicle(i.e.. FR, FL, RR, RL, Etc..). So if vlookup comes back with FR I want the various cells on the label to be orange, etc..
ALSO: if there is a way to embed it so it does this automatically (rather than run the macro each time).
if it is possible to do the following with the help of the formulas alone. I have a list of events which START and END at different times. Some of them start when the other events are still in progress (their end date has not been reached yet). I need a way to combine the results of the events whose times overlap. The results of the overlapping events should be multiplied and stored as a single event.
The file:
[url]
Was looking into this, but basically i have a repetitive task of copying contents from various columns, and pasting them into 1 column.
1 right after the other. I figured there could be a way for a macro to copy contents from multiple columns and paste them into 1 column.
I have including 2 files, 1 from the start of the file, and 1 how it should be so it will have clarify what i am trying to do.
So basically if you open book1 , i have factory install tabs,
I need the columns contents from the first tab the (5k-20k) starting with Column B, then E, then H, and so on( Every 3rd column starting with B ) to be copied and pasted in Column A of the (Factory Install Tab) ( last tab ).
If you open the 2nd file this will be clearly understood. In the second file in the factory install tab you will notice i did the first 2. I highlighted each starting part in yellow just so its easier to see. SO in column A you should have all the contents in the 5k-20k tab ( starting with b every 3rd ), pasted in column A 1 after the other.
The next tab is the 30k tab, i need all of the columns contents copied starting with B ( every 3rd again ), copied in Column C of the Factory Install tab. Basically every tab's contents needs to have its own column in the Factory Install tab.
If anyone could help that would be great, this is a very time consuming process manually, and it would seem like a macro could be made to easily copy contents.
I am trying to create a drop down list that when selected will display data in the 10 rows below and 4 columns, so a total of 40 cells. I would like the data to be based on the selection in the drop down list.
The data looks like this,
RequirementsQtyResourcesQty
Level 1 Sentinel10Level 1 Sentinel105
Level 2 Sentinel10Level 2 Sentinel167
Level 3 Sentinel10Level 3 Sentinel156
Level 1 Banga20Level 1 Banga401
Level 2 Banga20Level 2 Banga307
Level 4 Banga20Level 4 Banga0
i have 27 different combinations of the above list with up to 10 different rows. I have a label for each one, but i cannot get the rows below my drop down list to display the proper values base don the selection in the drop down list.
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.
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
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.
I’m trying to change color in cell B9 according to different conditions in Cell C9 (than on b10 according to c10 and so long until necessary - probably will be around 2000 lines). Since there are more than 3 conditions and in two cases condition depends on the color of the C Column, I can not use conditional formatting. I’ve searched and found similar forums here, but since I’m ignorant in VBA code, I couldn’t manage to make adjustments. So if you can help me with the code, I would really appreciate it!
Condition and Results required would be:
IF column C = “S” than on Column B = color cell light blue with white border
IF column C = “P” than on Column B = color cell Green with white border
IF column C = “A” than on Column B = color cell Yellow with white border
IF column C = “L” than on Column B = color cell Red with white border
IF column C = “C” than on Column B = color cell Dark Blue with white border
IF column C = “ ” than on Column B = color cell white
IF column C = “V” and has white background than on Column B = color cell white
IF column C = “V” and has a Dark Grey background than on Column B = color cell Dark Grey
I don’t know if the last two are feasible.
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.
I have a worksheet entitled 'Data'. In this worksheet there is a table consisting of 4 columns plus relevant data:
TABLE 1:
Project
Benefit Type
Delivered or Enabled
Benefit
PJ1
Financial
Delivered
Saving of $4M over 24 months.
[code]....
I have been trying to create a formula that will enable me to pull data from the 'benefit' column(column D) so that the cell contents populate in a single cell in a table in a different worksheet.
TABLE 2:
Financial - Delivered
Financial - Enabled
Tech - Delivered
Tech - Enabled
Green - Delivered
Green - Enabled
[code]....
So, as an example, I am hoping that a formula can be created which pulls the text from relevant cells in column D when criteria from columns A, B and C are met e.g. Tech benefits that are Delivered in PJ2 would populate cell E3 ('Tech -Enabled') in Table 2 with:
Continued maintenance of hardware.
Increased capacity.
On the "CurrentCustomer" worksheet tab I am trying to accomplish the following:
When the merged cells in the F column (F3, F25, F47...F443) equal the word "Contract", I need the corresponding total cells in the E column (E23, E45, E67...E463) to add up, if and only if the corresponding F column merged cell equals "Contract".
For example, using the first three ranges, if F3, F25, & F47 all equal "Contract", I need the corresponding E23, E45, & E67 to add up. If F3 & F47 equal "Contract", but F25 equals anything other than contract I need E23 & E67 to add up only.
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.
I want the 'Model Number' to automatically be picked based on the numbers entered into Sheet1 B1 & B2. The model numbers are listed on Sheet2.
View 4 Replies View RelatedHow multiple cells can be formatted based on one cell value. See the screenshot attached to understand what I am trying to achieve. I am very new to excel formatting, vba etc.
ExcelFormattinQs.jpg‎
I'm trying to remove cells that contain certain suffixs. Here is the code I'm trying to use:
Code:
For i = 7 To 2000
If Cells(i, 7) = "IV," Or Cells(i, 7) = "Jr," Or Cells(i, 7) = "III,"
Then Cells(i, 7).DELETE Shift:=xlToLeftEnd IfNext i
but it does absolutely nothing. I've used this for similar macros in the past and it worked just fine.
in the lookup function listed below, it only produces a result for the first exact macth. well, what if the data i am searching on could result in multiple results?
i would like advise on how to modify this formula to bring back the 2nd, 3rd, 4th, etc instance of a match in the lookup array.
the original formula , i found on this site, allows you to combine multiple cells to create a unique reference, and then search in another table containing columns of those combined cells, and results in the intersection of a row and column and that is the data you want returned ....
kind of like a hlookup, but you can use one or more cells to create the unique lookup, and bring back whatever column you want (where as a hlookup is limited to searching the left most column and bring back a column to the left)
here is the formula:
{=INDEX(data to retrieve,MATCH(cell1&cell2,table to find cell1 in&table to find cell2 in,0))}
"data to retrieve" is an array of one column containing the data you want to retrieve
"cell1&cell2" is a combination of cells to make the unique lookup (this could just be one cell if you wanted)
"table to find cell1 in&table to find cell2 in" is a table array to look in to find the look up value
I need to have multiple ranges count based on several criteria. The hitch is that needs to count if one or more criteria is met not all. I tried using a COUNTIFS however that seems to evaluate based on AND not OR.
Is there a nested COUNTIFS or similar function that would work?
I am displaying cells in textboxes depending on the results of a listbox selection. I was wondering if there is code for making the textbox names variable (so I could just loop through the offsets and textbox names)
the code below works but I would like to make it more versatile and shorten it.
There are other textboxes on the page.
Sub detailfill() ....
I have a worksheet that I want to populate with specific data from another worksheet.
If all of the following are met:
1) Column A equals "763" and
2) Column K is formatted Red (255, 0, 0) from conditional formatting
3) Column P is blank
Then in my blank worksheet I need to display the data from Columns A, B, D, K, O
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.
I am having a hard time searching for this formula.
Sheet1 has the following data in A1:A5
value1
value2
value3
value4
value5
I then have 5 additional sheets.
I want to populate cell D2 on each additional sheet with the values from Sheet1
Sheet2 - Value1 in "D2"
Sheet3 - Value2 in "d2"
and so on...
I'm trying to place the value of "X" in multiple cells based on a cell variable (i). For example, if (i) is evaluates to 35, the following code ...
View 8 Replies View RelatedI have just started to learn how to use vba in microsoft excel. Over the past few days i have been creating a drawing register and have incorporated multiple routines based on searching this forum. Up until this point i have foud everything i needed on this forum, but now i cannot work out how to creat a routine that does the below.
The setup
*I have a folder called Zircon Plant
*That folder contains 3 folders 01. Superseded, 02. PDFs and 03. Documentation (the 01. Sup...etc. is the actual folder name, the 01, 02 and 03 isnt distinguishing the 3 folders)
*The Register is located in 03. Documentation
*The PDFs i want to hyperlink to are located in 02. PDFs
What i want to do
*i have 2000 rows of drawing names
*the drawing name in excel is spread over 5 side by side cells on each row (rows 21 - 2020) so for example the first row the information is in cells (H21, I21, J21, K21 ,L21)
*the cells i want to contain hyperlinks are V21 - V2020
so what i want is when excel opens up (i already have another script running at startup so i will be placing this after the other startup routine i have) i want it to search in folder 02. PDFs using the contents in (for example the first row (21) and colums HIJKL contain (H21)4CP(I21)-(J21)D(K21)-(L21)55000) each row and combine those cell contents to find the file name 4CP-D-55000.pdf. I then want it to go down every row until row 2020 untill it has added all pdfs.
(The second row is (H22)4CP(I221)-(J221)D(K221)-(L21)55001.....file name would be 4CP-D-55001.....third row would be 4CP-D-55002 etc...)
If such a pdf with that name doesnt exist, i want the cell (for example v21) to read "PDF not available"
based on a search here i have been able to do this for 1 cell, but dont want to do 2000 seperate routines. the current routine i have for it to work on one cell is
' Select PDF range
ActiveSheet.Range("u21").Select
' Promt PDF Hyperlinking
ActiveCell.Hyperlinks.Add ActiveCell, "I:DraftingAs Built4CP - PinkenbaE - ElectricallZircon Plant02. PDFs4CP-D-55000.pdf"
I also have multiple sheets. the first sheet is D - Documentation. I will eventually copy this routine 4 more times as i will be able to work out how to apply it to the other sheets
I need to return the specific text of a cell based upon the text in multiple cells.
Worksheet as follows:
Asphalt Sub:
BLDG1-Backwash 1
*BLDG1*
*Sub*
BLDG1-Backwash 1 OverEx
*BLDG2*
*Sub*
[Code].....
I have three columns thus far. I need to add another column and search all items in Column A using the criteria in Columns B and C. For instance I need to search for BLDG1-Backwash Sub: and place it into column D using the criteria in Columns B and C.
If possible, I would also like to remove both the words, BLDG1 and Sub from the result so then it only shows "Backwash".
My spreadsheet is much larger than this and I have muliple buildings I am dealing with, sometimes 30-40.
I have a large worksheet that lists employees and the hours they work each day in a pay period.
For Example:
Column A holds a date and Column E holds the number of hours worked for a paytype. So i want to create some function that sums the numbers in column E, but only those where column A holds a date.
Then i want to return that number to a cell, say C20. I think I've managed to tell the macro which rows i want to return values from, but I can't figure out how to say "add these numbers together and put the total in C20."
I'll check this frequently if clarification is needed.
I have multiple workbooks in a particular folder. Each workbook has only one worksheet named "Data". I need a VBA script that could open each file in the folder, copy only particular pre-defined cells that have been mentioned in the script, and then place it one below the other on a new sheet.
Ex, if the pre-defined cell is A2, C6, and D7. I want the code to open all the sheets in the folder one by one, copy A2, C6, and D7 of each workbook opened, and paste it one below the other on the new workbook that is running the macro.
As an add-on, it would also be great if the next column cud carry the file name of from which workbook the information was picked out.
The result thus in the new workbook wud be like...
A2's data | C6's data | D7's data | File Name
Andrew Male Married march3.xls
Peter Male Un-Married march4.xls
Susan Female Divorced april2.xls