Auto Transfer of a filtered item to a different sheet. [Solved/Closed]

Report
Posts
5
Registration date
Saturday October 18, 2014
Status
Member
Last seen
October 30, 2014
-
Posts
5
Registration date
Saturday October 18, 2014
Status
Member
Last seen
October 30, 2014
-
Hello everyone,

I would like to ask for some help regarding some EXEL problems.

Example: I want some data from sheet1 E columns to transfer to sheet 2 E columns if the A column contains a number "1". If it doesn't the command will ignore the row and move on.

I tried something like [Sheet 2 C50 cell ] =IF(OR('sheet1'!A50=1),'sheet1'!E50)
It works but for the rows that doesn't contain the number 1 shows the word FALSE. Is it possible for the exel function to just ignore the columns without the number one and just copy the names from the rows with the number 1 on the A column?

I'm making this for a daily attendance report thing.

I'm using Microsoft Office 2007

I apologize for any poor grammar.
Please and thank you.



5 replies

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
the data in sheet1 is like this

hdng1 hdng2 hdng3 hdng4 hdng5
1 x x x 1
2 x x x 2
3 x x x 3
3 x x x 4
2 x x x 5
1 x x x 6
1 x x x 7
2 x x x 8
3 x x x 9


now in sheet2 E2 type this formula

=IF(COUNTIF(Sheet1!$A$1:$A$1000,1)>=ROWS($1:1),INDEX(Sheet1!$E$1:$E$1000,SMALL(IF(Sheet1!$A$1:$A$1000=1,ROW($1:$1000)),ROW(1:1))),"")

hit control shift enter

copy E2 and paste to E2 down for a few cells.

see what you get
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
5
Registration date
Saturday October 18, 2014
Status
Member
Last seen
October 30, 2014

Holy!!!!!! You got it!!! Thank you very much!!!!!!

And in addition can it also be done to prepare multiple of this. Ummm...sample.

from sheet1 E column to K column will transfer to Sheet 2 E to K column with the same condition of column A has number 1 on it.

Also, is it possible copying/transfering date information using this formula?
Sheet 1 Column J is in date format and I want to auto transfer/copy it also to sheet 2

soo my data sheet looks like this.

A B C D E F G J K L

1 blank blank blank name Nationality name of class ID number Date Days present


Sorry for the trouble.
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
copy your data in the message box and again explai8n
Posts
5
Registration date
Saturday October 18, 2014
Status
Member
Last seen
October 30, 2014

The formula that you gave me worked perfectly but it doesn't work on the "Date" kind of data.

If the data written in the cell is 10/21/2014, it doesn't copy the information but displays a different number

sample
if Sheet1 A7 has date info on it = 10/21/2014 Sheet2 E7 has 44476 something.
Anyway to fix this?

Thank you
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
quote
if Sheet1 A7 has date info on it = 10/21/2014 Sheet2 E7 has 44476 something.
unquote

44476 is how the date is stored in the computer. FORMAT THIS in any way you like preferably date-month-yyyy
Posts
5
Registration date
Saturday October 18, 2014
Status
Member
Last seen
October 30, 2014

thank you very much. This has solve the problem.