Adding Multiple Row Contents Based On The Content Of A Single Cell
May 30, 2007
Been trying to work this one out and have ended up banging my head against the wall.
Basically I have a spreadsheet, in the first column is the person's name, in the secon which team they are in and following this a cell for each day of the month with their availability.
The team name is a selection from a list, the availability is 1-6 (different levels of availability)
I'm trying to do subtotals at the bottom for each separate team, so the very last row each of the cells for each is added up:
Name Team 1st 2nd 3rd 4th 5th 6th 7th etc.
Mr X ___1st___1______2__1
Mr Y ___2nd_______2________5__5__5
Mr Z ___1st___1____________3__3__3
Total 1st: 2 2 1 3 3 3
Total 2nd: 2 5 5 5
So basically the formula in the last row goes along the lines of:
Add each cell in the column IF the team name = 1st
View 9 Replies
ADVERTISEMENT
Feb 28, 2014
I would like to automate the conversion of multiple versions of company names contained in cells to a single company name. i.e. XYZ Inc., XYZ Co., XYZ to all cells XYZ. My data file contains numerous versions of company names. I want them to all be the same company name so sorting data is easier. I now do this manually and it is getting tedious and time consuming. Can I build a table with the different versions that then chamges the cells to a single version that I choose?
View 1 Replies
View Related
Apr 28, 2014
I have values in a number of cells. Next to them, if there was a value, regardless of what the value is, except blank or zero, i would like the cell to turn blue and if possible have a 1 in the cell.
I've used the conditional formatting, however, among the options, 'less than' is the closest to to getting the color right. The issue is that if the cell has nothing in it, it still colors the corresponding cell blue; because it is not less than the already blank cell. If it is blank, I don't want it to color the cell.
View 1 Replies
View Related
Apr 24, 2012
I'm trying to make a macro in Excel 2003 to create x amount of named worksheets that are a copy of a different worksheet.
1. I have a main worksheet that will have a number manually entered into a cell (lets say A1);
2. I have a 'template' worksheet;
3. I'll assign the macro to a button on the main worksheet
If I enter '10' into cell A1 of the main worksheet, I'd like to click the button and have Excel create 10 copies of the template worksheet. These new worksheets should all share the same name with a number after them (ex: banana 1, banana 2..... banana 10).
View 2 Replies
View Related
May 25, 2014
I have a column with various values in, string and numeric. The strings I want to keep have "," in and letters one side and "space" on the other. All the cells I want cleared do not have ","
The rest of what I'm using works fine accept this line which forces an error.
[Code]....
View 5 Replies
View Related
Mar 25, 2014
multiple search match and replace content in a different column so for example
new workbook (look up table)
sku
search1
search2
[Code]....
so something like where you compare two tables and find and replace based on another cell that matches in my sku.. more details would be if the table column aren't exactly matching but the column header and the row header would match and fill or replace in the correct/corresponding cell is there a macro or vba to do this job in excel?
View 6 Replies
View Related
Jan 15, 2010
I have made a dynamic calculator which will calculate the total sale based on 3 criteria in 3 different cells as follows:-
I would like to derive the sales qty in cell C5 if all the 3 conditions in the range B2:B4 is true.
In this case the sales qty for Style-Colour-MRP combination(9103-PINK-399) should be 6.
I am unable to figure out with the formula for the same....
View 9 Replies
View Related
Feb 23, 2012
I 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
View 5 Replies
View Related
Apr 18, 2014
Formula/macro/etc that would enable me to have content of a cell changed based on the content of another cell in the same row.
Example: cell in column D says "PSA" - so I would need the cell in column H for that same row to read "Radio"
I would need an entire sheet scanned to review for these occurrences and make the appropriate changes. I also would need the formula to be inclusive enough to scan for variations in column D cell content (PSA 1, PSA 2, etc).
View 4 Replies
View Related
Aug 14, 2007
I am trying to write an "IF" scenerio.
I want to be able to control what is put in a cell based what is placed in another cell.
For example.
I want to be able to only enter a credit/debit in B1 based by what I type in A1.
If any cell in column A is any of these numbers (41,47,48,49,50,51,52,60,61,65,68,63,67,69) then any number entered in a cell in column B can only be a positive number. If any cell in column A is any of these numbers (40,42,43,44,45,46,52,53,55,56,57,64,62,66) then any number entered in a cell in column B must be a negative number.
So if A1 is 41 then B1 must be positive (debit).
If A4 is 42 then B4 must be negative (credit).
View 3 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
Aug 16, 2013
I have an excel document with multiple excel sheets(sheet1, sheet2...etc), now every sheet contains a cell "total".
Now I want to copy the row containing "total" from all the sheets into another sheet called "report".
View 9 Replies
View Related
Dec 31, 2008
I have an inventory sheet where I occassionally have to create more rows with the same information. So I will create rows and then copy and paste info into them. What I would like to do is be able to drag the information by clicking on the lower left corner and populate the cells that way. The problem is that if there is a number in the cell then it will automatically add to it. For instance, if I try to drag the contents of cell labeled klaraid 1112 then it will add incrementally to this in the cells below.....1113, 1114 and so on. If there are no numbers in the thing i'm dragging then it works fine. How do I drag cells with numbers without it adding to the contents incrementally?
View 3 Replies
View Related
Feb 15, 2009
I would like to Insert the contents of a Range of Cells into a single Comment. ie:
The contents of Range A1:A50 into a Comment placed at F1. Is there a Macro that will do this?
View 3 Replies
View Related
May 31, 2013
I'm using Excel 2007. I would prefer to stay away from the scripting side of the house if possible. This is basically a 3 day forcast weather chart. The top is the actual weather data, the bottom portion is a color coded reflection of how the weather affects various things.
This product is created in excel, but will be embedded into a powerpoint. It will be updated daily. Here is what I would like. I want the color chart at the bottom to update automatically based on the data I enter above. I have a grasp that I can update the color through conditional formatting, although im not exactly sure what that will look like with all of those cells.
I also figured out that I can insert the letters in those lower cells with something similar to " =IF(C6>90, "T", "") " which would put in a 'T' for Temperature when the temperature got above a certain degree.
I run into a problem when I have multiple factors affecting a single cell. For instance on the example in day 2 of my image. Personnel are affected by Temperate AND UV Index. How would I set up that cell to pull that information from both of those cells and display it accordingly? I would prefer the letters to stay separated by the comma, but I could live without that. The default cell color will be green, with the potential to be yellow or red. I left a few examples of possible situations on day 2 and 3.
View 3 Replies
View Related
May 28, 2006
I want to create a formula that will refer to the cell I want by adding a number to the cell reference, not to the contents of the cell. For example, lets say that the cell C11 holds a value of 22. I want to add 4 to C11 to get my formula to reference cell C15, I don't want it to add 4 to the contents of C11 which is 22 and would give me a result of 26. I have a large spreadsheet and I can't just fill down the way I have it now and I don't want to have to retype the formula every time.
View 2 Replies
View Related
Mar 31, 2009
What is the function to add numbers in a single cell to show only a single number. I know it can be done but do not know the quick function to do it.
View 10 Replies
View Related
Jul 29, 2008
I am triying to write a formula adding two digit in the same cell and return the sum as a single digit as shown below. but not sure how to go about doing it. would appreciate any kind souls to help out.
C5 D5 E5 F5H5I5J5K529121958 D8 F8 H8 J8 2 3 1 4Let's say if 2+9 = 11, than I wan to break 11 into 1+1 = 2, how can I get it done?examplecell D8 is the sum of cell C5 and D5 which is 2+9 = 11. than 11 is further break down into 1+1 = 2cell F8 is the sum of cell E5 and F5 which is 1+2 = 3.Likewise for cell H8 is the sum of cell H5 and I5 which is 1+9 = 10. than 10 is further break down into 1+ 0 =1cell J8 is the sum of cell J5 and K5 which is 5+8 = 13, than 13 is futher break down into 1+ 3 = 4.
View 9 Replies
View Related
Mar 13, 2014
how to copy the contents of multiple comment boxes and paste in a single comment box.
The big picture is that I have a number of cells with numerical values in and text in comment boxes. I want to be able to click a button to copy the contents of the comment boxes and paste them, along with the numerical value from the cell, into a single comment box, ordered by highest to lowest value within the comment box, then delete the original cells and comments.
I am quite new to VBA but have been coping quite well so far with information of the web and analysing recorded macros.
View 6 Replies
View Related
Mar 12, 2014
I thought this was a pretty simple formula but I am having difficulty creating it. I am attaching a little test spreadsheet. Sheet 1 is where the data will be entered. The Reimbursed column has a drop down choice of yes or no. The next 2 columns are the cost of registration and the cost of accommodations. On sheet 2 is where I would like the formulas. So in cell A4 I would like a formula that says if B3 on sheet 1 is Yes populate this cell with the contents of Cell C3 only, B4 of sheet 2 would then be B3 if A3 on sheet 3 is Yes and so on with the Not reinbursed if sheet 1 the Reimbursed column is no.
View 3 Replies
View Related
Mar 21, 2008
I have a text file containing internet explorer browser history. The file has data in the following format (in Excel all data is in 1 column): ...
View 9 Replies
View Related
Mar 18, 2009
I have a named range (D:16 F:800) called “NameRange” which contains names.
I’d like to create a macro which offsets a letter “x”, 10 columns to the right everywhere that name appears within the range. As an example: If I’m trying to find the name Tom, A dropdown menu shows the name “Tom” ( which I’ve created) whereby it calls upon a script to search within the named range from this:
Bob Kim Tom
Tom Sue
Mary Bill Ted
Tom Sue Bob
Fred Bill Sue
Bob Tom Kim
and offset “x” to look like this:.......
View 4 Replies
View Related
May 8, 2014
I'm trying to obtain a result based on the number of characters in a cell. If the cell content is blank or contains 9 digits then leave blank.
Any other result must provide the outcome of "incorrect number supplied".
I've tried the following formula but it doesn't work.
View 3 Replies
View Related
Jul 9, 2014
I am trying to search the cells in Column A around rows 54 to 77. And if the cell says "Yes" it should hide the row. I think I'm on the right track, but can't seem to get it work.
[Code] .....
View 6 Replies
View Related
Mar 18, 2012
I am working on a spread sheet for work were we have to test certain items on certain days, e.g. one item needs to be tested every Tuesday & Thursday, when the test is completed you click on a drop down box and click on completed or in progress.
What I would like are a couple of things to happen as follows:
A message box would pop up when you open the file to inform the employee that the test is due today only on Tuesday & Thursday if not completed or in progress from the drop down box. Once you select completed or in progress from the drop down box the message box no longer appears. Every Tuesday & Thursday when it's first open it deletes the cell content, thus triggering the message box test is due today, and once completed or in progress it no longer appears until next Tuesday or Thursday.
The spread sheet is on shared drive and used by several users, let's just say cell A1 is the cell I have the drop down box
View 1 Replies
View Related
Aug 7, 2006
I have a drop down list made from the Forms toolbar with a list of items: 1, 2, 3, 4, 5. I have set it with a linked cell, so when you choose, say 3, from the dropdown, 3 appears in B10.
I was hoping to add a 'Go' button, which would run another macro depending on what is in B10, i.e. so if 3 was chosen, then you press 'Go', Macro3 would be run.
So I have 5 macros called Macro1 - Macro5, and I need some code for the 'Go' button which will run the right macro depending on what number is in cell B10.
View 9 Replies
View Related
Apr 12, 2014
How to determine the correct formula for this requirement?
Assuming I have 2 individual excel files and an index excel file (in reality, there are more than hundreds of file). For index excel, once the user enter part number (eg. 1 or 2), the excel will look for the part number excel file and determine vlookup function.
The only problem I have here is I can not make the index file automatically add the part number shown on most left column into the required formula (replace the part number file section).
I tried use the indirect function but this require each file to be opened, which is not possible for actual use. I am looking for a function that can work in closed worksheet.
Attached files :
index.xlsx‎
1.xlsx‎
2.xlsx‎
View 2 Replies
View Related
Jan 7, 2009
If I have, in one cell (call it D1):
EH,DR,HU
and in a lookup table on another sheet:
A B
1 ED T
2 EH F
3 DR G
4 HU H
5 SE E
6 YU E
I need to be able to lookup the values in D1 on the table and return the values in column B to a single cell (say E1), also comma separated...
eg...
F,G,H
View 9 Replies
View Related
May 3, 2007
I have an excel file having different columns. The test data is as under
Email HQ City Product Value
S@y.com XYZ BAC abc 10
S@y.com XYZ CAD bcd 20
S@y.com XYZ BAC abc 10
S@y.com XYZ CAD bcd 20
A@b.com XYZ BAC abc 10
A@b.com XYZ CAD bcd 20
A@b.com XYZ BAC abc 10
A@b.com XYZ CAD bcd 20
The above is sample data and the rows will change depending on data.
Based on email id the range should be picked up and the email should be send as a attchment.
View 9 Replies
View Related
Jul 1, 2014
I am working on developing QR Codes using some MATLAB code and it would be really convenient if I could create an excel program which changed the background color of a cell containing a 1 to black and a cell containing a 0 to white.
View 2 Replies
View Related
