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
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
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
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
"leomanic@hotmail.com" 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
>
>
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
>
>
> "leomanic@hotmail.com" 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
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
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:Originally Posted by leomanic@hotmail.com
=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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks