Copy values into other cells

[newbie2002]

Hi,

I have a spreadsheet and i must copy a value into other cells, how can i realise this. It seems like this:

col a | col b | col c
Mary
12-4-02
12:20
15:34
15:36
Check
Peter
13-4-02
16:32
17:43
18:56
Check

I must copy the dates and names in column a and b till the word "check" and then i must take the other date and copy and so on...It must be like this:

col a | col b | col c
Mary
12-4-02
Mary 12-4-02 12:20
Mary 12-4-02 15:34
Mary 12-4-02 15:36
Check
Peter
13-4-02
Mary 13-4-02 16:32
Mary 13-4-02 17:43
Mary 13-4-02 18:56
Check

I hope somebody can help i'm just a newbie in VBA..

 

[newbie2002]

sorry have done some mistakes

col a | col b | col c
Mary
12-4-02
Mary 12-4-02 12:20
Mary 12-4-02 15:34
Mary 12-4-02 15:36
Check
Peter
13-4-02
Peter 13-4-02 16:32
Peter 13-4-02 17:43
Peter 13-4-02 18:56
Check

 

[larryww]

Consider adding a column D that contains (for e.g. cell D3)

if(C3="Check",1,if(C2="Check",2,if(C1="Check",3,0))).
Propagate down, and deal with cell D2 and D1 as needed. Then depending on whether a row is "category 1" (etc.) vs. plain old "category 0", use if statements to craft your output. These are the basics; can you run with that approach?

Or use an Access report

 

[SkipVought]

Hi,

I am assuming that your source data is in Column A.

In Column B

=IF(ISNUMBER($A2),B1,$A2)

In ColumnC

=IF(ISNUMBER($A2),IF($A2>=1,$A2,C1),"")

In ColumnD

=IF(ISNUMBER($A2),IF($A2>=1,"",$A2),"")

So your three columns end up in B-D.

Hope this helps Skip,

 

[SkipVought]

BTW, here's my results...

-A----- -B----- -C------- -D----
Mary    Mary		
12/4/02 Mary    12/04/02	
12:20   Mary    12/04/02   12:20
15:34   Mary    12/04/02   15:34
15:36   Mary    12/04/02   15:36
Check   Check		
Peter   Peter		
12/4/02 Peter   12/04/02	
16:32   Peter   12/04/02   16:32
17:43   Peter   12/04/02   17:43
18:56   Peter   12/04/02   18:56
Check   Check

Skip,

 

[newbie2002]

Thanks for your response. Is it possible to do this Only in column a and b how i had describe it above. I have a huge list of this, thats the reason why i don't want to work with excel formulas.

-a---- -b------ -c--------
Mary
12-4-02
Mary 12-4-02 12:20
Mary 12-4-02 15:34
Mary 12-4-02 15:36
Check
Peter
13-4-02
Peter 13-4-02 16:32
Peter 13-4-02 17:43
Peter 13-4-02 18:56
Check

Is it possible to get this result from this list:


-a---- -b------ -c--------
Mary
12-4-02
12:20
15:34
15:36
Check
Peter
13-4-02
16:32
17:43
18:56


Check

 

[SkipVought]

What column is your data in? Skip,

 

[newbie2002]

Columnn c. In column a the names must copied till the "check". In column b the the dates must copied also till the check. Hope u can help ;-)

 

[newbie2002]

Could anybody help me pls to solve this...

 

[SkipVought]

Hey, Newbie,

What you are asking for is fundamentally impossible!!!

If your souce data is in column C, you CANNOT overwrite that source data with a formula.

Lets get a good requirement! Skip,

 

[SkipVought]

sorry, I read your post again and now I understand...

You will need to add a Column D and hid that column

Col A

=IF(ISNUMBER(C2),IF(C2>0,"",D2),"")

Col B

=IF(ISNUMBER($C2),IF($C2>=1,$C2,B1),"")

Col D

=IF(ISNUMBER($C2),D1,$C2)

Enter all these formulae into ROW 2.
In D1

=C1

Hope this helps

Skip,

 

[SkipVought]

oops,

You will also need a Col E AND I revised my formulae as follows:

Col A

=IF(ISNUMBER(C2),IF(C2<1,D2,&quot;&quot;),&quot;&quot;)

Col B

=IF(ISNUMBER(C2),IF(C2<1,E2,&quot;&quot;),&quot;&quot;)

Col D Row 1

=C1

Col D Row 2 and following

=IF(ISNUMBER($C2),D1,$C2)

Col E

=IF(ISNUMBER($C2),IF($C2>=1,$C2,E1),"")

Skip,

 

[larryww]

Newbie, Skip put a lot - a lot - of time and effort into this. Yet it's hard to tell whether your Excel proficiency rose or if you're just dumbfounded, because instead of specific feedback on his suggestions, I only saw 2 things - desire not to use Excel formulae, and would someone finish this project for you.

If you want to become skillful and productive with it, people can give you ideas - e.g. experimenting with commands, getting to glide through F1 Help, testing endpoints, QUE texts, etc. On the other hand, if you truly don't want to learn Excel, and just want to use the group to solve your deal, okay too. Either way, Skip for one would surely enjoy feedback, after his extensive work on this.

And if he broke through to the answer, give him a star