Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If A1=dog or A2=cat in wrkbk1 sheet1, then copy row to wrkbk2 shee
Hi,
Help pls... I'm only familiar with very basic functions/formulas in Excel 2003. I need to copy an entire row from one wrkbk/wrksht to another wrkbk/wrksht based on the criteria in two individual cells in that row. (If A1=dog or A2=cat in wrkbk1 sheet1, then copy entire row to wrkbk2 sheet1) The original worksheet data is updated daily. Format, header, columns, etc. never changes. Thanks for your help, Ruth |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If A1=dog or A2=cat in wrkbk1 sheet1, then copy row to wrkbk2 shee
To do what you're looking to do, you would have to use VBA code.
Your logic would be =IF(OR(A1="dog",A2="cat"),[copy row],[do nothing]) But as I say you can't do this if you want to stick to very basic functions/formulas. Dave -- Brevity is the soul of wit. "Ruth_J" wrote: Hi, Help pls... I'm only familiar with very basic functions/formulas in Excel 2003. I need to copy an entire row from one wrkbk/wrksht to another wrkbk/wrksht based on the criteria in two individual cells in that row. (If A1=dog or A2=cat in wrkbk1 sheet1, then copy entire row to wrkbk2 sheet1) The original worksheet data is updated daily. Format, header, columns, etc. never changes. Thanks for your help, Ruth |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If A1=dog or A2=cat in wrkbk1 sheet1, then copy row to wrkbk2
I am also needing help on this. I have a log that I keep of new hires. I have
to log all applicants. I need for the employee info to copy over to a different worksheet. example: if a cell has HIRE in it on tab applicant, I need that line to copy over to new hire. "Dave F" wrote: To do what you're looking to do, you would have to use VBA code. Your logic would be =IF(OR(A1="dog",A2="cat"),[copy row],[do nothing]) But as I say you can't do this if you want to stick to very basic functions/formulas. Dave -- Brevity is the soul of wit. "Ruth_J" wrote: Hi, Help pls... I'm only familiar with very basic functions/formulas in Excel 2003. I need to copy an entire row from one wrkbk/wrksht to another wrkbk/wrksht based on the criteria in two individual cells in that row. (If A1=dog or A2=cat in wrkbk1 sheet1, then copy entire row to wrkbk2 sheet1) The original worksheet data is updated daily. Format, header, columns, etc. never changes. Thanks for your help, Ruth |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If A1=dog or A2=cat in wrkbk1 sheet1, then copy row to wrkbk2
You could do this by using a filter and manually copying the filtered
rows across to another sheet. For example, filter the column for HIRE and only those records will be displayed. Then highlight the record(s) you want to copy to another sheet, click <copy, select the other sheet, move curso to the appropriate cell and <paste - only the visible row(s) selected will be copied into this sheet. In Ruth's case, she would have to apply a Custom filter on column A - equal to Dog OR equal to CAT, then copy/paste as described. Hope this helps. Pete twmmyv wrote: I am also needing help on this. I have a log that I keep of new hires. I have to log all applicants. I need for the employee info to copy over to a different worksheet. example: if a cell has HIRE in it on tab applicant, I need that line to copy over to new hire. "Dave F" wrote: To do what you're looking to do, you would have to use VBA code. Your logic would be =IF(OR(A1="dog",A2="cat"),[copy row],[do nothing]) But as I say you can't do this if you want to stick to very basic functions/formulas. Dave -- Brevity is the soul of wit. "Ruth_J" wrote: Hi, Help pls... I'm only familiar with very basic functions/formulas in Excel 2003. I need to copy an entire row from one wrkbk/wrksht to another wrkbk/wrksht based on the criteria in two individual cells in that row. (If A1=dog or A2=cat in wrkbk1 sheet1, then copy entire row to wrkbk2 sheet1) The original worksheet data is updated daily. Format, header, columns, etc. never changes. Thanks for your help, Ruth |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If A1=dog or A2=cat in wrkbk1 sheet1, then copy row to wrkbk2
Is ther any way for this to do it automatically?
"Pete_UK" wrote: You could do this by using a filter and manually copying the filtered rows across to another sheet. For example, filter the column for HIRE and only those records will be displayed. Then highlight the record(s) you want to copy to another sheet, click <copy, select the other sheet, move curso to the appropriate cell and <paste - only the visible row(s) selected will be copied into this sheet. In Ruth's case, she would have to apply a Custom filter on column A - equal to Dog OR equal to CAT, then copy/paste as described. Hope this helps. Pete twmmyv wrote: I am also needing help on this. I have a log that I keep of new hires. I have to log all applicants. I need for the employee info to copy over to a different worksheet. example: if a cell has HIRE in it on tab applicant, I need that line to copy over to new hire. "Dave F" wrote: To do what you're looking to do, you would have to use VBA code. Your logic would be =IF(OR(A1="dog",A2="cat"),[copy row],[do nothing]) But as I say you can't do this if you want to stick to very basic functions/formulas. Dave -- Brevity is the soul of wit. "Ruth_J" wrote: Hi, Help pls... I'm only familiar with very basic functions/formulas in Excel 2003. I need to copy an entire row from one wrkbk/wrksht to another wrkbk/wrksht based on the criteria in two individual cells in that row. (If A1=dog or A2=cat in wrkbk1 sheet1, then copy entire row to wrkbk2 sheet1) The original worksheet data is updated daily. Format, header, columns, etc. never changes. Thanks for your help, Ruth |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If A1=dog or A2=cat in wrkbk1 sheet1, then copy row to wrkbk2
Hi Pete,
How about a way to filter two columns at the same time so that I would only have to filter and copy/paste once? That way I would not have to filter A1=John, copy/paste to John's worksheet and then filter again for A2=John A then copy/paste to John's worksheet. The next filter would need to be filter A1=Pete copy/paste then filter A2=Pete R copy/paste to Pete's spreadsheet, etc. Many rows have A1=Lisa and A2=John or A1=John and A2=Susan so both rows would need to be on John's worksheet, etc. (I can do the copy/paste thing for each filter but I was just looking for a quicker way...chuckle) I have 5 people that need updated spreadsheets twice a week and asking them to filter their own is not an option-doggone it! (smile) The original spreadsheet I'm using is currently 20 columns by 3500 rows and growing by rows - columns are static. Thanks bunches, Ruth "Pete_UK" wrote: You could do this by using a filter and manually copying the filtered rows across to another sheet. For example, filter the column for HIRE and only those records will be displayed. Then highlight the record(s) you want to copy to another sheet, click <copy, select the other sheet, move curso to the appropriate cell and <paste - only the visible row(s) selected will be copied into this sheet. In Ruth's case, she would have to apply a Custom filter on column A - equal to Dog OR equal to CAT, then copy/paste as described. Hope this helps. Pete twmmyv wrote: I am also needing help on this. I have a log that I keep of new hires. I have to log all applicants. I need for the employee info to copy over to a different worksheet. example: if a cell has HIRE in it on tab applicant, I need that line to copy over to new hire. "Dave F" wrote: To do what you're looking to do, you would have to use VBA code. Your logic would be =IF(OR(A1="dog",A2="cat"),[copy row],[do nothing]) But as I say you can't do this if you want to stick to very basic functions/formulas. Dave -- Brevity is the soul of wit. "Ruth_J" wrote: Hi, Help pls... I'm only familiar with very basic functions/formulas in Excel 2003. I need to copy an entire row from one wrkbk/wrksht to another wrkbk/wrksht based on the criteria in two individual cells in that row. (If A1=dog or A2=cat in wrkbk1 sheet1, then copy entire row to wrkbk2 sheet1) The original worksheet data is updated daily. Format, header, columns, etc. never changes. Thanks for your help, Ruth |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If A1=dog or A2=cat in wrkbk1 sheet1, then copy row to wrkbk2
Pete,
Oppps!!!!!! Major typo!!!! I'm needing A1 & B1 not A1 & A2...duh!! Any place I have A2 actually should be B1... don't know where my mind was...sorry...long hectic week at the ofc!! Thanks again, Ruth "Pete_UK" wrote: You could do this by using a filter and manually copying the filtered rows across to another sheet. For example, filter the column for HIRE and only those records will be displayed. Then highlight the record(s) you want to copy to another sheet, click <copy, select the other sheet, move curso to the appropriate cell and <paste - only the visible row(s) selected will be copied into this sheet. In Ruth's case, she would have to apply a Custom filter on column A - equal to Dog OR equal to CAT, then copy/paste as described. Hope this helps. Pete twmmyv wrote: I am also needing help on this. I have a log that I keep of new hires. I have to log all applicants. I need for the employee info to copy over to a different worksheet. example: if a cell has HIRE in it on tab applicant, I need that line to copy over to new hire. "Dave F" wrote: To do what you're looking to do, you would have to use VBA code. Your logic would be =IF(OR(A1="dog",A2="cat"),[copy row],[do nothing]) But as I say you can't do this if you want to stick to very basic functions/formulas. Dave -- Brevity is the soul of wit. "Ruth_J" wrote: Hi, Help pls... I'm only familiar with very basic functions/formulas in Excel 2003. I need to copy an entire row from one wrkbk/wrksht to another wrkbk/wrksht based on the criteria in two individual cells in that row. (If A1=dog or A2=cat in wrkbk1 sheet1, then copy entire row to wrkbk2 sheet1) The original worksheet data is updated daily. Format, header, columns, etc. never changes. Thanks for your help, Ruth |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If A1=dog or A2=cat in wrkbk1 sheet1, then copy row to wrkbk2
Ruth,
I'm a bit confused from your description. If your filter applies to the whole range, then selecting "John" from the values shown in the pull-down filter list should display all the records which have John in column A, not just in A1, so you could copy/paste all of these to John's sheet. If you then select "Susan" from the filter, all her records would be visible, and so you can just copy her records to her sheet, and so on Instead of just selecting one name in the filter, however, you can select Custom ... and then you can have two criteria - the first one would be "Is Equal to" from the pull-down and "John" in the box, then click OR and for the second criteria again choose "Is Equal To" and then put "Susan" in the box. When you click OK you will see both John's and Susan's records on screen. You can also filter two columns at the same time - in column A select "John", then in column B select something else (might be a date). Now you will see only John's records for that particular date. I'm still not sure what you are getting at, having re-read your post several times, but I hope this helps. Pete Ruth_J wrote: Hi Pete, How about a way to filter two columns at the same time so that I would only have to filter and copy/paste once? That way I would not have to filter A1=John, copy/paste to John's worksheet and then filter again for A2=John A then copy/paste to John's worksheet. The next filter would need to be filter A1=Pete copy/paste then filter A2=Pete R copy/paste to Pete's spreadsheet, etc. Many rows have A1=Lisa and A2=John or A1=John and A2=Susan so both rows would need to be on John's worksheet, etc. (I can do the copy/paste thing for each filter but I was just looking for a quicker way...chuckle) I have 5 people that need updated spreadsheets twice a week and asking them to filter their own is not an option-doggone it! (smile) The original spreadsheet I'm using is currently 20 columns by 3500 rows and growing by rows - columns are static. Thanks bunches, Ruth "Pete_UK" wrote: You could do this by using a filter and manually copying the filtered rows across to another sheet. For example, filter the column for HIRE and only those records will be displayed. Then highlight the record(s) you want to copy to another sheet, click <copy, select the other sheet, move curso to the appropriate cell and <paste - only the visible row(s) selected will be copied into this sheet. In Ruth's case, she would have to apply a Custom filter on column A - equal to Dog OR equal to CAT, then copy/paste as described. Hope this helps. Pete twmmyv wrote: I am also needing help on this. I have a log that I keep of new hires. I have to log all applicants. I need for the employee info to copy over to a different worksheet. example: if a cell has HIRE in it on tab applicant, I need that line to copy over to new hire. "Dave F" wrote: To do what you're looking to do, you would have to use VBA code. Your logic would be =IF(OR(A1="dog",A2="cat"),[copy row],[do nothing]) But as I say you can't do this if you want to stick to very basic functions/formulas. Dave -- Brevity is the soul of wit. "Ruth_J" wrote: Hi, Help pls... I'm only familiar with very basic functions/formulas in Excel 2003. I need to copy an entire row from one wrkbk/wrksht to another wrkbk/wrksht based on the criteria in two individual cells in that row. (If A1=dog or A2=cat in wrkbk1 sheet1, then copy entire row to wrkbk2 sheet1) The original worksheet data is updated daily. Format, header, columns, etc. never changes. Thanks for your help, Ruth |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If A1=dog or A2=cat in wrkbk1 sheet1, then copy row to wrkbk2
Makes a bit more sense now. My third paragraph covers this, but if you
would like to give me the column headings in your sheet and explain what you want to do with the data in a bit more detail, then my comments might become less general. Pete Ruth_J wrote: Pete, Oppps!!!!!! Major typo!!!! I'm needing A1 & B1 not A1 & A2...duh!! Any place I have A2 actually should be B1... don't know where my mind was...sorry...long hectic week at the ofc!! Thanks again, Ruth "Pete_UK" wrote: You could do this by using a filter and manually copying the filtered rows across to another sheet. For example, filter the column for HIRE and only those records will be displayed. Then highlight the record(s) you want to copy to another sheet, click <copy, select the other sheet, move curso to the appropriate cell and <paste - only the visible row(s) selected will be copied into this sheet. In Ruth's case, she would have to apply a Custom filter on column A - equal to Dog OR equal to CAT, then copy/paste as described. Hope this helps. Pete twmmyv wrote: I am also needing help on this. I have a log that I keep of new hires. I have to log all applicants. I need for the employee info to copy over to a different worksheet. example: if a cell has HIRE in it on tab applicant, I need that line to copy over to new hire. "Dave F" wrote: To do what you're looking to do, you would have to use VBA code. Your logic would be =IF(OR(A1="dog",A2="cat"),[copy row],[do nothing]) But as I say you can't do this if you want to stick to very basic functions/formulas. Dave -- Brevity is the soul of wit. "Ruth_J" wrote: Hi, Help pls... I'm only familiar with very basic functions/formulas in Excel 2003. I need to copy an entire row from one wrkbk/wrksht to another wrkbk/wrksht based on the criteria in two individual cells in that row. (If A1=dog or A2=cat in wrkbk1 sheet1, then copy entire row to wrkbk2 sheet1) The original worksheet data is updated daily. Format, header, columns, etc. never changes. Thanks for your help, Ruth |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If A1=dog or A2=cat in wrkbk1 sheet1, then copy row to wrkbk2
Hi Pete,
I'm the clerical prepartory person for new work coming into an office and have 5 managers being assigned technical jobs (manager's id or blank if not assigned yet is in column J). They are to complete the job without regard to who originated it. If the job cannot be completed, then it goes back to the originator with notes on what is missing, incomplete, etc. (S column is the manager's id that originated the job). The rest of the fields in the spreadsheet are filled with project data. The manager who originated the job & the manager who was assigned the job to complete is seldom the same person. Rather than expecting the manager to access the spreadsheet on our network and sort their own, I'm required to hand out a paper spreadsheet twice a week to each manager showing his open jobs (column J) and his jobs that could not be completed (column S). I'm trying to avoid a lot of cut & paste or printing two spreadsheets for each person. We do not have the budget for formal training (I learn out of necessity...laugh) and cannot justify it as I rarely create spreadsheets. Most of my training comes from the Excel Help Menu & asking questions. (FYI-I extract the data daily (57 columns) from an incompatible database to a text file, import the text file into Bk1 Wrksht1, extract the columns I need via linking to Wrksht2, apply formulas to two new columns and then copy/paste the data & format only to Bk2 Wrksht1. Bk2 Wrksht1 is open to viewing & sorting by all.) Sorry if this is TMI but...you asked (chuckle)...thanks bunches, Ruth PS Is there any way to edit/correct my original question? I couldn't find any moderator contact info but maybe I was looking in the wrong place. :-) "Pete_UK" wrote: Makes a bit more sense now. My third paragraph covers this, but if you would like to give me the column headings in your sheet and explain what you want to do with the data in a bit more detail, then my comments might become less general. Pete Ruth_J wrote: Pete, Oppps!!!!!! Major typo!!!! I'm needing A1 & B1 not A1 & A2...duh!! Any place I have A2 actually should be B1... don't know where my mind was...sorry...long hectic week at the ofc!! Thanks again, Ruth "Pete_UK" wrote: You could do this by using a filter and manually copying the filtered rows across to another sheet. For example, filter the column for HIRE and only those records will be displayed. Then highlight the record(s) you want to copy to another sheet, click <copy, select the other sheet, move curso to the appropriate cell and <paste - only the visible row(s) selected will be copied into this sheet. In Ruth's case, she would have to apply a Custom filter on column A - equal to Dog OR equal to CAT, then copy/paste as described. Hope this helps. Pete twmmyv wrote: I am also needing help on this. I have a log that I keep of new hires. I have to log all applicants. I need for the employee info to copy over to a different worksheet. example: if a cell has HIRE in it on tab applicant, I need that line to copy over to new hire. "Dave F" wrote: To do what you're looking to do, you would have to use VBA code. Your logic would be =IF(OR(A1="dog",A2="cat"),[copy row],[do nothing]) But as I say you can't do this if you want to stick to very basic functions/formulas. Dave -- Brevity is the soul of wit. "Ruth_J" wrote: Hi, Help pls... I'm only familiar with very basic functions/formulas in Excel 2003. I need to copy an entire row from one wrkbk/wrksht to another wrkbk/wrksht based on the criteria in two individual cells in that row. (If A1=dog or A2=cat in wrkbk1 sheet1, then copy entire row to wrkbk2 sheet1) The original worksheet data is updated daily. Format, header, columns, etc. never changes. Thanks for your help, Ruth |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If A1=dog or A2=cat in wrkbk1 sheet1, then copy row to wrkbk2
Hi
You could use Advanced Filter to extract data to another sheet. For more help on how to do this, take a look at Debra Dalgleish's site http://www.contextures.com/xladvfilter01.html#ExtractWs -- Regards Roger Govier "twmmyv" wrote in message ... Is ther any way for this to do it automatically? "Pete_UK" wrote: You could do this by using a filter and manually copying the filtered rows across to another sheet. For example, filter the column for HIRE and only those records will be displayed. Then highlight the record(s) you want to copy to another sheet, click <copy, select the other sheet, move curso to the appropriate cell and <paste - only the visible row(s) selected will be copied into this sheet. In Ruth's case, she would have to apply a Custom filter on column A - equal to Dog OR equal to CAT, then copy/paste as described. Hope this helps. Pete twmmyv wrote: I am also needing help on this. I have a log that I keep of new hires. I have to log all applicants. I need for the employee info to copy over to a different worksheet. example: if a cell has HIRE in it on tab applicant, I need that line to copy over to new hire. "Dave F" wrote: To do what you're looking to do, you would have to use VBA code. Your logic would be =IF(OR(A1="dog",A2="cat"),[copy row],[do nothing]) But as I say you can't do this if you want to stick to very basic functions/formulas. Dave -- Brevity is the soul of wit. "Ruth_J" wrote: Hi, Help pls... I'm only familiar with very basic functions/formulas in Excel 2003. I need to copy an entire row from one wrkbk/wrksht to another wrkbk/wrksht based on the criteria in two individual cells in that row. (If A1=dog or A2=cat in wrkbk1 sheet1, then copy entire row to wrkbk2 sheet1) The original worksheet data is updated daily. Format, header, columns, etc. never changes. Thanks for your help, Ruth |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If A1=dog or A2=cat in wrkbk1 sheet1, then copy row to wrkbk2
Hi Ruth
If you are only wanting to provide a printed copy to each manager, then obviously you don't need to copy the data to another sheet. Filtering on column J and column S, then printing will provide your report. If you did want to extract to another sheet, then instead of Autofilter, you could use Advanced Filter. For more help on how to do this, take a look at Debra Dalgleish's site http://www.contextures.com/xladvfilter01.html#ExtractWs You could create 5 sheets, 1 for each manger with the necessary criteria set up, then refresh each sheet and print. PS Is there any way to edit/correct my original question? I couldn't find any moderator contact info but maybe I was looking in the wrong place. :-) No, not as far as I am aware. If I have made a mistake, I post a reply to my own message, with the correct and it shows up in the thread, so others watching the thread should pick up the amendment. -- Regards Roger Govier "Ruth_J" wrote in message ... Hi Pete, I'm the clerical prepartory person for new work coming into an office and have 5 managers being assigned technical jobs (manager's id or blank if not assigned yet is in column J). They are to complete the job without regard to who originated it. If the job cannot be completed, then it goes back to the originator with notes on what is missing, incomplete, etc. (S column is the manager's id that originated the job). The rest of the fields in the spreadsheet are filled with project data. The manager who originated the job & the manager who was assigned the job to complete is seldom the same person. Rather than expecting the manager to access the spreadsheet on our network and sort their own, I'm required to hand out a paper spreadsheet twice a week to each manager showing his open jobs (column J) and his jobs that could not be completed (column S). I'm trying to avoid a lot of cut & paste or printing two spreadsheets for each person. We do not have the budget for formal training (I learn out of necessity...laugh) and cannot justify it as I rarely create spreadsheets. Most of my training comes from the Excel Help Menu & asking questions. (FYI-I extract the data daily (57 columns) from an incompatible database to a text file, import the text file into Bk1 Wrksht1, extract the columns I need via linking to Wrksht2, apply formulas to two new columns and then copy/paste the data & format only to Bk2 Wrksht1. Bk2 Wrksht1 is open to viewing & sorting by all.) Sorry if this is TMI but...you asked (chuckle)...thanks bunches, Ruth PS Is there any way to edit/correct my original question? I couldn't find any moderator contact info but maybe I was looking in the wrong place. :-) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
reminder notifications in a column | Excel Discussion (Misc queries) | |||
Copy values from Sheet1 to Sheet2 | Excel Discussion (Misc queries) | |||
How do I copy page setup from one worksheet & paste into new shee. | Excel Discussion (Misc queries) |