Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Formula to copy rng of cells where (value is met) to anther sheet
I have one sheet that contains info for all three office locations. One
column on this sheet contains location ID. I want to create three seperate sheets for each office, then have a formula that will copy the info from a row on the primary sheet to respective office sheet. Office locations are 28, 44 and 68. So if column c on main sheet =28 then copy all rows where column c = 28 to 28 sheet, etc with 44 and 68. Please help, I can't figure it out. Thanks, Brian |
#2
|
|||
|
|||
Formula to copy rng of cells where (value is met) to anther sheet
Hi!
If you have more than 2 or 3 columns of data then your best course of action would be to use a filter. Apply an autofilter and filter on location ID. Then it's just a simple copy/paste. This could be done using formulas which makes it dynamic but as I stated above, if you have a lot of columns and rows it may not be the most efficient way to go. Biff "Exit Advantage" <Exit wrote in message ... I have one sheet that contains info for all three office locations. One column on this sheet contains location ID. I want to create three seperate sheets for each office, then have a formula that will copy the info from a row on the primary sheet to respective office sheet. Office locations are 28, 44 and 68. So if column c on main sheet =28 then copy all rows where column c = 28 to 28 sheet, etc with 44 and 68. Please help, I can't figure it out. Thanks, Brian |
#3
|
|||
|
|||
Formula to copy rng of cells where (value is met) to anther sheet
If this is a one-time effort, you could
(1) autofilter on the location column for each office in turn. Then copy the visible rows and paste special values into the associated sheet. or (2) if location column is A, in A2 on the office sheet (assuming you have headers in row 1), enter =IF(Sheet1!$A2=28,sheet1!A2,"") and copy to as many columns and rows as are in sheet1. Do the same for the other sheets but substitute 44 and 68 for the 28. This will leave you with a lot of blank rows in each sheet. You can bring all your completed rows to the top by sorting. If, on the other hand, this is something you need to do repeatedly, please let us know, and we may devise a repeatable approach. As an aside, will you ever need to analyze all the data across all offices, and, if you spread it across three sheets, will that be more difficult than if it were on one sheet. Note that you can display the rows for one office at a time on your primary sheet using Autofilter, thereby making it virtually the equivalent of the three sheet approach, and yet maintaining the ability to analyze all the data across offices more easily. If you haven't use Autofilter before, look it up in the Help. To use it, highlight the header row of your primary sheet. Then click Data/Filter/Autofilter. This will put drop-down arrows across your header row. Click on the one in the Location column and then click 28. You should now see only the rows that pertain to offfice 28. HTH Declan |
#4
|
|||
|
|||
Formula to copy rng of cells where (value is met) to anther sheet
Brian,
I have here a rudimentary solution. It can be tweaked, but for a quick solution this will work. Put a helper column in column A of your "Primary" sheet, using the row() function, to create a sequential list. Your data should now look something like this, with the headings in row 1. COL A COL B COL C Row ID DATA 2 44 1 3 44 2 4 68 3 5 68 4 6 28 5 7 28 5 8 28 5 9 28 5 10 28 5 11 28 5 12 28 6 Now on another sheet, using the sheet name "Loc28", put the heading Row in cell A1. In cell A2 type this formula =MIN(IF((Primary!$A$1:$A$12A2)*(Primary!$B$1:$B$1 2=28),Primary!$A$1:$A$12)). Use <ctrl <shift <enter to make it an array formula. You should see { } brackets around the formula. (Use a longer range that to A12 and B12 to match your data.) Cell A2 will ready as zero. Copy the formula down a few cells. You'll now see data as below, representing rows from the "Primary" sheet. Row 0 6 7 8 9 10 11 12 0 When it gets to zero, here after the 12, you can erase that row and those below it, for it will repeat. Now use the VLOOKUP function to lookup data based on the Row values. Then repeat, using sheets for your other two locations, 44 and 68. Tweaking the array formula to test for text values can eliminate the leading zero and another tweak can eliminate the repeats, turning them into 99999's or some such, but that would complicate the array formula too much for this discussion. Do that later. Exit Advantage" wrote: I have one sheet that contains info for all three office locations. One column on this sheet contains location ID. I want to create three seperate sheets for each office, then have a formula that will copy the info from a row on the primary sheet to respective office sheet. Office locations are 28, 44 and 68. So if column c on main sheet =28 then copy all rows where column c = 28 to 28 sheet, etc with 44 and 68. Please help, I can't figure it out. Thanks, Brian |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy values generated by conditional formula in one sheet to the other work sheet as values | Excel Worksheet Functions | |||
copy values generated by conditional formula in one sheet to the other work sheet as values | Excel Discussion (Misc queries) | |||
Edit / Move or copy sheet stopped working in Excel 2003 | Excel Worksheet Functions | |||
Formula works in some cells, doesn't in other | New Users to Excel | |||
Howdo U copy a formula down a column, that uses data in another w. | Excel Worksheet Functions |