Previous Topic Next Topic

Auto Transfer of a filtered item to a different sheet.

Solved/Closed
Mymyname Posts 4 Registration date Saturday 18 October 2014 Status Member Last seen 30 October 2014 - 18 Oct 2014 à 13:36
Mymyname Posts 4 Registration date Saturday 18 October 2014 Status Member Last seen 30 October 2014 - 23 Oct 2014 à 18:57
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 responses

Réponse 1 / 5
venkat1926 Posts 1863 Registration date Sunday 14 June 2009 Status Contributor Last seen 7 August 2021 811
19 Oct 2014 à 00:45
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 18 October 2014 Status Member Last seen 30 October 2014
19 Oct 2014 à 03:56
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.
Réponse 2 / 5
venkat1926 Posts 1863 Registration date Sunday 14 June 2009 Status Contributor Last seen 7 August 2021 811
20 Oct 2014 à 02:40
copy your data in the message box and again explai8n
Réponse 3 / 5
Mymyname Posts 4 Registration date Saturday 18 October 2014 Status Member Last seen 30 October 2014
20 Oct 2014 à 11:21
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
Réponse 4 / 5
venkat1926 Posts 1863 Registration date Sunday 14 June 2009 Status Contributor Last seen 7 August 2021 811
21 Oct 2014 à 02:13
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
Réponse 5 / 5
Mymyname Posts 4 Registration date Saturday 18 October 2014 Status Member Last seen 30 October 2014
23 Oct 2014 à 18:57
thank you very much. This has solve the problem.

Similar discussions

Any auto clicker for Xbox One?
vic11con -
julietdcosta -

13 responses