| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to copy data in one sheet to another
Sheet1 'Transaction Database' stores all transactions by column from row 5
to row 59. Sheet1 Cell Row 6 Column C, D, E, F, G etc. is the type of transaction--Open, Pending, Closed, Withdrawn, Expired. Then I have a seperate sheet for each titled--Open, Pending, Closed, Withdrawn, Expired. I have entered this formula in each sheet--Open, Pending, Closed, Withdrawn, Expired-- to move the type of transaction from sheet1 'Transaction Database' to the appropriate sheet: =IF('Transaction Database'!C$6="open",'Transaction Database'!C5,"") The only thing that changes is "Open" Pending" "Closed" "Withdrawn" "Expired"... My problem is, the type of transaction is copied to the appropriate sheet but in the same column as it is in sheet1. I need the transaction to copy to the first available column from column C on in each sheet. Is this possible??? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to copy data in one sheet to another
Hi Mike,
try 'Copy information in the new Tab Worksheets(Your Sheet).Select Set ws1 = Worksheets(Your Sheet) 'find first empty row in database starting column C With ws1 iRow1 = .Cells(.Rows.Count, 3).End(xlUp).Offset(1, 0).Row "Mike" wrote: Sheet1 'Transaction Database' stores all transactions by column from row 5 to row 59. Sheet1 Cell Row 6 Column C, D, E, F, G etc. is the type of transaction--Open, Pending, Closed, Withdrawn, Expired. Then I have a seperate sheet for each titled--Open, Pending, Closed, Withdrawn, Expired. I have entered this formula in each sheet--Open, Pending, Closed, Withdrawn, Expired-- to move the type of transaction from sheet1 'Transaction Database' to the appropriate sheet: =IF('Transaction Database'!C$6="open",'Transaction Database'!C5,"") The only thing that changes is "Open" Pending" "Closed" "Withdrawn" "Expired"... My problem is, the type of transaction is copied to the appropriate sheet but in the same column as it is in sheet1. I need the transaction to copy to the first available column from column C on in each sheet. Is this possible??? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to copy data in one sheet to another
Hi Eduardo,
Sorry, I don't understand your response... The formula I included in my question is in the 5 seperate sheets titled Open, Pending, Closed, Withdrawn, Expired. They all pull the files from the files in Sheet1 titled 'Transaction Database'. But, lets say the file in sheet 1 column F5:59 has Open in F5 the the formula in the 'Open' sheet pulls the data into the 'Open' sheet, column F5:59 and leaves C and D blank. I need it to go to column C or the first available column. Sorry if I don't understand or if I am not explaining myself properly... "Eduardo" wrote: Hi Mike, try 'Copy information in the new Tab Worksheets(Your Sheet).Select Set ws1 = Worksheets(Your Sheet) 'find first empty row in database starting column C With ws1 iRow1 = .Cells(.Rows.Count, 3).End(xlUp).Offset(1, 0).Row "Mike" wrote: Sheet1 'Transaction Database' stores all transactions by column from row 5 to row 59. Sheet1 Cell Row 6 Column C, D, E, F, G etc. is the type of transaction--Open, Pending, Closed, Withdrawn, Expired. Then I have a seperate sheet for each titled--Open, Pending, Closed, Withdrawn, Expired. I have entered this formula in each sheet--Open, Pending, Closed, Withdrawn, Expired-- to move the type of transaction from sheet1 'Transaction Database' to the appropriate sheet: =IF('Transaction Database'!C$6="open",'Transaction Database'!C5,"") The only thing that changes is "Open" Pending" "Closed" "Withdrawn" "Expired"... My problem is, the type of transaction is copied to the appropriate sheet but in the same column as it is in sheet1. I need the transaction to copy to the first available column from column C on in each sheet. Is this possible??? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to copy data in one sheet to another
Hi Mike,
Sorry I misunderstood you, just a question when you say you have entered the formula, where have you enter it, is that a formula in a cell or a code, if that is part of a code could you please post your code "Mike" wrote: Hi Eduardo, Sorry, I don't understand your response... The formula I included in my question is in the 5 seperate sheets titled Open, Pending, Closed, Withdrawn, Expired. They all pull the files from the files in Sheet1 titled 'Transaction Database'. But, lets say the file in sheet 1 column F5:59 has Open in F5 the the formula in the 'Open' sheet pulls the data into the 'Open' sheet, column F5:59 and leaves C and D blank. I need it to go to column C or the first available column. Sorry if I don't understand or if I am not explaining myself properly... "Eduardo" wrote: Hi Mike, try 'Copy information in the new Tab Worksheets(Your Sheet).Select Set ws1 = Worksheets(Your Sheet) 'find first empty row in database starting column C With ws1 iRow1 = .Cells(.Rows.Count, 3).End(xlUp).Offset(1, 0).Row "Mike" wrote: Sheet1 'Transaction Database' stores all transactions by column from row 5 to row 59. Sheet1 Cell Row 6 Column C, D, E, F, G etc. is the type of transaction--Open, Pending, Closed, Withdrawn, Expired. Then I have a seperate sheet for each titled--Open, Pending, Closed, Withdrawn, Expired. I have entered this formula in each sheet--Open, Pending, Closed, Withdrawn, Expired-- to move the type of transaction from sheet1 'Transaction Database' to the appropriate sheet: =IF('Transaction Database'!C$6="open",'Transaction Database'!C5,"") The only thing that changes is "Open" Pending" "Closed" "Withdrawn" "Expired"... My problem is, the type of transaction is copied to the appropriate sheet but in the same column as it is in sheet1. I need the transaction to copy to the first available column from column C on in each sheet. Is this possible??? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to copy data in one sheet to another
Hi Eduardo,
The Formula is: =IF('Transaction Database'!C$6="open",'Transaction Database'!C5,"") I enter it in cell C5 and copy/paste down the column to C59 then copy/paste C5:C59 to all columns... The formula in each sheet is changed for "Open to Pending, Closed, Withdrawn, Expired for each coresponding sheet "Eduardo" wrote: Hi Mike, Sorry I misunderstood you, just a question when you say you have entered the formula, where have you enter it, is that a formula in a cell or a code, if that is part of a code could you please post your code "Mike" wrote: Hi Eduardo, Sorry, I don't understand your response... The formula I included in my question is in the 5 seperate sheets titled Open, Pending, Closed, Withdrawn, Expired. They all pull the files from the files in Sheet1 titled 'Transaction Database'. But, lets say the file in sheet 1 column F5:59 has Open in F5 the the formula in the 'Open' sheet pulls the data into the 'Open' sheet, column F5:59 and leaves C and D blank. I need it to go to column C or the first available column. Sorry if I don't understand or if I am not explaining myself properly... "Eduardo" wrote: Hi Mike, try 'Copy information in the new Tab Worksheets(Your Sheet).Select Set ws1 = Worksheets(Your Sheet) 'find first empty row in database starting column C With ws1 iRow1 = .Cells(.Rows.Count, 3).End(xlUp).Offset(1, 0).Row "Mike" wrote: Sheet1 'Transaction Database' stores all transactions by column from row 5 to row 59. Sheet1 Cell Row 6 Column C, D, E, F, G etc. is the type of transaction--Open, Pending, Closed, Withdrawn, Expired. Then I have a seperate sheet for each titled--Open, Pending, Closed, Withdrawn, Expired. I have entered this formula in each sheet--Open, Pending, Closed, Withdrawn, Expired-- to move the type of transaction from sheet1 'Transaction Database' to the appropriate sheet: =IF('Transaction Database'!C$6="open",'Transaction Database'!C5,"") The only thing that changes is "Open" Pending" "Closed" "Withdrawn" "Expired"... My problem is, the type of transaction is copied to the appropriate sheet but in the same column as it is in sheet1. I need the transaction to copy to the first available column from column C on in each sheet. Is this possible??? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to copy data in one sheet to another
Hi Mike, I don't understand something,
1) Sheet 1 is named Transaction Database??, 2) if Data starts in C5, as per below why sheet1 cell c6 = open 3) could you send an example just a few # to make sense, thank you "Mike" wrote: Hi Eduardo, The Formula is: =IF('Transaction Database'!C$6="open",'Transaction Database'!C5,"") I enter it in cell C5 and copy/paste down the column to C59 then copy/paste C5:C59 to all columns... The formula in each sheet is changed for "Open to Pending, Closed, Withdrawn, Expired for each coresponding sheet "Eduardo" wrote: Hi Mike, Sorry I misunderstood you, just a question when you say you have entered the formula, where have you enter it, is that a formula in a cell or a code, if that is part of a code could you please post your code "Mike" wrote: Hi Eduardo, Sorry, I don't understand your response... The formula I included in my question is in the 5 seperate sheets titled Open, Pending, Closed, Withdrawn, Expired. They all pull the files from the files in Sheet1 titled 'Transaction Database'. But, lets say the file in sheet 1 column F5:59 has Open in F5 the the formula in the 'Open' sheet pulls the data into the 'Open' sheet, column F5:59 and leaves C and D blank. I need it to go to column C or the first available column. Sorry if I don't understand or if I am not explaining myself properly... "Eduardo" wrote: Hi Mike, try 'Copy information in the new Tab Worksheets(Your Sheet).Select Set ws1 = Worksheets(Your Sheet) 'find first empty row in database starting column C With ws1 iRow1 = .Cells(.Rows.Count, 3).End(xlUp).Offset(1, 0).Row "Mike" wrote: Sheet1 'Transaction Database' stores all transactions by column from row 5 to row 59. Sheet1 Cell Row 6 Column C, D, E, F, G etc. is the type of transaction--Open, Pending, Closed, Withdrawn, Expired. Then I have a seperate sheet for each titled--Open, Pending, Closed, Withdrawn, Expired. I have entered this formula in each sheet--Open, Pending, Closed, Withdrawn, Expired-- to move the type of transaction from sheet1 'Transaction Database' to the appropriate sheet: =IF('Transaction Database'!C$6="open",'Transaction Database'!C5,"") The only thing that changes is "Open" Pending" "Closed" "Withdrawn" "Expired"... My problem is, the type of transaction is copied to the appropriate sheet but in the same column as it is in sheet1. I need the transaction to copy to the first available column from column C on in each sheet. Is this possible??? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to copy data in one sheet to another
Hi Eduardo,
Sheet1 is named 'Transaction Database... Data starts in C5. C5 is the transaction number C6 is the status of a transaction Open,Pending,Closed,Withdrawn or Expired. C7 is Sellers Name C8 is Buyers Name C9 is Listing Date C10 is Agents Name etc... Column D would be say Transaction #2 Column E would be Transaction #3 and so on... Hope this helps! "Eduardo" wrote: Hi Mike, I don't understand something, 1) Sheet 1 is named Transaction Database??, 2) if Data starts in C5, as per below why sheet1 cell c6 = open 3) could you send an example just a few # to make sense, thank you "Mike" wrote: Hi Eduardo, The Formula is: =IF('Transaction Database'!C$6="open",'Transaction Database'!C5,"") I enter it in cell C5 and copy/paste down the column to C59 then copy/paste C5:C59 to all columns... The formula in each sheet is changed for "Open to Pending, Closed, Withdrawn, Expired for each coresponding sheet "Eduardo" wrote: Hi Mike, Sorry I misunderstood you, just a question when you say you have entered the formula, where have you enter it, is that a formula in a cell or a code, if that is part of a code could you please post your code "Mike" wrote: Hi Eduardo, Sorry, I don't understand your response... The formula I included in my question is in the 5 seperate sheets titled Open, Pending, Closed, Withdrawn, Expired. They all pull the files from the files in Sheet1 titled 'Transaction Database'. But, lets say the file in sheet 1 column F5:59 has Open in F5 the the formula in the 'Open' sheet pulls the data into the 'Open' sheet, column F5:59 and leaves C and D blank. I need it to go to column C or the first available column. Sorry if I don't understand or if I am not explaining myself properly... "Eduardo" wrote: Hi Mike, try 'Copy information in the new Tab Worksheets(Your Sheet).Select Set ws1 = Worksheets(Your Sheet) 'find first empty row in database starting column C With ws1 iRow1 = .Cells(.Rows.Count, 3).End(xlUp).Offset(1, 0).Row "Mike" wrote: Sheet1 'Transaction Database' stores all transactions by column from row 5 to row 59. Sheet1 Cell Row 6 Column C, D, E, F, G etc. is the type of transaction--Open, Pending, Closed, Withdrawn, Expired. Then I have a seperate sheet for each titled--Open, Pending, Closed, Withdrawn, Expired. I have entered this formula in each sheet--Open, Pending, Closed, Withdrawn, Expired-- to move the type of transaction from sheet1 'Transaction Database' to the appropriate sheet: =IF('Transaction Database'!C$6="open",'Transaction Database'!C5,"") The only thing that changes is "Open" Pending" "Closed" "Withdrawn" "Expired"... My problem is, the type of transaction is copied to the appropriate sheet but in the same column as it is in sheet1. I need the transaction to copy to the first available column from column C on in each sheet. Is this possible??? |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to copy data in one sheet to another
Hi Mike,
you mention before that in F5 you might have Open, do you have the status in two different columns or in both of them C and F "Mike" wrote: Hi Eduardo, Sheet1 is named 'Transaction Database... Data starts in C5. C5 is the transaction number C6 is the status of a transaction Open,Pending,Closed,Withdrawn or Expired. C7 is Sellers Name C8 is Buyers Name C9 is Listing Date C10 is Agents Name etc... Column D would be say Transaction #2 Column E would be Transaction #3 and so on... Hope this helps! "Eduardo" wrote: Hi Mike, I don't understand something, 1) Sheet 1 is named Transaction Database??, 2) if Data starts in C5, as per below why sheet1 cell c6 = open 3) could you send an example just a few # to make sense, thank you "Mike" wrote: Hi Eduardo, The Formula is: =IF('Transaction Database'!C$6="open",'Transaction Database'!C5,"") I enter it in cell C5 and copy/paste down the column to C59 then copy/paste C5:C59 to all columns... The formula in each sheet is changed for "Open to Pending, Closed, Withdrawn, Expired for each coresponding sheet "Eduardo" wrote: Hi Mike, Sorry I misunderstood you, just a question when you say you have entered the formula, where have you enter it, is that a formula in a cell or a code, if that is part of a code could you please post your code "Mike" wrote: Hi Eduardo, Sorry, I don't understand your response... The formula I included in my question is in the 5 seperate sheets titled Open, Pending, Closed, Withdrawn, Expired. They all pull the files from the files in Sheet1 titled 'Transaction Database'. But, lets say the file in sheet 1 column F5:59 has Open in F5 the the formula in the 'Open' sheet pulls the data into the 'Open' sheet, column F5:59 and leaves C and D blank. I need it to go to column C or the first available column. Sorry if I don't understand or if I am not explaining myself properly... "Eduardo" wrote: Hi Mike, try 'Copy information in the new Tab Worksheets(Your Sheet).Select Set ws1 = Worksheets(Your Sheet) 'find first empty row in database starting column C With ws1 iRow1 = .Cells(.Rows.Count, 3).End(xlUp).Offset(1, 0).Row "Mike" wrote: Sheet1 'Transaction Database' stores all transactions by column from row 5 to row 59. Sheet1 Cell Row 6 Column C, D, E, F, G etc. is the type of transaction--Open, Pending, Closed, Withdrawn, Expired. Then I have a seperate sheet for each titled--Open, Pending, Closed, Withdrawn, Expired. I have entered this formula in each sheet--Open, Pending, Closed, Withdrawn, Expired-- to move the type of transaction from sheet1 'Transaction Database' to the appropriate sheet: =IF('Transaction Database'!C$6="open",'Transaction Database'!C5,"") The only thing that changes is "Open" Pending" "Closed" "Withdrawn" "Expired"... My problem is, the type of transaction is copied to the appropriate sheet but in the same column as it is in sheet1. I need the transaction to copy to the first available column from column C on in each sheet. Is this possible??? |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to copy data in one sheet to another
No, Row 5 is allways the Transaction Number. Row 6 is always the Status of
the transaction which is either Open,Pending,Closed,Withdrawn,Expired. Each transaction file is entered in a column (C,D,E,F,G,H,I,J,K,l and so on) in sheet1 "Transaction Data Base" The status of each file (Which is always in row 6 column (C,D,E,F,G,H,I,J,K,l and so on) changes to either Open,Pending,Closed,Withdrawn,Expired The formula in each of the other 5 sheets idenifies the status of each transaction and pulls it into the approproate sheet Open,Pending,Closed,Withdrawn,Expired "Eduardo" wrote: Hi Mike, you mention before that in F5 you might have Open, do you have the status in two different columns or in both of them C and F "Mike" wrote: Hi Eduardo, Sheet1 is named 'Transaction Database... Data starts in C5. C5 is the transaction number C6 is the status of a transaction Open,Pending,Closed,Withdrawn or Expired. C7 is Sellers Name C8 is Buyers Name C9 is Listing Date C10 is Agents Name etc... Column D would be say Transaction #2 Column E would be Transaction #3 and so on... Hope this helps! "Eduardo" wrote: Hi Mike, I don't understand something, 1) Sheet 1 is named Transaction Database??, 2) if Data starts in C5, as per below why sheet1 cell c6 = open 3) could you send an example just a few # to make sense, thank you "Mike" wrote: Hi Eduardo, The Formula is: =IF('Transaction Database'!C$6="open",'Transaction Database'!C5,"") I enter it in cell C5 and copy/paste down the column to C59 then copy/paste C5:C59 to all columns... The formula in each sheet is changed for "Open to Pending, Closed, Withdrawn, Expired for each coresponding sheet "Eduardo" wrote: Hi Mike, Sorry I misunderstood you, just a question when you say you have entered the formula, where have you enter it, is that a formula in a cell or a code, if that is part of a code could you please post your code "Mike" wrote: Hi Eduardo, Sorry, I don't understand your response... The formula I included in my question is in the 5 seperate sheets titled Open, Pending, Closed, Withdrawn, Expired. They all pull the files from the files in Sheet1 titled 'Transaction Database'. But, lets say the file in sheet 1 column F5:59 has Open in F5 the the formula in the 'Open' sheet pulls the data into the 'Open' sheet, column F5:59 and leaves C and D blank. I need it to go to column C or the first available column. Sorry if I don't understand or if I am not explaining myself properly... "Eduardo" wrote: Hi Mike, try 'Copy information in the new Tab Worksheets(Your Sheet).Select Set ws1 = Worksheets(Your Sheet) 'find first empty row in database starting column C With ws1 iRow1 = .Cells(.Rows.Count, 3).End(xlUp).Offset(1, 0).Row "Mike" wrote: Sheet1 'Transaction Database' stores all transactions by column from row 5 to row 59. Sheet1 Cell Row 6 Column C, D, E, F, G etc. is the type of transaction--Open, Pending, Closed, Withdrawn, Expired. Then I have a seperate sheet for each titled--Open, Pending, Closed, Withdrawn, Expired. I have entered this formula in each sheet--Open, Pending, Closed, Withdrawn, Expired-- to move the type of transaction from sheet1 'Transaction Database' to the appropriate sheet: =IF('Transaction Database'!C$6="open",'Transaction Database'!C5,"") The only thing that changes is "Open" Pending" "Closed" "Withdrawn" "Expired"... My problem is, the type of transaction is copied to the appropriate sheet but in the same column as it is in sheet1. I need the transaction to copy to the first available column from column C on in each sheet. Is this possible??? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| If formula copy data to another sheet | Excel Discussion (Misc queries) | |||
| Copy data from one sheet to another using a formula | Excel Discussion (Misc queries) | |||
| Formula Help - Copy data from one sheet to another based on criter | Excel Worksheet Functions | |||
| how to copy a cell with formula from sheet 1 (data is all vertical) into sheet 2 | Excel Worksheet Functions | |||
| With a formula copy data from one sheet to another | New Users to Excel | |||
Linear Mode
