I am trying to link data from one worksheet to another using the IF formula.
The first worksheet (roster) includes student information, status (active or
inactive) and assessment units completed (designated as Y or N for each
unit). The subsequent worsheets include the data for each unit.

With the help of Max--I used the following formula in the second worksheet
to return a value only if the student was active and had completed the unit:

=IF(Roster!$A9="I","",IF(AND(Roster!$A9="A",Roster !$H9="Y"),Roster!B9,""))

However, if a student is inactive (I) --a blank cell is returned. Is there
any way for the values of only the active students (A) to appear on the
worksheet sequentialy (without the blank cells)?

Your help is greatly appreciated!

Assume your source data table is in Roster, cols A to H,
where data starts from row 8 down to row 100 (say)

In your other sheet,
Put in say, J8:
=IF(Roster!$A8="I","",IF(AND(Roster!$A8="A",Roster !$H8="Y"),ROWS($1:1),""))
Leave J1:J7 empty. This is the extraction criteria col, which is spun-off
your earlier thread.

Put in K8:
=IF(ROWS($1:1)COUNT($J$8:$J$100),"",INDEX(Roster! A$8:A$100,SMALL($J$8:$J$100,ROWS($1:1))))
Copy K8 across to R8. Select J8:R8, copy down. Minimize/hide col J. Cols K
to R will return the required lines from Roster which satisfy the criteria,
with all lines neatly packed at the top.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Wilma" wrote:
I am trying to link data from one worksheet to another using the IF formula.
The first worksheet (roster) includes student information, status (active or
inactive) and assessment units completed (designated as Y or N for each
unit). The subsequent worsheets include the data for each unit.

With the help of Max--I used the following formula in the second worksheet
to return a value only if the student was active and had completed the unit:

=IF(Roster!$A9="I","",IF(AND(Roster!$A9="A",Roster !$H9="Y"),Roster!B9,""))

However, if a student is inactive (I) --a blank cell is returned. Is there
any way for the values of only the active students (A) to appear on the
worksheet sequentialy (without the blank cells)?

Your help is greatly appreciated!

I guess perhaps I was not clear in my question--or perhaps it is not possible
to do what I am hoping to accomplish. If a student is inactive (I) --i am
getting a blank cell (or row). What I am trying to achieve is for the data
of only the active students (A) to appear in the second worksheet.

The formula below gave me the values (list of active students) but in a
different column than where I wanted it to appear.

I hope this is clear--please let me know if more info is needed.

Thank you so much for your help! I very much appreciate it.


"Max" wrote:

Assume your source data table is in Roster, cols A to H,
where data starts from row 8 down to row 100 (say)

In your other sheet,
Put in say, J8:
=IF(Roster!$A8="I","",IF(AND(Roster!$A8="A",Roster !$H8="Y"),ROWS($1:1),""))
Leave J1:J7 empty. This is the extraction criteria col, which is spun-off
your earlier thread.

Put in K8:
=IF(ROWS($1:1)COUNT($J$8:$J$100),"",INDEX(Roster! A$8:A$100,SMALL($J$8:$J$100,ROWS($1:1))))
Copy K8 across to R8. Select J8:R8, copy down. Minimize/hide col J. Cols K
to R will return the required lines from Roster which satisfy the criteria,
with all lines neatly packed at the top.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Wilma" wrote:
I am trying to link data from one worksheet to another using the IF formula.
The first worksheet (roster) includes student information, status (active or
inactive) and assessment units completed (designated as Y or N for each
unit). The subsequent worsheets include the data for each unit.

With the help of Max--I used the following formula in the second worksheet
to return a value only if the student was active and had completed the unit:

=IF(Roster!$A9="I","",IF(AND(Roster!$A9="A",Roster !$H9="Y"),Roster!B9,""))

However, if a student is inactive (I) --a blank cell is returned. Is there
any way for the values of only the active students (A) to appear on the
worksheet sequentialy (without the blank cells)?

Your help is greatly appreciated!

I got it working! YEAH!

I realized after I wrote my last post that all I had to do was move the
formula to the column I wanted and it worked beautifully.

Max--you are super wonderful--thank you so much for your help!

"Wilma" wrote:

I guess perhaps I was not clear in my question--or perhaps it is not possible
to do what I am hoping to accomplish. If a student is inactive (I) --i am
getting a blank cell (or row). What I am trying to achieve is for the data
of only the active students (A) to appear in the second worksheet.

The formula below gave me the values (list of active students) but in a
different column than where I wanted it to appear.

I hope this is clear--please let me know if more info is needed.

Thank you so much for your help! I very much appreciate it.


"Max" wrote:

Assume your source data table is in Roster, cols A to H,
where data starts from row 8 down to row 100 (say)

In your other sheet,
Put in say, J8:
=IF(Roster!$A8="I","",IF(AND(Roster!$A8="A",Roster !$H8="Y"),ROWS($1:1),""))
Leave J1:J7 empty. This is the extraction criteria col, which is spun-off
your earlier thread.

Put in K8:
=IF(ROWS($1:1)COUNT($J$8:$J$100),"",INDEX(Roster! A$8:A$100,SMALL($J$8:$J$100,ROWS($1:1))))
Copy K8 across to R8. Select J8:R8, copy down. Minimize/hide col J. Cols K
to R will return the required lines from Roster which satisfy the criteria,
with all lines neatly packed at the top.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Wilma" wrote:
I am trying to link data from one worksheet to another using the IF formula.
The first worksheet (roster) includes student information, status (active or
inactive) and assessment units completed (designated as Y or N for each
unit). The subsequent worsheets include the data for each unit.

With the help of Max--I used the following formula in the second worksheet
to return a value only if the student was active and had completed the unit:

=IF(Roster!$A9="I","",IF(AND(Roster!$A9="A",Roster !$H9="Y"),Roster!B9,""))

However, if a student is inactive (I) --a blank cell is returned. Is there
any way for the values of only the active students (A) to appear on the
worksheet sequentialy (without the blank cells)?

Your help is greatly appreciated!