Auto Transfer of a filtered item to a different sheet.

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



Related:

5 responses

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
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
Mymyname Posts 4 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 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Oct 20, 2014 at 02:40 AM
copy your data in the message box and again explai8n
Mymyname Posts 4 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
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
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

Didn't find the answer you are looking for?

Ask a question
Mymyname Posts 4 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.