Help needed with Excel 'IF' forumlas

[maracles]

I need a way of automatically copying the same data from one worksheet to a second worksheet without typing it out again. The only problem is this data should only be copied when a value is entered in a certain cell.

I have an excel layout whereby on my first worksheet my 'A' column is initially empty, the 'B' and 'C' column is filled with data.

I'm looking for a formula which, when I enter any text in column 'A' on a certain row, the contents of Columns B and C on that row are automatically input on a second worksheet in a certain place.

i.e.

If (A1 = text/number/value) then ('worksheet2' B1 and C1 = 'worksheet1' B1 and C1.


Anyone got any ideas?

 

[etaf]

do you want it copied to the same place

in the sheet you want the data copied to put this in cell B2
=IF(Sheet2!A2<>"",Sheet2!B2,"")
then in Cell C2 put
=IF(Sheet2!A2<>"",Sheet2!C2,"")

Now if sheet2 A2 has anything entered it will reflect cells B and C in the sheet

 

[maracles]

thanks for the speedy response. I will try this now.

Good choice with the dpforums!

 

[maracles]

Thats great, it worked.

One other thing is it possible to make it slightly more complex in that the value that will decide whether or not data is copied (in your forumla A2) changes everytime and is made of two parts;

A2 = abc123

The letters always remain the same, whereas the numbers increase incrementally.

Therefore it may begin with abc123, but the next value will be abc124. Can this be incorporated so that excel recognises only values that begin abc. So in effect it is IF(A2= abcxxx,Sheet2!B2,"")

 

[etaf]

There are a few ways to do that

you can use a MID function

=IF(MID(Sheet2!A2,1,3)="abc",Sheet2!B2,"")

 

[Zack Barresse]

maracles,

I have a hankering this is more than meets the eye. Can you give us the full scope of what you're trying to do here? The process sounds clunky, and there may be a better way to achieve what you want. Please post as much detail as possible. The more [data] you post the less we ask.

 

[MRdNk]

I have to add the question, how many records are you likely to be dealing with? Excel can only handle a certain amount of data, before its time to move your data to a database.