Auto Transfer of a filtered item to a different sheet.

Solved/Closed
Mymyname
Posts
5
Registration date
Saturday October 18, 2014
Status
Member
Last seen
October 30, 2014
- Oct 18, 2014 at 01:36 PM
Mymyname
Posts
5
Registration date
Saturday October 18, 2014
Status
Member
Last seen
October 30, 2014
- Oct 23, 2014 at 06:57 PM
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

venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
810
Oct 19, 2014 at 12:45 AM
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
Mymyname
Posts
5
Registration date
Saturday October 18, 2014
Status
Member
Last seen
October 30, 2014

Oct 19, 2014 at 03:56 AM
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.
0
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
810
Oct 20, 2014 at 02:40 AM
copy your data in the message box and again explai8n
0
Mymyname
Posts
5
Registration date
Saturday October 18, 2014
Status
Member
Last seen
October 30, 2014

Oct 20, 2014 at 11:21 AM
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
0
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
810
Oct 21, 2014 at 02:13 AM
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
0

Didn't find the answer you are looking for?

Ask a question
Mymyname
Posts
5
Registration date
Saturday October 18, 2014
Status
Member
Last seen
October 30, 2014

Oct 23, 2014 at 06:57 PM
thank you very much. This has solve the problem.
0