Help needed with Excel 'IF' forumlas

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

maracles

Thread Starter
Joined
Apr 4, 2006
Messages
298
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

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,471
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

Thread Starter
Joined
Apr 4, 2006
Messages
298
thanks for the speedy response. I will try this now.

Good choice with the dpforums!
 

maracles

Thread Starter
Joined
Apr 4, 2006
Messages
298
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

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,471
There are a few ways to do that

you can use a MID function

=IF(MID(Sheet2!A2,1,3)="abc",Sheet2!B2,"")
 
Joined
Jul 25, 2004
Messages
5,458
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. ;)
 
Joined
Apr 7, 2007
Messages
439
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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top