E

#### Eva

col A - blank or "new"

col B - name

col c - number

I need to create in sheet2 report that takes only "new" data from column A

col B - name

col c - number

I thought about the offset function - any ideas?

You are using an out of date browser. It may not display this or other websites correctly.

You should upgrade or use an alternative browser.

You should upgrade or use an alternative browser.

E

col A - blank or "new"

col B - name

col c - number

I need to create in sheet2 report that takes only "new" data from column A

col B - name

col c - number

I thought about the offset function - any ideas?

Ad

J

right ColB/C and then copy the formula down as required. Please note that

this is an array formula. An array formula can perform multiple calculations

and then return either a single result or multiple results.You create array

formulas in the same way that you create other formulas, except you press

CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can

notice the curly braces at both ends like "{=<formula>}"

=IF(ROW(A1)<=COUNTIF(Sheet1!$A:$A,"?*"),INDEX(Sheet1!A:A,SMALL(IF(Sheet1!$A$1:$A$100<>"",ROW($A$1:$A$100)),ROW(A1))),"")

If this post helps click Yes

E

it is so brilliant!

Thank you very much

Thank you very much

M

Using Indirect

=INDIRECT("Sheet1!A1")

=INDIRECT("Sheet1!B1")

=INDIRECT("Sheet1!C1")

Using Offset

=OFFSET(Sheet1!A1,0,0)

=OFFSET(Sheet1!A1,0,1)

=OFFSET(Sheet1!A1,0,2)

If this post helps, Click Yes!

T

Assume the data on Sheet1 is in the range A2:C20

Enter this array formula** on Sheet2 in cell A2:

=IF(ROWS(A$2:A2)>COUNTIF(Sheet1!$A:$A,"new"),"",INDEX(Sheet1!B:B,SMALL(IF(Sheet1!$A$2:$A$20="new",ROW(Sheet1!A$2:A$20)),ROWS(A$2:A2))))

** array formulas need to be entered using the key combination of

CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT

key then hit ENTER.

Copy across to B2 then down until you get blanks meaning all relative data

has been extracted.

E

the headings - so I would like to start my formula in sheet2 from A2 Sheet1.

I was trying to change the formula myself but it didn't work. Can you have a

look at this once again? Thank you very much

Ad

E

perfectly, so thank you for your time

A

Try this

1. Say your data is in range B510 (Sheet1)

2. In B44, type headings - Status, Name and Number

3. In B12, type Status

4. In B13, type New

5. In Sheet2, type Name and number in B4:C4

6. Click on cell B6 of sheet2

7. Go to Data > Filter > Advanced Filter

8. In Action, select "Copy to another location"

9. In the list box, select B410 of sheet1;

10. In criteria, select b12:B13 of sheet1

11. In the copy to box, select B4:C4 of sheet2

12. Click on Finish

Hope this helps

--

Regards,

Ashish Mathur

Microsoft Excel MVP

www.ashishmathur.com

J

across/down as required

=IF(ROW(A1)<=COUNTIF(Sheet1!$A:$A,"new"),INDEX(Sheet1!A:A,SMALL(IF(Sheet1!$A$1:$A$100="new",ROW($A$1:$A$100)),ROW(A1))),"")

If this post helps click Yes

E

Thank you Ashish, but I prefer the formula. I know about this option

E

I tried already to change the formula with "new", but it doesn't work, but

it realy is not a big problem, so I will leave it as it is.

Thank you for your help

Ad

E

--

Greatly appreciated

Eva

T. Valko said:Try this...

Assume the data on Sheet1 is in the range A2:C20

Enter this array formula** on Sheet2 in cell A2:

=IF(ROWS(A$2:A2)>COUNTIF(Sheet1!$A:$A,"new"),"",INDEX(Sheet1!B:B,SMALL(IF(Sheet1!$A$2:$A$20="new",ROW(Sheet1!A$2:A$20)),ROWS(A$2:A2))))

** array formulas need to be entered using the key combination of

CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT

key then hit ENTER.

Copy across to B2 then down until you get blanks meaning all relative data

has been extracted.

--

Biff

Microsoft Excel MVP

.

Ad

T

You're welcome. Thanks for the feedback!

**Want to reply to this thread or ask your own question?**

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.