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

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 Advantage@discussions.microsoft.com> wrote in message
news:1D31CCA9-1821-4922-9D1C-735ED2005829@microsoft.com...
>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

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

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$12>A2)*(Primary!$B$1:$B$12=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