ExcelBanter - Specific keywords copying data to cells

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Specific keywords copying data to cells (https://www.excelbanter.com/excel-worksheet-functions/90576-specific-keywords-copying-data-cells.html)

Specific keywords copying data to cells

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

Specific keywords copying data to cells

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 "")

--
Kevin Vaughn

"Leo Edwards" wrote:

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

Specific keywords copying data to cells

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

Kevin Vaughn wrote:
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 "")

--
Kevin Vaughn

"Leo Edwards" wrote:

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

Specific keywords copying data to cells

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?
--
Kevin Vaughn

" wrote:

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

Kevin Vaughn wrote:
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 "")

--
Kevin Vaughn

"Leo Edwards" wrote:

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

Specific keywords copying data to cells

Hi Kevin,

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

Top marks all round for you!

Leo

Kevin Vaughn wrote:
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?
--
Kevin Vaughn

" wrote:

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

Kevin Vaughn wrote:
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 "")

--
Kevin Vaughn

"Leo Edwards" wrote:

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

Specific keywords copying data to cells

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

Specific keywords copying data to cells