Specific keywords copying data to cells

L

Leo Edwards

Hi there,

I'm not sure whether this is possible, but is there a way in which typing in
a specific word would result in information being copied from one cell into
another?

In simpler form: 'Keyword' typed into cell A (after verification) causes
data to duplicate from cell B into cell C.

Sorry to ask such a simple question, but any help would be greatly
appreciated!

Thanks

Leo
 
K

Kevin Vaughn

The most straightforward way of doing what you ask is with a formula in
column c. For instance, you could enter this formula into C1 and copy it
down:
=IF(A1="Keyword", B1,"")
Then, if in the corresponding row in column A you type Keyword, the cell in
column C will display what is in column B's cell. If not, it will display
nothing (which is signified by "")
 
L

leomanic

Thanks for the help Kevin, works brilliantly!

Sorry to be a pain, but there's also another thing along the same lines
that I forgot to mention?

Is there a way in which I could do this with 2 keywords? i.e. Keyword1
puts figure X into a cell or if Keyword2 entered, puts in figure Y?

Thanks

Leo
 
K

Kevin Vaughn

I'm not sure if this is what you mean, but perhaps this is what you want:

=IF(A1="foo",B1,IF(A1="bar",C1,""))

This says that if (assuming you entered this in column D and copied down)
column a (for that row) contains the word foo, return what is in column B
(for that row.) if not, then if A = "bar" then return column C otherwise
return nothing. This is called nested IFs and the current versions of Excel
are limited to 7 levels of nested IFs. The most common work around if you
need more tests than that is to use a lookup function such as vlookup or
index/match.

Is this what you were asking?
 
L

leomanic

Hi Kevin,

That is excellent thanks! Just what I was looking for!

Top marks all round for you!

Leo
 
L

leomanic

Sorry Kevin!

You are going to love me!

Is there a way in which you can make Excel work out what you're doing
when you copy formula down rows?

For example, I currently have this as my first formula:

=IF(B9="Truckstop1",J3,IF(B9="Truckstop2",J4,""))

Which basically works out which truckstop name I've typed in & then
copies across the relevant haulage rate.

Unfortunately, when I do the drag/copy thing, it does the usual Excel
pre-guessing you:

=IF(B9="Truckstop1",J3,IF(B9="Truckstop2",J4,""))
=IF(B10="Truckstop1",J4,IF(B10="Truckstop2",J5,""))
=IF(B11="Truckstop1",J5,IF(B11="Truckstop2",J6,""))

Basically, I want the above B column cells to increase by 1 each time,
but I still need it to refer to cells J3 or J4 each time, rather than
increasing accordingly.

Is this possible? Sorry to be a pain!

Thanks

Leo Edwards
 
K

Kevin Vaughn

Sorry Kevin!

You are going to love me!

Is there a way in which you can make Excel work out what you're doing
when you copy formula down rows?

For example, I currently have this as my first formula:

=IF(B9="Truckstop1",J3,IF(B9="Truckstop2",J4,""))

Which basically works out which truckstop name I've typed in & then
copies across the relevant haulage rate.

Unfortunately, when I do the drag/copy thing, it does the usual Excel
pre-guessing you:

=IF(B9="Truckstop1",J3,IF(B9="Truckstop2",J4,""))
=IF(B10="Truckstop1",J4,IF(B10="Truckstop2",J5,""))
=IF(B11="Truckstop1",J5,IF(B11="Truckstop2",J6,""))

Basically, I want the above B column cells to increase by 1 each time,
but I still need it to refer to cells J3 or J4 each time, rather than
increasing accordingly.

Is this possible? Sorry to be a pain!



HTH
Thanks

Leo Edwards
Click to expand...
Sure, you just need to anchor those cells . You do this by using the $
in the appropriate places to make that part of the formula absolute (as
opposed to relative.) So, for the first one, you would write:
=IF(B9="Truckstop1",$J$3,IF(B9="Truckstop2",$J$4,""))
And if you wanted the row to stay the same, but the column to be
relative, you would omit the $ before the J.
 
Top