How To Move Data From One Worksheet To Another Based On Formula Result
Jun 20, 2013
I have a formula sheet that uses an IF statement to determine if one columns data is bigger than another. Out of the 300 or so rows there are approx 20 that come back as yes (this is in column A) and the rest are blank
What I would like to do, is for the 20 or so rows, I would like to pull out (copy) columns B, J and L and put them into worksheet 2. Preferably without any gaps in the rows or columns.
I hope this is enough information, I am using MS excel version 2010 although I think the people who will ultimately be using it are on an earlier version.
View 6 Replies
ADVERTISEMENT
Jan 10, 2012
Error in the code, where I'm trying to copy the data (based on criteria) from one worksheet to another, then delete the data from the first worksheet. In the code (I have complied from this message board) I get an 1004 error "Application-defined or Object-defined error".
Code:
Option Explicit
Sub CopyALColKYes()
Dim NR As Long, c As Range, firstaddress As String
Application.ScreenUpdating = False
NR = Sheets("Completed-Expired").Cells(Rows.Count, 13).End(xlUp).Row + 1
[Code] .........
View 9 Replies
View Related
Oct 23, 2013
I have a excel file with two tabs, one with raw data, with data ordered by geographic region.
I have another blank tab, where I've created a dropdown list of the geographic regions.
How do I move all the data from worksheet to the other based on that geographic region?
View 9 Replies
View Related
Dec 6, 2009
This formula tells me the highest number: =LARGE((G36,O36,W36,AE36),1)
The result is AE36. The answer is correct. I get the highest number. I want the formula now shows Y3 instead of AE36.
In my situation Y3 is the name of 1 of my tables. In my case I get from this table, the highest number.
These are 6 cells to the left side. These are 33 cells to the upper part. In the future I will work with more tables. So the answer must always move
I've been busy this few days to investigate. That is why I know it works with offset. Anyway, I still do not know how it goes.
View 5 Replies
View Related
Jul 16, 2005
I currently have a consolidated worksheet (thanks Bill!) called " Dashboard" that contains closed items that are marked by a validated column that can only contain "Closed, Open, or In-Progress." Is there a way to move the rows with a value of "Closed" to another worksheet called "Completed"? Also when this move is done, that row is no longer necessary in Dashboard and should be removed. So I'm guessing its a lot like a cut and paste and then a delete row/shift cells up?
here are some additional information:
The worksheet has a locked header that is 6 rows deep (the values for the "Status" column begins on row 7 and on.)
The "status" column is at column 11.
View 8 Replies
View Related
Jan 15, 2008
I have a excel file to keep track of tasks or actions that need to be performed. What I am looking for, is an automated utility or code that will allow excel to automatically move entire rows (so an entire task) of completed tasks to another sheet called, "Completed Actions". In Actions sheet I have a column for " status" and here you have to select from a drop down menu, either "On-going", "Urgent" or "Done". What I would like, is that once you have selected "Done", the entire row or entry, will be automatically moved to the "Completed Actions" sheet.
View 7 Replies
View Related
Sep 19, 2013
IF formula to transfer specific text and values from sheet 1 to sheet 2 (please refer to attachment). Sheet 2 should display the name, state and unprocessed rate for each month for individuals with a rate of 95% and 30 or more unprocessed files.
Unprocessed Files.xlsx
View 9 Replies
View Related
Jan 15, 2010
I have a Excel workbook with two sheets - 'MainDataSheet' and 'ArchiveSheet' .
The 'MainDataSheet' has 5 columns and one of the column is 'status'.
The 'MainDataSheet' will have a command button 'MoveData' which will trigger the VBA macro to move data rows from 'MainDataSheet' to 'ArchiveSheet' . Only the rows having value set to "MOVE" in the 'status' column have to be moved.
View 9 Replies
View Related
Mar 3, 2007
Any other day but today (I've been starring at numbers too long) I could do this.
I need a simple formula that will return:
a. 3 if resut is >= $2.5M
b. 2 if over >= $0.5M
c. 1 if
View 9 Replies
View Related
Jul 27, 2006
How can I move data from one worksheet and place in another worksheet in the appropriate column? I exported data from Access to Excel in order but I need to group the data in excel.
View 3 Replies
View Related
Dec 13, 2012
I have a scorecard that looks something like this:
90% or greater=3
80%-89%=2
70%-79%=1
0%-69%=0
I need the "cell" to react accordingly and I'm lost.
View 2 Replies
View Related
Aug 1, 2006
how to have a cell equate to an autoshape based on the results of an "if" statement?
View 4 Replies
View Related
Jan 4, 2007
I would like to use formulas to display different results eg UNDERBUDGET - OK - OVERLIMIT etc. Ideally they would be shown using different colours & text size. I can't seem to change the colour/text size inside the formula for the different words - only for the whole cell.
View 2 Replies
View Related
Aug 2, 2013
I would like to move the current row of data to another sheet when I change a value in the row from an x to an s. I think I could use an If statement, but if you can move data from one worksheet to another, I don't know the syntax to use.
View 9 Replies
View Related
Mar 17, 2012
I need a code that will allow me to move data from a worksheet to a worksheet from which I came from. e.g. worksheet "Sheet 6" opens "Sheet 10", then after filling data in "Sheet 10" I need to move this data back to "Sheet 6" in the cells in range "F12 to F56". Please note that the data in "Sheet 10" is in the same range as in "Sheet 6"
View 1 Replies
View Related
Aug 5, 2014
How to formulate results of students in excel sheet.
From the attached picture (capture1.jpg) of the excel sheet - The rules of exams are:
1. if candidate scores 50% in all 4 papers, PASS and proceed to next year
2. if candidate scores 50% in 2 or 3 papers + borderline fail in 1 or 2 papers, VIVA VOCE exam for the borderline failed paper (Definition of borderline fail is candidate scoring 45 to 50 marks)
3. if candidate scores 50% in upto 2 papers + borderline fail in more than 2 papers, RE-EXAM
4. if candidate scores 45% in 2 or more papers, FAIL and repeat the year
The rules are in the attached picture flowchart.jpg
I am unsuccessful in writing a formula for such multiple criteria...
View 12 Replies
View Related
Dec 6, 2012
I'm looking to conditional format a cell/cells based on whether a formula result returns a whole number or not.
I don't want to include the formula in the sheet itself, just have that as the formula in the condition.
The formula will be along the lines of:
=IF(SUM(BB10/BA10)"a whole number",TRUE,FALSE)
My problem is, is that I don't know who to refer to "a whole number" in Excel formula language.
View 4 Replies
View Related
Feb 15, 2007
I need a formula to count cells based on the date, so that I can have a blank cell when the answer is 0. I am adding values cumulative and future cells need to be blank because I have a graph that has a trend line and I don't want the trend line to fall off at the end. I also don't want to have to go back to this every month and update it.
Column A
Date
1/2/2007 0:00
1/3/2007 0:00
1/10/2007 0:00
2/10/2007 0:00
2/10/2007 0:00
2/15/2007 0:00
3/22/2007 0:00
3/22/2007 0:00
3/22/2007 0:00
4/31/2007 0:00
4/31/2007 0:00
4/31/2007 0:00
Column B
Invoice #
CM-0003881
CM-0003882
CM-0003883
CM-0003888
CM-0003928
CM-0003932
CM-0003933
CM-0003985
CM-0004007
CM-0004008
CM-0004009
CM-0004065
I have this formula in cell F3 that will add the dates through the end of January:
=SUMPRODUCT((A1:A1000>=DATEVALUE("1/1/2007"))*(A1:A1000
View 10 Replies
View Related
Sep 14, 2006
how to run a macro from an IF function, if the function is true macro 1 runs if the function is false macro 2 runs.
View 2 Replies
View Related
Sep 27, 2006
I have a spreadsheet that give me the percentage difference of two cell say a1 and a2 (=a2/a1)[format as % two decimal places]. The result is on say b5 as a %[format as % two decimal places]. Now on B6 I want to do this:
if B5 is >3.01% then b6 =" Market Test Required"
if B5 is +3% then b6 =110
if B5 is +2% then b6 =106
if B5 is +1% then b6 =103
if B5 is 0% then b6 =100
if B5 is -1% then b6 =96
if B5 is -2% then b6 =93
if B5 is -3% then b6 =90
if B5 is >-3.01% then b6 ="Market Test Required"
So on and so on.... I would really appreciate your help on this issue.
I have used excel for some time now but not with complex formulas or any vb.
View 9 Replies
View Related
Aug 5, 2008
I have an Excel workbook with multiple sheets. In one sheet, there are many columns that automatically get hidden based on cells values (=1) in another sheet as I type. I use this
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$7" And Target.Value = 1 Then
Sheets("Stakes").Range("E:E").EntireColumn.Hidden = True
Else
If Target.Address = "$C$7" And Target.Value <> 1 Then
Sheets("Stakes").Range("E:E").EntireColumn.Hidden = False
End If
End If
End Sub
Recently I had to change the second sheet that instead of manually entered values, formula results appear in cells. And the above code doesn't work anymore, columns are always stay unhidden. How can I achieve what I want? I need columns get hidden if formula results =1.
View 4 Replies
View Related
Dec 27, 2013
I'm looking to automatically move data from my working worksheet, when a particular column states "Won" or "Lost". I'm hoping the entire row of data can be cut, and added to the appropriate sheet, determined by "Won" or "Lost".
View 5 Replies
View Related
Feb 6, 2009
I need to do is move a list of data in an excel worksheet to Notepad. The data is 16 numbers long, but the Notepad needs to be left justified to 19 characters. I can't figure out a way to move spaces over to notepad.
View 6 Replies
View Related
Jul 22, 2009
Is it possible to have a macro compare two wokrsheets and move the totals from one sheet to the other worksheet.
Here are the worksheets: ....
View 9 Replies
View Related
Feb 20, 2014
We receive payments in either USD or HNL. I want to be able to have a final price (last column) that puts all prices in USD. I have a reference cell on a separate worksheet (drop down inputs) that we will use to store the data for validations and the conversion rate. We use one conversion rate for all transactions for a fiscal year, so only need to update once a year. Not sure how to make this work.
View 2 Replies
View Related
May 16, 2009
The following code works perfect but the "change" event is only triggered when working directly on intersect range. Tried using the "calculation" event but could not figure it out. This is what I want:
1) To replace the code provided below using the calculation event
2) To only trigger the event for the row(s) where the new value was generated, not for the whole "For Each" statement
3) To use one single code for all worksheets, instead of copying the code in every working worksheet on the workbook, if feasible
4) And I would like a "second alternative", where the user of the workbook can click on a button and trigger the event on every row on the workbook that has a non empty cell within the intersect range, assuming that the intersect range column is the same for all worksheets
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range, fc As Long, bc As Long, bf As Boolean
Set d = Intersect(Range("I:I"), Target)
If d Is Nothing Then Exit Sub
For Each c In d
Select Case UCase(c)
Case "C"
fc = 1: fb = True: bc = 4
Case "O"
fc = 2: fb = True: bc = 3
Case "D"
fc = 2: fb = True: bc = 46
Case "G"
fc = 2: fb = True: bc = 5..................
View 4 Replies
View Related
May 24, 2008
I'm quite a novice at Excel. I have a column of values that I sum as follows;
A
0
0
0
0
0
0 <----------------sum of A1:A5
A formula may change one of the values in the above column to a '1' which means the sum will become '1'. The sum can only be '1' or '0' and only one value in the column will ever change. I need to add a value of 2 to another cell (say, C1) when the sum of A1:A5 changes from a value of '0' to '1'. I know this will probably involve the worksheet change event but am having a problem implementing it.
View 7 Replies
View Related
Jan 20, 2009
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 3 Replies
View Related
Oct 31, 2007
situation: 3 rows of data, row 1 has HDR in several cells (always in A1).
task
1. search row one for next example of cell containing HDR, excluding cell A1.
2. IF found, create a new worksheet and move all data below and/or to the right of that cell to the new sheet.
3. Repeat steps two and three until the search does not turn up another HDR except in A1.
View 9 Replies
View Related
Mar 4, 2013
I have this macro which ranks in reverse order col AA , I need to get the macro to do this ranking in col U .
The smallest number would be ranked 1 to the highest number ranked last .
All I need is the current result too go into col U and not col T when I run macro .
Please ignore the Value error in col T they don't matter at all .
Sheet1 STUVWXYZAA1Fsz PtsFwinFplNo.Swin213#VALUE!
SCRSCR1SCR3135 7.52.427.2413#VALUE!
SCRSCR3SCR5132 4.51.6545.36131 5.51.855.17134 5.51.865.88133 5.51.875.491310 266.5819.610138
184.5916.911136 164.5101312137 102.81114.21313#VALUE! SCRSCR12SCR14139 185.51317.8
[Code] ..........
View 2 Replies
View Related
