Simple Macro Placing Value In Cell Based On Values In Other Cells 


Simple Macro Placing Value In Cell Based On Values In Other Cells  Excel 
View Answers 
I have a list of data in four columns (multiple rows), e.g.,
1 2 3 4
I inserted 2 additional columns after the data to categorize the data according to specific customer feedback, e.g.,
5 6
I am trying to create a macro that takes the original 4 columns of data, copies it into a second worksheet, inserts a column between column 1 and 2 (lets call it column 1.2 for now), and fills column 1.2 in based on the data in column 5 and 6. What I mean by the "filling in column 1.2" is that I would like for the macro to see what the value is in the first row in column 5 and column 6, go to a specific area on a third worksheet based on the combined information in column 5 and 6, row 1, see what the value is in that specific location, and put that value in row 1 of column 1.2 in worksheet 2
I would like the process to repeat as long as there is a data value (other than 0) in column 1. Can anyone help?
1 2 3 4
I inserted 2 additional columns after the data to categorize the data according to specific customer feedback, e.g.,
5 6
I am trying to create a macro that takes the original 4 columns of data, copies it into a second worksheet, inserts a column between column 1 and 2 (lets call it column 1.2 for now), and fills column 1.2 in based on the data in column 5 and 6. What I mean by the "filling in column 1.2" is that I would like for the macro to see what the value is in the first row in column 5 and column 6, go to a specific area on a third worksheet based on the combined information in column 5 and 6, row 1, see what the value is in that specific location, and put that value in row 1 of column 1.2 in worksheet 2
I would like the process to repeat as long as there is a data value (other than 0) in column 1. Can anyone help?
Similar Excel Tutorials
Linking Cells in Excel
In this tutorial I am going to cover how to link cells together. This is a useful feature of Excel as you can link ...
In this tutorial I am going to cover how to link cells together. This is a useful feature of Excel as you can link ...
Run a Macro from Another Macro in Excel
I will show you how to run a macro from another macro in Excel. This means that you can run any macro when you ne ...
I will show you how to run a macro from another macro in Excel. This means that you can run any macro when you ne ...
How to Make Macros Run A LOT Faster
Here is a very simple and easytouse tip to make all of your Excel macros run A LOT faster. It is very simple and ...
Here is a very simple and easytouse tip to make all of your Excel macros run A LOT faster. It is very simple and ...
Introducing Logic into Formulas and Functions in Excel
In this tutorial I am going to introduce the idea of Logic in Formulas. A Logic test is a test that evaluates eithe ...
In this tutorial I am going to introduce the idea of Logic in Formulas. A Logic test is a test that evaluates eithe ...
Helpful Excel Macros
Reverse Row or Column Order in a Worksheet
 This macro reverses the order of a selected row or column in excel. It will completely reverse the data.
 This macro reverses the order of a selected row or column in excel. It will completely reverse the data.
Filter Data Sets on Multiple Columns with Multiple Criteria at Once in Excel  AutoFilter
 This Excel macro allows you to filter a data set on multiple columns and criteria at once. This means that you can filt
 This Excel macro allows you to filter a data set on multiple columns and criteria at once. This means that you can filt
Vlookup Macro to Return All Matching Results and Stack them with Previous Results
 This is very similar to the other Vlookup type Macro in that it returns all of the results that match a particular se
 This is very similar to the other Vlookup type Macro in that it returns all of the results that match a particular se
Delete Entire Rows Based on Predefined Criteria (Text)
 This macro will allow you to specify certain criteria and then to delete rows based upon that criteria. You will choose
 This macro will allow you to specify certain criteria and then to delete rows based upon that criteria. You will choose
Sort Data that Doesn't Have Headers in Ascending Order in Excel
 Sort data that doesn't have headers in ascending order in Excel with this macro. This is a simple sort macro that assum
 Sort data that doesn't have headers in ascending order in Excel with this macro. This is a simple sort macro that assum
Similar Topics
Hello,
I am working with two worksheets, I will call A and B. Worksheet A contains stationary column labels with specific formatting. Worksheet B is an Excel table which contains columns of data of which the column label order and number of rows can vary each time the table is updated based on the pivot critera selected by the user.
I would like to write a macro which will find a specified column label, we can call "Area" in worksheet B, move one cell down and copy all the data below the label, then past only the values (not formatting) into Worksheet A, just under a label with the same name "Area". As I mentioned earlier the location of the column in Worksheet A will remain consistent so I will have an exact cell reference to paste to.
Thanks ahead of time for any advice...greatly appreciated!
I am working with two worksheets, I will call A and B. Worksheet A contains stationary column labels with specific formatting. Worksheet B is an Excel table which contains columns of data of which the column label order and number of rows can vary each time the table is updated based on the pivot critera selected by the user.
I would like to write a macro which will find a specified column label, we can call "Area" in worksheet B, move one cell down and copy all the data below the label, then past only the values (not formatting) into Worksheet A, just under a label with the same name "Area". As I mentioned earlier the location of the column in Worksheet A will remain consistent so I will have an exact cell reference to paste to.
Thanks ahead of time for any advice...greatly appreciated!
Hope you can help
Have a spreadsheet with 2 worksheets. In worksheet 1, I have 4 columnsColumns 1 & 3 contain city names and Columns 2 & 4 contain numbers corresponding with the city in in either Column 1 or Column 3 (1 with 2; 3 with 4). All columns have equal amounts of data (all the same cell length). Although the columns remain the same, each week, the location of the data (rows) and the actual data in each column changes.
In worksheet 2, I have a listing of the cities found in Worksheet 1 all in one column. In the column next to the city names, I want to bring over the number data corresponding with the specific city name.
My thought was to use a combined =if and =vlookup formula to arrive at the correct number being connected with the city name in Worksheet 2. However, I am not clear on the formula syntaxwhat I want the formula in Worksheet 2 to indicate is: If the city name is found in Worksheet 1, Column 1, give me the corresponding number from Column 2, otherwise, if the city name is found in Column 3, give me the corresponding number in Column 4.
As I mentioned, the Column 1 & Column 3 data in Worksheet 1 will change weekly (both rows and what cities are found in each), so I am looking for a universal formula.
Thanks for your help
Have a spreadsheet with 2 worksheets. In worksheet 1, I have 4 columnsColumns 1 & 3 contain city names and Columns 2 & 4 contain numbers corresponding with the city in in either Column 1 or Column 3 (1 with 2; 3 with 4). All columns have equal amounts of data (all the same cell length). Although the columns remain the same, each week, the location of the data (rows) and the actual data in each column changes.
In worksheet 2, I have a listing of the cities found in Worksheet 1 all in one column. In the column next to the city names, I want to bring over the number data corresponding with the specific city name.
My thought was to use a combined =if and =vlookup formula to arrive at the correct number being connected with the city name in Worksheet 2. However, I am not clear on the formula syntaxwhat I want the formula in Worksheet 2 to indicate is: If the city name is found in Worksheet 1, Column 1, give me the corresponding number from Column 2, otherwise, if the city name is found in Column 3, give me the corresponding number in Column 4.
As I mentioned, the Column 1 & Column 3 data in Worksheet 1 will change weekly (both rows and what cities are found in each), so I am looking for a universal formula.
Thanks for your help
I realize that similar questions have been asked all the time, but I can't get it sorted and adjusted to work properly.
I have:
a worksheet 'x' with multiple columns (variable) with multiple rows (variable as well) different columns have different row lengths there are no blanks in the columns I need:
a macro that selects all columns with all values copies them (not move!) to a predefined column where A and its values (A1,A2,A3...) are followed by B and the values (B1,B2,B3,B4...), followed by C... right untill the last column with values in it Example:
Worksheet 1:
Column A:
value a1 value a2 value a3 value a4 Column B:
value b1 value b2 Column C:
value c1 value c2 value c3 value c4 value c5 Worksheet with result:
value a1 value a2 value a3 value a4 value b1 value b2 value c1 value c2 value c3 value c4 value c5
I have:
a worksheet 'x' with multiple columns (variable) with multiple rows (variable as well) different columns have different row lengths there are no blanks in the columns I need:
a macro that selects all columns with all values copies them (not move!) to a predefined column where A and its values (A1,A2,A3...) are followed by B and the values (B1,B2,B3,B4...), followed by C... right untill the last column with values in it Example:
Worksheet 1:
Column A:
value a1 value a2 value a3 value a4 Column B:
value b1 value b2 Column C:
value c1 value c2 value c3 value c4 value c5 Worksheet with result:
value a1 value a2 value a3 value a4 value b1 value b2 value c1 value c2 value c3 value c4 value c5
Hi,
I really need someone to assist me with this if possible.
I have two columns of data as follows:
10:57:42 273
10:57:42 263
10:57:42 253
10:57:42 241
10:57:37 273
10:57:37 243
10:57:37 249
10:57:37 261
10:57:37 253
11:04:47 241
11:04:47 253
11:04:47 263
10:54:31 254
10:54:31 240
10:54:31 265
10:54:31 263
10:54:31 258
11:07:38 273
11:07:38 261
11:07:38 241
11:07:38 253
11:12:41 252
11:12:41 242
11:12:41 244
I need to transpose the second column into a row with the time grouped together in the same row so only a single time is shown per row.
Example:
10:57:42 241 253 263 273
10:57:37 243 249 253 261 273
11:04:47 241 253 263
10:54:31 240 254 258 263 265
11:07:38 241 253 261 273
11:12:41 242 244 252
The other issue is that I need these number put into 6 columns where each column houses a specific range:
TIME : Time
Column 1: 240243
Column 2: 244251
Column 3: 252255
Column 4: 256259
Column 5: 260263
Column 6: 264273
Example:
Time
Column 1
Column 2
Column 3
Column 4
Column 5
Column 6
10:57:42
241
253
263
273
10:57:37
243
249
253
261
273
11:04:47
241
253
263
10:54:31
240
254
258
263
265
11:07:38
241
253
261
273
11:12:41
242
244
252
Thanks,
Cameron
I really need someone to assist me with this if possible.
I have two columns of data as follows:
10:57:42 273
10:57:42 263
10:57:42 253
10:57:42 241
10:57:37 273
10:57:37 243
10:57:37 249
10:57:37 261
10:57:37 253
11:04:47 241
11:04:47 253
11:04:47 263
10:54:31 254
10:54:31 240
10:54:31 265
10:54:31 263
10:54:31 258
11:07:38 273
11:07:38 261
11:07:38 241
11:07:38 253
11:12:41 252
11:12:41 242
11:12:41 244
I need to transpose the second column into a row with the time grouped together in the same row so only a single time is shown per row.
Example:
10:57:42 241 253 263 273
10:57:37 243 249 253 261 273
11:04:47 241 253 263
10:54:31 240 254 258 263 265
11:07:38 241 253 261 273
11:12:41 242 244 252
The other issue is that I need these number put into 6 columns where each column houses a specific range:
TIME : Time
Column 1: 240243
Column 2: 244251
Column 3: 252255
Column 4: 256259
Column 5: 260263
Column 6: 264273
Example:
Time
Column 1
Column 2
Column 3
Column 4
Column 5
Column 6
10:57:42
241
253
263
273
10:57:37
243
249
253
261
273
11:04:47
241
253
263
10:54:31
240
254
258
263
265
11:07:38
241
253
261
273
11:12:41
242
244
252
Thanks,
Cameron
Hey guys,
Hope you can help here. Suppose I've got a table:
Column 1 column 2
x.................2
x.................8
x.................0
y.................3
y.................1
y.................4
This is obviously much longer and many more values in column 1. It is an export from a database. I'd like to find a macro that will create a worksheet for each group of items in column 1, copy the rows for each of those values and paste them in the new worksheet named the same as the grouped value in column 1. So worksheet 1 would contain 3 rows (from above) based on the value "x" and that worksheet would be named "x".
Is there a way to do this?
Thanks!!
J.
Hope you can help here. Suppose I've got a table:
Column 1 column 2
x.................2
x.................8
x.................0
y.................3
y.................1
y.................4
This is obviously much longer and many more values in column 1. It is an export from a database. I'd like to find a macro that will create a worksheet for each group of items in column 1, copy the rows for each of those values and paste them in the new worksheet named the same as the grouped value in column 1. So worksheet 1 would contain 3 rows (from above) based on the value "x" and that worksheet would be named "x".
Is there a way to do this?
Thanks!!
J.
I am a novice when it comes to making macros so I am in desperate need of help. I have two worksheets, one with data that will be updated with new data everyday and the other, a template that the data needs to fill in by each row accordingly. However the template needs to be a copy of the unique name from column A and renamed by that name. If the template worksheet already exist it will take the information from that matching name and copy certain information to the template. If there is no worksheet(template) existent then it will create a new worksheet according to that unique value from column "A".
The data in worksheet(data) will first need to be filtered by two types, "Redemption" and "Full Liquidation" from column "I" before it can start transferring data.
From worksheet(data) to worksheet(template),
column "A" will be the new worksheet(template) name,
column "E" will be put in Worksheet(template) column "A" starting with row 4,
column "F" will be in column "B",
column "B" will be in column "C",
column "O" will be in column "D",
column "L" will be in column "T".
So far I have this as a Macro, but it does not copy the template and it takes the whole row instead of the specific columns that I need.
Please Login or Register to view this content.
Please help I'm in desperate need of some guidance
I have 80 columns of raw data with static headers that are produced out of an ERP system, however the report run is not consistent in placing the data in consistent column letters. I am looking for a solution to autocopy the raw data into a formatted worksheet that can identify the data based on the sheet name and column header, similar to Paste Link where a column would populate, but only based on the Column Header name alone.
In a beautiful world it would look something like "='Raw Data'!"Data Column Header".
I'm a total Excel idiot so if you can recommend a macro, please be gentle.
Thanks so much in advance!
Todd
In a beautiful world it would look something like "='Raw Data'!"Data Column Header".
I'm a total Excel idiot so if you can recommend a macro, please be gentle.
Thanks so much in advance!
Todd
Hope you can help
Have a spreadsheet with 2 worksheets. In worksheet 1, I have 4 columnsColumns 1 & 3 contain city names and Columns 2 & 4 contain numbers corresponding with the city in in either Column 1 or Column 3 (1 with 2; 3 with 4). All columns have equal amounts of data (all the same cell length). Although the columns remain the same, each week, the location of the data (rows) and the actual data in each column changes.
In worksheet 2, I have a listing of the cities found in Worksheet 1 all in one column. In the column next to the city names, I want to bring over the number data corresponding with the specific city name.
My thought was to use a combined =if and =vlookup formula to arrive at the correct number being connected with the city name in Worksheet 2. However, I am not clear on the formula syntaxwhat I want the formula in Worksheet 2 to indicate is: If the city name is found in Worksheet 1, Column 1, give me the corresponding number from Column 2, otherwise, if the city name is found in Column 3, give me the corresponding number in Column 4.
As I mentioned, the Column 1 & Column 3 data in Worksheet 1 will change weekly (both rows and what cities are found in each), so I am looking for a universal formula.
Thanks for your help
Have a spreadsheet with 2 worksheets. In worksheet 1, I have 4 columnsColumns 1 & 3 contain city names and Columns 2 & 4 contain numbers corresponding with the city in in either Column 1 or Column 3 (1 with 2; 3 with 4). All columns have equal amounts of data (all the same cell length). Although the columns remain the same, each week, the location of the data (rows) and the actual data in each column changes.
In worksheet 2, I have a listing of the cities found in Worksheet 1 all in one column. In the column next to the city names, I want to bring over the number data corresponding with the specific city name.
My thought was to use a combined =if and =vlookup formula to arrive at the correct number being connected with the city name in Worksheet 2. However, I am not clear on the formula syntaxwhat I want the formula in Worksheet 2 to indicate is: If the city name is found in Worksheet 1, Column 1, give me the corresponding number from Column 2, otherwise, if the city name is found in Column 3, give me the corresponding number in Column 4.
As I mentioned, the Column 1 & Column 3 data in Worksheet 1 will change weekly (both rows and what cities are found in each), so I am looking for a universal formula.
Thanks for your help
I have one column of data that is the date of a specific event. Multiple events may occur on the same date and no events may occur on a given date. Here's a simple example (Column B is arbitrary):
Column A: Column B: Column C: Column D: Column E:
1/16/2010 5
1/16/2010 3
1/16/2010 8
1/17/2010 2
1/20/2010 3
1/29/2010 9
Columns A and B are entered manually based on some occurrence on that particular date. Columns C, D, and E are formulas based on Columns A and B, as well as other data. The catch is that Columns C, D, and E require each day to be present in Column A because they are updated daily, regardless of whether or not there is an occurrence on a day not manually input in Column A.
This is also important because I then plot Columns C, D, and E on the yaxis of a graph, with Column A on the xaxis. I need to have a point for every day, as Columns C, D, and E change even though there is no new value in Column B.
Is there a way, given the above Columns A and B, to automatically create new versions of A and B with rows inserted for every day that is not manually entered? Thanks for the help. Let me know if you need any more information and I'll try to provide it.
Column A: Column B: Column C: Column D: Column E:
1/16/2010 5
1/16/2010 3
1/16/2010 8
1/17/2010 2
1/20/2010 3
1/29/2010 9
Columns A and B are entered manually based on some occurrence on that particular date. Columns C, D, and E are formulas based on Columns A and B, as well as other data. The catch is that Columns C, D, and E require each day to be present in Column A because they are updated daily, regardless of whether or not there is an occurrence on a day not manually input in Column A.
This is also important because I then plot Columns C, D, and E on the yaxis of a graph, with Column A on the xaxis. I need to have a point for every day, as Columns C, D, and E change even though there is no new value in Column B.
Is there a way, given the above Columns A and B, to automatically create new versions of A and B with rows inserted for every day that is not manually entered? Thanks for the help. Let me know if you need any more information and I'll try to provide it.
I have posted this question before with no response....so..let's try again.
I am trying to create a macro and having a problem. I have 100 columns of data. Each column has a header name. How do I create a macro find a specific column(s), and move it to a new worksheet via a macro?
I am looking to select specific columns searching by header name instead of column letter as the identifier. I then want to copy all that column data, including the header name into a new worksheet. I will do this with 50 of the 100 columns and I want to place the columns of my choice into specific order in the new worksheet.
Someone once mentioned using the advanced find feature, but I have had no success. Could you help me with this problem?
Tony
I am trying to create a macro and having a problem. I have 100 columns of data. Each column has a header name. How do I create a macro find a specific column(s), and move it to a new worksheet via a macro?
I am looking to select specific columns searching by header name instead of column letter as the identifier. I then want to copy all that column data, including the header name into a new worksheet. I will do this with 50 of the 100 columns and I want to place the columns of my choice into specific order in the new worksheet.
Someone once mentioned using the advanced find feature, but I have had no success. Could you help me with this problem?
Tony
I have a userform that I created which has a lot of checkboxes on it. Each checkbox corresponds to a column of data in my sheet. As of now, when I check the box, the column of data is made visible. So I can select which columns of data I want to see.
My question is how can I make the reference apply to a specific column of data rather than just the specific column.
Now my code has Columns("C:C").hidden=false.
How can I change this so that I can add columns before column C. As of now, if I insert a column at B then when I check off that box in my userform it hides the wrong column (since the data that was in column C has now been moved to column D).
Thanks for your help
My question is how can I make the reference apply to a specific column of data rather than just the specific column.
Now my code has Columns("C:C").hidden=false.
How can I change this so that I can add columns before column C. As of now, if I insert a column at B then when I check off that box in my userform it hides the wrong column (since the data that was in column C has now been moved to column D).
Thanks for your help
I am trying to create a macro and having a problem. I have 100 columns of data. Each column has a header name. How do I create a macro find a specific column(s), and move it to a new worksheet via a macro?
I am looking to select specific columns searching by header name instead of column letter as the identifier. I then want to copy all that column data, including the header name into a new worksheet. I will do this with 50 of the 100 columns and I want to place the columns of my choice into specific order in the new worksheet.
Someone once mentioned using the advanced find feature, but I have had no success. Could you help me with this problem?
Tony
I am looking to select specific columns searching by header name instead of column letter as the identifier. I then want to copy all that column data, including the header name into a new worksheet. I will do this with 50 of the 100 columns and I want to place the columns of my choice into specific order in the new worksheet.
Someone once mentioned using the advanced find feature, but I have had no success. Could you help me with this problem?
Tony
Hi, this is my first post on this forum.
I am looking for solution to the following problem.
I have row with 31 data columns. Column(A) is row name. Column(B) through Column(AF) are data columns and will be manually populated daily with a numeric value as needed or "0" if no data is required.
I need a vba code, macro or conditional formatting that will autoupdate, on worksheet change, and display the following:
In column (AG) a running count of columns with "0's" , in column (AH) a column count between last two columns with numeric data, then in column (AI) running sum of all columns with numeric data within specific row.
Always the staring column will be column(B) with "0" or with numeric data.
The cells will get populated with a numeric data or "0's" randomly.
Number of data rows may grow from 1 to n.
Any help appreciated greatly.
Thanks,
Tom
I am looking for solution to the following problem.
I have row with 31 data columns. Column(A) is row name. Column(B) through Column(AF) are data columns and will be manually populated daily with a numeric value as needed or "0" if no data is required.
I need a vba code, macro or conditional formatting that will autoupdate, on worksheet change, and display the following:
In column (AG) a running count of columns with "0's" , in column (AH) a column count between last two columns with numeric data, then in column (AI) running sum of all columns with numeric data within specific row.
Always the staring column will be column(B) with "0" or with numeric data.
The cells will get populated with a numeric data or "0's" randomly.
Number of data rows may grow from 1 to n.
Any help appreciated greatly.
Thanks,
Tom
Hi,
Wondering if any one can help. I have been searching online for quite sometime but can't seem to find a solution to what I'm looking for.
Apologies for the long description but I hope it will reduce the amount of Q&A.
I have written a macro to get me to a point with my raw data whereby I have identified the number of instances that it appears using a concatentation of 3 columns and have seperated these from the unique instances.
What I would like to do with the data now, and where I'm having a problem, is to remove duplicates and lower values based on additional columns.
Column A represents columns B, C & D concatenated and by which I have determined the duplicates thus far. What I now need to do is remove duplicates based on column A and on columns E and F and also remove rows whereby column A is duplicated but the value in column E is lower. To add to the complexity, for a particular duplicate in column A, if one of the values in column E is text eg Other or NA, then I need to leave and NOT remove all instances of column A.
To Summarise:
1. Remove Duplicates whereby Columns A, E and F match and column E does not contain text
2. Remove Rows whereby Column A and F match and where column E is not the highest value and that none of the values in E for duplicates of A are text
3. All rows removed to be copied to a worksheet called "Removed"
I have attached an example spreadsheet, however the data I work with will vary in length, usually 40k150k lines, and usually have additional columns although the additional columns would be irrelevant.
In this small example based on the rules above rows 5, 11, 17, 20, 23, 24 & 25 would be moved to the "Removed" worksheet and the others would remain.
Any help would be grealty appreciated because I have been trying to do this for over a week and being fairly new to VBA it is doing my head in.
Thank you in advance
Rich.
PS Using excel 2007
Wondering if any one can help. I have been searching online for quite sometime but can't seem to find a solution to what I'm looking for.
Apologies for the long description but I hope it will reduce the amount of Q&A.
I have written a macro to get me to a point with my raw data whereby I have identified the number of instances that it appears using a concatentation of 3 columns and have seperated these from the unique instances.
What I would like to do with the data now, and where I'm having a problem, is to remove duplicates and lower values based on additional columns.
Column A represents columns B, C & D concatenated and by which I have determined the duplicates thus far. What I now need to do is remove duplicates based on column A and on columns E and F and also remove rows whereby column A is duplicated but the value in column E is lower. To add to the complexity, for a particular duplicate in column A, if one of the values in column E is text eg Other or NA, then I need to leave and NOT remove all instances of column A.
To Summarise:
1. Remove Duplicates whereby Columns A, E and F match and column E does not contain text
2. Remove Rows whereby Column A and F match and where column E is not the highest value and that none of the values in E for duplicates of A are text
3. All rows removed to be copied to a worksheet called "Removed"
I have attached an example spreadsheet, however the data I work with will vary in length, usually 40k150k lines, and usually have additional columns although the additional columns would be irrelevant.
In this small example based on the rules above rows 5, 11, 17, 20, 23, 24 & 25 would be moved to the "Removed" worksheet and the others would remain.
Any help would be grealty appreciated because I have been trying to do this for over a week and being fairly new to VBA it is doing my head in.
Thank you in advance
Rich.
PS Using excel 2007
Hi all,
I need to evaluate values from a list on one worksheet and summarize the answers on to another worksheet using specific logic. I don't write code so I seem to be lost. I can't figure this out based on other examples given because it's not an exact match to mine. here goes...
Working with two worksheets, First worksheet is worksheet 1 and it has two columns, A for "Area" and B for "Status."
Second worksheet has 3 columns, A for Area, B for Task, C for Status. Example data:
Column A
Area
Project Team
Column B
Task
Documentation
UAT
Support
Training Materials
Column C
Status
G
G
Y
R
Values in "Status" of Column c of worksheet 2 are selected from a named list that contain the values G, Y, R and a blank. each has associated conditional formatting so G=Green, Y=Yellow, R=Red and Blank is no fill.
I need the status in column B of worksheet 1 to disaply automatically based on selections made in column C of worksheet 2 based on the following logic:
If one or more cells in the range (C2C5) in column C worksheet 2 equals G or blank, with no R or Y, display G (with green background) in cell 2b on worksheet 1.
if at least one of the cells in range (C2C5) in column C equals Y, with no R, display Y with yellow background) in cell 2b on worksheet 1. (overrides any green or blank)
if at least one of the cells in range (C2C5) in column C equals R, display R with red background in cell 2b on worksheet 1. (ignores green, blank and yellow)
I'd also like to add this condition to the listing if possible:
if all cells in the range are G/green, display Completed with green background in cell 2b on worksheet 1.
I am not an advanced excel user so any help you can give would be greatly appreciated.
I need to evaluate values from a list on one worksheet and summarize the answers on to another worksheet using specific logic. I don't write code so I seem to be lost. I can't figure this out based on other examples given because it's not an exact match to mine. here goes...
Working with two worksheets, First worksheet is worksheet 1 and it has two columns, A for "Area" and B for "Status."
Second worksheet has 3 columns, A for Area, B for Task, C for Status. Example data:
Column A
Area
Project Team
Column B
Task
Documentation
UAT
Support
Training Materials
Column C
Status
G
G
Y
R
Values in "Status" of Column c of worksheet 2 are selected from a named list that contain the values G, Y, R and a blank. each has associated conditional formatting so G=Green, Y=Yellow, R=Red and Blank is no fill.
I need the status in column B of worksheet 1 to disaply automatically based on selections made in column C of worksheet 2 based on the following logic:
If one or more cells in the range (C2C5) in column C worksheet 2 equals G or blank, with no R or Y, display G (with green background) in cell 2b on worksheet 1.
if at least one of the cells in range (C2C5) in column C equals Y, with no R, display Y with yellow background) in cell 2b on worksheet 1. (overrides any green or blank)
if at least one of the cells in range (C2C5) in column C equals R, display R with red background in cell 2b on worksheet 1. (ignores green, blank and yellow)
I'd also like to add this condition to the listing if possible:
if all cells in the range are G/green, display Completed with green background in cell 2b on worksheet 1.
I am not an advanced excel user so any help you can give would be greatly appreciated.
I currently have a macro in the attached file that populates cell A23 in the Data Finder worksheet based on column A in the Employees worksheet and prints them out (the graphs from each valid entry Data Finder sheet). Rather than print them out, I want to place the data that would be found in column AI in the Data Finder sheet and match it up with the corresponding number in column A of the Employees sheet placing the values in columns E, F and so on....The way the file is currently setup (this is the smaller file, copied from larger ones), it calculates the data based on the 'clock number' input from the employee sheet. I'm not sure how to simplify it, but I believe a change to the macro that already functions may be the way to go. I add each month into this file so the macro may need to be tweaked so that i could add onto the data in the employee sheet (additional columns for subsequent months).
Any suggestions?
Thank You
Hi All. i am fairly ignorant in Excel! i have a worksheet with several columns. Column C may have a comma seperated list of values (e.g. 5, 15, 12, etc).
What i need to do is for instances where there are multiple values, create a new row with the value and all other data is the same as the previous row. For instance:
ROW 1
Column A: 123
Column B: 456
Column C: 12, 5
This would create two different rows:
ROW 1
Column A: 123
Column B: 456
Column C: 12
ROW 2
Column A: 123
Column B: 456
Column C: 5
I assume this is possible in a Macro, but my expertise are not great enough. Can someone please help? Thanks in advance!
What i need to do is for instances where there are multiple values, create a new row with the value and all other data is the same as the previous row. For instance:
ROW 1
Column A: 123
Column B: 456
Column C: 12, 5
This would create two different rows:
ROW 1
Column A: 123
Column B: 456
Column C: 12
ROW 2
Column A: 123
Column B: 456
Column C: 5
I assume this is possible in a Macro, but my expertise are not great enough. Can someone please help? Thanks in advance!
Hi,
I'm trying to write a macro that copies the last column of a spreadsheet containing formulas and inserts it into the next column, then copies the original column and hard copies the data. ie the formulas are moving along each week I obtain new data.
The problem I am incurring is that after the 2nd week it selects the original column and inserts there (because that column's id is in the macro) and not the last column thereby copying older incorrect data.
Is there any way I can get it so that it will always select the last column and use that?
Thanks in advance
Andy
I'm trying to write a macro that copies the last column of a spreadsheet containing formulas and inserts it into the next column, then copies the original column and hard copies the data. ie the formulas are moving along each week I obtain new data.
The problem I am incurring is that after the 2nd week it selects the original column and inserts there (because that column's id is in the macro) and not the last column thereby copying older incorrect data.
Is there any way I can get it so that it will always select the last column and use that?
Thanks in advance
Andy
Hello everyone,
I hope I am posting in the correct place, but I am guessing that what I need needs VBA ( i am just starting with VBA btw)
So my problem is this:
I have a column that generates QuotationCodes ( so all cells contain a formula).lets say its column A.
columns B, C have other data like customer, and issue date etc.
Column D is OrderConfirmation and the user fills it in ( the date) only when the quotation is confirmed.
What I need is to copy (in another worksheet) a list of column A (QuoatationCodes) '(and other columns, but if I know how to do it for 1 column I suppose I will be able to do it for the rest as well)
only if it gets a confirmation date in column D. ' (basicaly generate an order list where i generate uniquencodes what i have now is a list of orders with blank rows for the quotations that have not been confirmed)
I need this to refresh automatically/ dynamicaly. either on new data entry ( In column D) or by a control button ...
note that column A will be ever expanding and data entry is on a daily basis.
Thank you in advance,
Angelos
Hi,
I have a set of data in one worksheet which contains 7 columns and multiple
rows of data (over 300 rows). In a new worksheet, I need to summarize one
column of this data which is referenced by matching 2 (or more columns).
Please see below:
Sheet 1 contains the data with 7 columns:
A B C D E F G H
110 03 2A 1777 03 3000 1555 456
111 04 5A 2587 02 3156 1777 12.65
etc etc etc etc for over 300 rows of data
Sheet 2 is where I wish to pull the data from Sheet 1I would like to
return a value from Sheet 1 Column H based on criteria matching Column A,
column C and column F. In this example, I want to find all values in Column
H where Column F=3000, Column C=5A and Column A=110.
Any guidance or suggestions would be greatly appreciated. Thanks.
I have a set of data in one worksheet which contains 7 columns and multiple
rows of data (over 300 rows). In a new worksheet, I need to summarize one
column of this data which is referenced by matching 2 (or more columns).
Please see below:
Sheet 1 contains the data with 7 columns:
A B C D E F G H
110 03 2A 1777 03 3000 1555 456
111 04 5A 2587 02 3156 1777 12.65
etc etc etc etc for over 300 rows of data
Sheet 2 is where I wish to pull the data from Sheet 1I would like to
return a value from Sheet 1 Column H based on criteria matching Column A,
column C and column F. In this example, I want to find all values in Column
H where Column F=3000, Column C=5A and Column A=110.
Any guidance or suggestions would be greatly appreciated. Thanks.
Hello All,
Just joined up today. Lets get to it then! I'm trying to create a macro that will look at values in Column A which contains customer account numbers. The Macro will look at column A and if it matches a value in a list (can this list be included in the macro code or does the list need to exist in a column in the worksheet?), then it will pull all rows which contain that account number over to a new worksheet. When this data is found and copied over to the new worksheet, i would also like the worksheet to be named based on the customer name in cell B2 (assuming header row is B1).
I have attached a brief example of a spreadsheet for reference. How can i tell the macro to look for account 1987 and take all rows containing that data over to a new sheet? The example i have provided is small, and the account numbers i need to look for are many but i was thinking of just creating a list and when i have the macro code i can use this list to put into the macro.
I have looked online and can't quite find the code which does what i want  any help would be massivley appreciated
Can this be done?
Thanks in advance
Aldo
Just joined up today. Lets get to it then! I'm trying to create a macro that will look at values in Column A which contains customer account numbers. The Macro will look at column A and if it matches a value in a list (can this list be included in the macro code or does the list need to exist in a column in the worksheet?), then it will pull all rows which contain that account number over to a new worksheet. When this data is found and copied over to the new worksheet, i would also like the worksheet to be named based on the customer name in cell B2 (assuming header row is B1).
I have attached a brief example of a spreadsheet for reference. How can i tell the macro to look for account 1987 and take all rows containing that data over to a new sheet? The example i have provided is small, and the account numbers i need to look for are many but i was thinking of just creating a list and when i have the macro code i can use this list to put into the macro.
I have looked online and can't quite find the code which does what i want  any help would be massivley appreciated
Can this be done?
Thanks in advance
Aldo
Is it possible to create a macro to check the value of a specific column and depending on the value, then no row need to be added or one row or two rows need to be inserted as well as the new row data will be filled with some same data and calculated data from the above row.
For example, the worksheet may have about 1000 rows with column A contains person ID, B contains OrderCode, C contains OrderDate, D contains TotalHrs, E contains NorHrs, F contains OTHrs, G contains PreHrs. If TotalHrs has value of 11.5 then on the current row the NorHrs will have the value of 9, then a new row will be inserted below which has the same value of the above row from column A to C while the TotalHrs and OTHrs will have value of 1, finally a second row will also be inserted similar as above but TotalHrs and PreHrs will have value of 1.5.
The main idea is if in the checking row, if TotalHrs is less than or equal to 9, no new row need to be inserted, if TotalHrs is greater than 9 but less than 10 then only one row needs to be inserted, and if TotalHrs is greater than 10 then 2 rows need to be inserted.
For example, the worksheet may have about 1000 rows with column A contains person ID, B contains OrderCode, C contains OrderDate, D contains TotalHrs, E contains NorHrs, F contains OTHrs, G contains PreHrs. If TotalHrs has value of 11.5 then on the current row the NorHrs will have the value of 9, then a new row will be inserted below which has the same value of the above row from column A to C while the TotalHrs and OTHrs will have value of 1, finally a second row will also be inserted similar as above but TotalHrs and PreHrs will have value of 1.5.
The main idea is if in the checking row, if TotalHrs is less than or equal to 9, no new row need to be inserted, if TotalHrs is greater than 9 but less than 10 then only one row needs to be inserted, and if TotalHrs is greater than 10 then 2 rows need to be inserted.
I work at a company that needs an excel macro to make transferring data from one workbook to another workbook simple. For example, cells A1:K1 have the specific string that the column has data for. So cell A1 reads "Car" and the data from A2:A200 (setting 200 as the range) is specific to Car. So this company sends us an excel document that is a full sheet of data but their columns may not match  for instance  Our emptybook.xlsm has Car as A1 and the rest of the data in column A2:A200, but their fullbook.xlsm may have Car as the B column with Car data from B2:B200. We are looking for a macro that searches the fullbook.xlsm for a specific column (based on its title in A1:K1) and then copies it to the emptybook.xlsm in its corresponding column
There may also be nothing in a certain column (if the company we get the data from skips a column for some reason)
Emptybook column setup
Car = Column A, LE = Column B, Event = C, Event City = D, Event ST = E, Dest City = F, Dest ST = G, Carrier = H, Hist. ETA = I
Remember the fullbook could be random so its not a simple copy and paste. Please let me know if you have any other questions
Greetings all: This is my project. I have a worksheet with 100 rows of data. Each row has 20 columns with the final column displaying a result. What I would like to do is create a command by which I can display and compare only rows with a defined value in column A (which would be from 040). Example: I would like to show only data where column A is 012, another where column A is 1320, and another where column A is greater than 20. Can anyone please help? I've always had great results using this forum, thanks in advance. Jim
I have a data dump in multiple spreadsheets and want to pull in specific data from specific sheets and columns.
I want to create a formula or way to pull match up a sheet and a column in that sheet.
For example, I want to reference specific data based on a specific sheet and a specific column based on data in cells A1 (sheet reference) and B1(column reference).
Is there a way to do this?
I want to create a formula or way to pull match up a sheet and a column in that sheet.
For example, I want to reference specific data based on a specific sheet and a specific column based on data in cells A1 (sheet reference) and B1(column reference).
Is there a way to do this?