Highlighting A Row And Having That Data Tranfered To Another Worksheet
I have 1 Excel Doc that has 3 worksheets (DATA, COVER, TPA). DATA is where i input the data and COVER and TPA are where the data needs to end up. both COVER and TPA are standardized sheets that i do not change...only the data coming in will change.
How can i make it so that whatever row in DATA i highlight, that row's data will be entered in to the correct spots on both COVER and TPA. when i highlight a different row, then the data will change to that row's data in both COVER and TPA (hope i'm explaining this correctly).
right now i can only do 1 row by using (=DATA!$D$2). basically i want the '2' that is representing the row to be whatever row i have highlighted. if i highlight row 51 i want the formula to read (=DATA!$D$51).
can anyone please help. i can put up a copy of the spreadsheet if need be, i am just hesitant cause of the confidential info. i would need to change the names and numbers to protect the innocent.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Select An Item Of Data ( Or Update A Particular Cell ) Then I Loose The Highlighting For That Row
I have a big spreadsheet that has 30 columns. The data is downloaded from an SQL database from time to time. The row identifying data ( the record number/key ) is in the left most cell. I often have to move over 10 to 20 columns to view some other data in the same row. I can do this by selecting the preset Excel row number which higlights the whole row and then use the lower slider bar to go to the correct column to view the data. However if I wish to select an item of data ( or update a particular cell ) then I loose the highlighting for that row and I can "loose" which row I am on. ( maybe I'm stupid but it does happen ) Can I use Conditional formatting to highlight a row whenever I alight on any data in that row and it sttays highlighted until I select another value on another row?
View Replies!
View Related
Highlighting Active Cell's Row, Along With Any Row That Shares Same Value In That Column
Is it possible to click on a cell in column C, and have the wishlist below happen: That active cell's row is hightlighted. Any cell in that column that has the same value as active cell is also highlighted. Plus, any cell in another sheet that has that value it's row is highlighted too. Example: I click on C5 in Sheet 2 its value is 45000789 it row is highlighted, this value also appears in C3 in the same sheet, so it's row is highlighted as well. Plus, in sheet 1 in C10 this value appears and it's row is highlighted as well. When any of the values are clicked again the highlight is removed from all parties.
View Replies!
View Related
Highlighting Entire Row
This problem has been driving me insane, as I thought it wasn't possible to do in excel however this morning I accidentally pressed a shortcut key on my keyboard and what I wanted, happened.. pity I can't remember how I did it. How do I highlight an entire row, but remains highlighted when i enter info in the cells until i move down to the next cell? Eg. I click on row"1" so that all cells are highlighted, then while entering info in B:1 - the entire row is still highlighted? This will be very useful in my line of work. I know there was some information on conditioning formatting, but surely there is a way to do it via keyboard shortcut since i accidentally did it just before.
View Replies!
View Related
Highlighting The Row With The Selected Cell
I work with many sheets that have many data columns and have used a "greenbar" formula in conditional formating but would like to have a similar formula that highlights the current row. If possible, it would be nice if this new formula would not wipe out all other formatting as it moves. I have seen Ivan's work at www.mrexcel.com/hof001.php but it says that it wipes out other formatting. The formula that I am currently using is =MOD(ROW(),3)=2 Is there a way of getting the current row number so that it could be =ROW()=(current row number) ?
View Replies!
View Related
Highlighting Data
I have a spreadsheet that I need to highlight the last row next to the name in column E where the date in column C is 35 days or less than the current date in cell A2. For eg if there are 10 names containing the name "James" in Column E and the last date in column C (say C20) in line with James is 35 days or less than the current date in A2, then the entire row from A to G for eg A20:F20 must be highlighted in grey. I need VBA code that will do this for me. I have attached in sample spreadshhet. In the attached example A14:G14 and A16:G16 must be highlighted in grey
View Replies!
View Related
Copy Between Worksheets Doesn't Work (look For A Certain Value In Worksheet A And Copy That Row Of Data To Worksheet B)
look for a certain value in worksheet A and copy that row of data to Worksheet B. However, it seems to be only copying the row in worksheet A and pasting it. Is there something that a noob VBA scripter has missed out? PHP Private Sub GetInfo_Click() Dim r As Long, LastRow As Long, Status As Integer Dim Message As String, Title As String, Default As String, MyValue As String Application.ScreenUpdating = False MyValue = Range("A4").Value Workbooks("invoice.xls").Worksheets("A").Activate LastRow = Range("C65536").End(xlUp).Row For r = LastRow To 1 Step -1 If Cells(r, 1).Value = MyValue Then Rows(r).EntireRow.Copy Workbooks("invoice.xls").Worksheets("B").Activate Rows("8").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Status = 1 Workbooks("invoice.xls").Worksheets("A").Activate Rows(r).EntireRow.Delete Exit For End If Next r Application.ScreenUpdating = True
View Replies!
View Related
UserForm Data To Go To Next Row In Worksheet
In the code below, all I'm trying to do is get the next record from the userform to go to the next row in the worksheet and so on. I looked through some of the threads and even tried (as underlined) but I just can't seem to get it to work. Private Sub TEST_Click() Dim lRow As Long Dim ws As Worksheet Set ws = Worksheets("Sheet1") 'Prompt user before adding record MsgBox "Are you sure you want to Add record?", vbOKOnly, Verify 'Add data to worksheet ActiveCell. Offset(0, 0) = txtFruit.Value ActiveCell.Offset(0, 1) = txtFruit_Number.Value ActiveCell.Offset(0, 2) = txtFruit_Color.Value 'Clear userform txtFruit.Value = vbNullString txtFruit_Number.Value = vbNullString txtFruit_Color.Value = vbNullString txtFruit.SetFocus ' Find emtpy row lRow = ws.Cells(Rows.Count, 2) _ .End(xlUp).Offset(1, 0).Row End Sub
View Replies!
View Related
Insert Row And Paste Data From Another Worksheet
The 'Add new material' button at the SMX sheet paste a range of formatted cells and formulas. The user is suppose to type in the description into the yellow filled cells. The problem is once the user enter sthe description in the yellow filled cells, i need the data to be copied into the FastCheck Sheet. Before that, a new row must be generated to copy for the data to be pasted.
View Replies!
View Related
Copying Row To Other Worksheet If Cell Has Specific Data
I have an Excel 2003 spreadsheet with multiple sheets - one main one with all data and others which have only the data that falls into certain criteria. I'm wondering if there's a way to enter the information on the main sheet and have it automatically populate the correct "sub-worksheet" depending on what is entered in a specific column. I'm not describing this well but I've attached a sample of what I mean.
View Replies!
View Related
Copy Row Data To Another Worksheet Based On Criteria
Data exists in a workbook's sheet name "0293" in columns A, B, C, & D. If a row's value in column B exceeds zero, the entire row & formatting needs to be added to the bottom of data in a sheet called "Comprehensive" of the same workbook and put 0293 in column E of the same row. What would be the simplest VBA code to copy sheet 0293's data & add it to the "Comprehensive" sheet with the sheet name in column E of the same row?
View Replies!
View Related
Cut & Paste (Move) Filtered Data To 1st Empty Row In Another Worksheet
I am trying to find a macro that will filter on Column F (non-blanks - just cells with dates) cut just the filtered data (A:G inclusive)without the Header Rows and then paste/move this data to the first empty row on another sheet called "Complete" So that I get the cells in columns A:G with dates in moved to the "Complete" tab and they are no longer in the sheet "Deliverables"
View Replies!
View Related
Highlighting A Range Of Cells Dependant On Data In Any Of Those Cells
On the attached sheet I am trying to indicate that enough data has been entered by highlighting the entire column. Any 3 cells in Rows 7-13 and 18-36 will be filled in with any of the values from cells O6-O11 (hidden). When the total = 6 i would like to highlight the entire column to indicate it has been completed. The aim is two fold: to ensure that the correct number of points is allocated in each race, and secondly to indicate which column is the next to be filled in (as human error sometimes misses the column and adds the points to either the previous or next columns). You could call it idiot proofing the sheet. At present i have the cells in Row 37 conditional formatted to show this, but would much prefer the entire column to highlight.
View Replies!
View Related
Auto Copy Part Of Row In A Worksheet To Another Worksheet Based On Criteria
I'm a novice Excel 2007 user and appreciate all the help I can get. I have a workbook with monthly worksheets in it. When a certain data Type is selected from a drop down menu in that monthly worksheet than I would like to have it automatically enter specific data (Name, Date, Eval, Type) copied to another worksheet (CC) in the same workbook. I have been manually entering the data so far. Another thing, some of the data will be entered into the Monthly worksheets and some will only be manually entered into the CC worksheet so it would need to accomodate both methods of data entry. Please let me know if I need to clarify. I have attached the workbood, too.
View Replies!
View Related
Macro - Delete Row From Worksheet 1 And Add To Worksheet 2
I am maintaining an issues list in excel. File contains two worksheets/tabs: 1) Open Issues & 2)Closed Issues One of the columns is Status. When an issues is in 'Complete' status, it is removed from Open Issues and added onto the 'Closed Issues' worksheet. There are many issues closed daily and its quite a manual task to individually delete rows in one sheet and paste them at the end of the last row in the other sheet. Want to have a button/macro that would find any rows that have the cell for the status column w/ value 'Complete', delete it from 'Open Issues' sheet, and append it to the 'Closed Issues' shseet. ive done basic c++ programing years ago, but know nothing about vba or excel macros.
View Replies!
View Related
Trying To Cut Row From One Worksheet And Paste In Another Worksheet
I am trying to cut a row from one worksheet if a value in a cell is a negative number and paste the entire row to another worksheet in the same work book. I would like for the code to evaluate each cell in a range and if it is a negative number cut and paste that entire row into the worksheet called credits. I am very new to VBA. Below is the code I am trying to use:
View Replies!
View Related
Insert Row In Worksheet One And Do The Same In Worksheet 2
I've attached a spreadsheet with 2 worksheets, "Summary" and "Detailed". I would like to create a macro in "Summary" that enables me to insert a row at any position (e.g. insert row to Project 1 - 'above row 12'). While inserting the row, I would like to copy the formulas represented in cells "G:I" (e.g. G8:G11). Cell H11 and I11 both have vlookup formulas that lookup in "Detailed". However, upon inertion of row in "Summary", need to insert 25 rows in "Summary", while copying the cells G6:G31 within respective area of Summary. My purpose is to set this spreadsheet up, so if anyone wants to insert a task or project to the summary area, they can do so with the click of a button and does not compromise any of the formulas. Also note, I have code within the sheet that colours the cels within "Summary" "G:I" based on selections. I would like to ensure this is updated as well based on insertion of rows.
View Replies!
View Related
Copying Certain Row With In A Worksheet To A New Worksheet
I have a workbook with several worksheets in it. on one of the worksheets named WSP Mthly I need to copy certain rows (Monthly totals) Jan, Feb, March etc. Rows 37,69,98,130,161,193,224,256,288,319,351,382,414,415. The sheet that iIneed to copy has been been Subtotaled. I need to copy and paste the information with the totals on a another work sheet starting at cell A6. This is to be attached to a button. I have no ideal were to begin in writting a macro for this. Can anyone help and thank you very much any information would be appreciated. Also is there a away to clear the information that is on the totals sheet before I copy and paste from the WSP Mthly sheet, but not the headers because I have another button set up that copies a whole sheet from a different worksheet to the totals sheet also. What Im tring to do is when you click this button you get this information and when you click another button you get different information but one sheet may have more rows then the other.
View Replies!
View Related
INDEX(MATCH Using Value From One Worksheet To Locate Data In Another Worksheet
Here is the situation: On Sheet1: A 123 456 789 On Sheet2: D, G aaa123, 11 bbb456, 22 ccc789, 33 I would like the function to search strings within an array on Sheet2 for a value on Sheet1 (that is, to search for 123 within the strings aaa123, bbb456, ccc789,etc.). Once a match is found, I would like the function to return another value from the same row on Sheet2 that contains the value from Sheet1 (within the string). Can you help me? I must use cell references for the values on Sheet1 because I am working with thousands of unique supplier numbers. In addition, all cell formats are general. I prefer to use the INDEX(MATCH combination rather than the VLOOKUP option.
View Replies!
View Related
Highlighting Within A Sort
how can I highlight any value that is duplicated within the same sorted value? Example: I sort column B so that all the names are in order. In column F I want them to be highlighted when there is duplicate name within the same sorted names of B.
View Replies!
View Related
Highlighting Big Changes
I've a large spreadsheet packed full of values, and % variences etc etc My boss has tasked me to try and highlight each week the major changes from the week before. The whole spreadsheet is full of vlookups based on a cell - Z3 in this case. Cell Z3 is a week number, and when i change that, everything is updated based on a load of other sheets. Is there a quick and easy cheatuing way using some code that i can analyse all the changes from what the values were before, to what they are now, and highlight the major movers?
View Replies!
View Related
Highlighting And Printing ...
I have attached a sample sheet which is for invigilation of exams. In columns D, E & F are the initials of people invigilating at different times. What I want to able to do is to print personised sheets for each person where there initals are in bold and the box has a black border around it. This is very labour intensive if I do it manually as have to highlight one person, print, unhighlight and rehighlight the next person and so on. What I was wondering is could I use a macro to automate this process. So it has a list of all the initals and it searches through and highlights the person and prints it and then moves onto the next.
View Replies!
View Related
Highlighting Rows ..?
I have a spreadsheet with one column. For example, it contains A A A A B B C C C D D I want to highlight the A's and don't highlight the B's and then highlight the C's and then don't highlight the D's and so on. I want to highlight or not highlight the entire row.
View Replies!
View Related
Highlighting A Link
I have created a data base of all the spare parts we have at work. In the last column I have entered a hyperlinks that take you to a layout of the store and shows you where the part is located i.e. – 1C would take you to shelf ‘1’ level ‘C’ on the drawing in another tab in the same Excel workbook. I would be pleased if anyone could help me by suggesting away that I can make the destination cell highlighted or more prominent once you have clicked on the link. Also can I make the Find window automatically appear when the workbook is first opened?
View Replies!
View Related
Highlighting A Certain Value In A Column
i am trying to get a VBA to find the value 'TRCO' select the row and highlight the row in red, i have tried the way below but i get an error saying 'type mismatch'. If Range("b7", "b51166").Value = "TRCO" Then Rows.Select Selection.Font.ColorIndex = 3 End If
View Replies!
View Related
Lookup Data In Row & Cell Based On Data In Row And Cell
I am looking for a way to do what i would call a dual vlookup. i have some data in a number of sheets and need to do some consolidation into a summary sheet. problem is, not all the sheets have data in the same columns and rows but all the data is the same. what i am trying to do is something like vlookup("a",sheet2!A:M,***need to put the column of the data here***,0) i cant just put in the column number because it changes on each sheet, need some way to do another lookup there, like an hlookup or something, so that i could lookup which column contained 'sales' and return that number so it can be put into the vlookup.
View Replies!
View Related
Adding Data By Row Based On Typing In Cell Contained In Row
I've built (but not completed) a spreadsheet used to organize multiple Bill of Material lists. This list is a row-by row list of products with information relating to a specific part number including: Mfg, Part#, Qty Used, Cost Ea, Vendor, among many more. Here's what I'm trying to accomplish first. As this list grows, my intension is the be able to recall a row of data (or control what columns of the row are recalled) from this list and be able to easily add them to the list. I would like to be able to do this by typing in data in one cell in the row (like the Part#), and then have a macro or VB code that will automatically fill in a defined number of columns with the descriptive data like: Mfg, Cost Ea, Vendor, etc. Currently, the data for the rows (if it already exists) would reside higher up in the list on the same worksheet but could also potentially be on another tab in the workbook.
View Replies!
View Related
Is There A Macro To Transfer A Row Of Data From Top To Bottom Row Of Range ?
I have a s/s encompassing over 350,000 rows. Data consists of a series of ranges from columns A:N and rows varying in number between 3 and 30. There are two blank lines between each range of data. Each range is (with exception) numerically ordered down column H (e.g, 1, 2, 3, etc). Problem: there are around 1100 occasions when a range contains a row of data which is to be disqualified from consideration. On these occasions the data always appears in the top row of the range and is identifiable in column H by the nine codes 111, 222, 333, 444, 555, 666, 777, 888, and 999). I wish to find a macro which will : 1. where the top row of a range in column H is one of the codes 111 to 999, transfer that row of data to the bottom of the range. 2. delete the now empty top row from which the transfer was made from. 3. insert a new blank row at the bottom of the range below the row which the data was transferred to (in order to maintain the two blank rows above and below each range).
View Replies!
View Related
Consolidate Data From Many Worksheet To A Master Worksheet
I'm trying to consolidate data from many worksheet to a master worksheet. So far, thanks to the help of some experts, i've got this code. But i need to tweak it further to suit my requirements. Sub CopyDataToConsol() Dim Sht1 As Worksheet Dim Sht2 As Worksheet Dim SrcRg As Range How can i tweak the above code to: 1) copy the lastest updated row to the Master sheet, instead of copying the whole set of data over and over again.. 2) automate it by using worksheet change?
View Replies!
View Related
Consolidating Multiple Worksheet Data Into One Worksheet
I have an excel 2007 work book with multiple worksheets that updates daily. I want to bring all the data from each of the work sheets into a new worksheet so that i can create a pivot table. All the data in the worksheets have the same headings and correspond to the days of the year. I think i need to use some sort of range look up?
View Replies!
View Related
Copy Selected Data From Worksheet To Different Worksheet
As I am managing a few projects at once, I would like to create a To Dos List for each project (seperated by different worksheets). To make my life a bit easier, I hope to show all the To Dos which is due on a particular day on a separate worksheet. In other words, I am hoping to write a macro which allows me to copy all the relevant To Dos (of that day) from different worksheets and compiled it into a single list on a new worksheet. Note: 1) Each row of To Do contains 3 columns, Date, Priority and Descriptions. 2) The To Dos for each project are NOT arrange in sequence by dates. 3) Number of project will increase over time, therefore the number of worksheets will also increase.
View Replies!
View Related
Highlighting For Duplicate Entries
I would like to check column B and if any of these values match values in column J hightlight the cell on the same row as the match on column E green. I don't know much about excel but this would be a tremendous help because i'm looking through quite a bit of data. I've tried using conditional formatting and i can't seem to get that to work for my needs. Let me know if you need anymore information.
View Replies!
View Related
Highlighting More Rows Than I Want To - How To Stop Please
I am using a 2007 excel spreadsheet and on parts of it I can't just highlight one horizontal row of cells, it wants to highlight three down. I suppose that I have asked it to do this but I can't for the life of me undo it. I have tried unmerging cells, cutting and pasting somewhere else, but nothing works. Can anyone help me please as it's driving me to distraction?
View Replies!
View Related
|
TRACKER
