A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

creating a formul



 
 
Thread Tools Display Modes
  #21  
Old March 2nd, 2005, 04:45 PM
George A. Yorks
external usenet poster
 
Posts: n/a
Default

I understand that excel is not seeing a match. But if I do the following the
file is recognized. I copy the file(containing a column of names and column
of numbers) and past to excel.I then copy from excel to lotus 123 and then
from lotus 123 back to excel. Is there any reason for this and is there any
other, less cumbersome, proceedure???

"George A. Yorks" wrote:

Ihave tried to copy data from USA Today report. When I past to excel
worksheet all cells are obliterated. Does this account for the data not
being recognized. If so is there anything that can be done to make this data
useable??

Thanks for all the help and information

"Myrna Larson" wrote:

Well, Excel isn't seeing a match. For one of the formulas that you expect to
return a value, what is in column B, and what is the matching data in column C
or your table on Sheet3? Could the problem be that you have numbers in one
place and text that looks like a number (but is stored as text) in the other?
If so, they won't match, e.g. 1 doesn't match "1"


On Mon, 21 Feb 2005 13:55:04 -0800, George A. Yorks
.(donotspam) wrote:

I've used the following formula =vlookup(b4,sheet3!$c$1:$m$31,11,0) The
correct data is transfered to the first cell in the column all other cells
receive #N/A a value is not available to the formula or function. There is
however data to be transfered.Any help appreciated

"George A. Yorks" wrote:

Thanks for the help. One further question.
In the formul In sheet 2 $A$1:$b$10makes reference to two adjacent columns.
If there is a column between so there is data in a and c and none in b the
result return err,the formula in this cell referes to cells that are
currently emply. How to get around this. ie" if sheet two has data in

column
a and column d.

thanks

George Yorks

"Earl Kiosterud" wrote:

George,

In B1 of sheet 1:
=VLOOKUP(A1, 'Sheet2'!$A$1:$B$10, 2, FALSE)

Copy down with fill handle to B10.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"George A. Yorks" .(donotspam) wrote in message
...
Trying to create a formula to do the following:
Sheet 1 column A a list of personal names a1-a10

Sheet 2 has list of names a1-a10 and list of dollar amounts colums

d1-d10

want to search sheet one and if any name from sheet 2 found on sheet 1
than
the corresponding dollar amount is entered.

Any help appreciated.
--
George





  #22  
Old March 3rd, 2005, 06:51 AM
George A. Yorks
external usenet poster
 
Posts: n/a
Default

Thanks for the help. I am confronted with a new scenario which I'll present
for help.

One worksheet titled geo I have a list of names b4 thru b18 and data in
k4thru k18. I want to transfere the data to worksheet titled miriam after
searching for the exact names. The names of the second worksheet are column
ax3 thru ax89. Hope this makes sense to you.

George

"RagDyer" wrote:

First question:

Replace error message with a null ( "" ), which can then be added:

=IF(ISNA(MATCH(A1,'Sheet2'!$A$1:$A$10,0)),"",VLOOK UP(A1,
'Sheet2'!$A$1:$D$10, 4, 0)
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"George A. Yorks" .(donotspam) wrote in message
news I keep saying thanks, your help is outstanding. I would like to ask two
additional questions. ie: When I enter my formula into the cells of column
A
in a number of cells it returns a value in a few it returns#N/A. There is
no data in those cases but with the #n?A when I try to add the columns will
not do so as it cant enter a non digit. I'm using 0 for the last number in
my formula. How best to get around this.

Also I've copied a table from USA internet. In pasting to worksheet all the
cells are obliterated and nothing is recognized by excel. The data is
however recognized in (pardon me) lotus. Is there anyway of having my
vlookup formula search 123 in place of a sheet in excel. =vlookup(a1,
sheet2!$A$1:$j$31, 10,0). Hope these questions make sense to someone

"Earl Kiosterud" wrote:

George,

The 4 is the third parameter of the VLOOKUP function, and tells it to
retrieve the cell in the 4th column of the range being looked up in. Did

I
say being looked up in? Oh, well.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"George A. Yorks" .(donotspam) wrote in message
...
Thanks for the help. I know I'll fully understand the formula structure
soon. One question, at end of formula ,4,0 what in fact does the 4 make
reference to?

"Ragdyer" wrote:

Try this:

=VLOOKUP(A1, 'Sheet2'!$A$1:$D$10, 4, 0)

--
HTH,

RD


-------------------------------------------------------------------------

--
Please keep all correspondence within the NewsGroup, so all may benefit

!

-------------------------------------------------------------------------

--

"George A. Yorks" .(donotspam) wrote in message
...
Thanks for the help. One further question.
In the formul In sheet 2 $A$1:$b$10makes reference to two adjacent
columns.
If there is a column between so there is data in a and c and none in

b
the
result return err,the formula in this cell referes to cells that are
currently emply. How to get around this. ie" if sheet two has data

in
column
a and column d.

thanks

George Yorks

"Earl Kiosterud" wrote:

George,

In B1 of sheet 1:
=VLOOKUP(A1, 'Sheet2'!$A$1:$B$10, 2, FALSE)

Copy down with fill handle to B10.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"George A. Yorks" .(donotspam) wrote in message
...
Trying to create a formula to do the following:
Sheet 1 column A a list of personal names a1-a10

Sheet 2 has list of names a1-a10 and list of dollar amounts

colums
d1-d10

want to search sheet one and if any name from sheet 2 found on
sheet 1
than
the corresponding dollar amount is entered.

Any help appreciated.
--
George










  #23  
Old March 3rd, 2005, 05:05 PM
RagDyeR
external usenet poster
 
Posts: n/a
Default

Try this in Ay3 of "Miriam":

=IF(ISNA(MATCH(AX3,Geo!$B$4:$B$18,0)),"Not
Found",INDEX(Geo!$K$4:$K$18,MATCH(AX3,Geo!$B$4:$B$ 18,0)))

You can then *double click* on the "fill handle" in the lower right corner
of AY3, which will *automatically* copy the formula in AY3 down Column AY,
as far as there is data in Column AX.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"George A. Yorks" .(donotspam) wrote in message
...
Thanks for the help. I am confronted with a new scenario which I'll present
for help.

One worksheet titled geo I have a list of names b4 thru b18 and data in
k4thru k18. I want to transfere the data to worksheet titled miriam after
searching for the exact names. The names of the second worksheet are column
ax3 thru ax89. Hope this makes sense to you.

George

"RagDyer" wrote:

First question:

Replace error message with a null ( "" ), which can then be added:

=IF(ISNA(MATCH(A1,'Sheet2'!$A$1:$A$10,0)),"",VLOOK UP(A1,
'Sheet2'!$A$1:$D$10, 4, 0)
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"George A. Yorks" .(donotspam) wrote in message
news I keep saying thanks, your help is outstanding. I would like to ask two
additional questions. ie: When I enter my formula into the cells of

column
A
in a number of cells it returns a value in a few it returns#N/A. There

is
no data in those cases but with the #n?A when I try to add the columns

will
not do so as it cant enter a non digit. I'm using 0 for the last number

in
my formula. How best to get around this.

Also I've copied a table from USA internet. In pasting to worksheet all

the
cells are obliterated and nothing is recognized by excel. The data is
however recognized in (pardon me) lotus. Is there anyway of having my
vlookup formula search 123 in place of a sheet in excel. =vlookup(a1,
sheet2!$A$1:$j$31, 10,0). Hope these questions make sense to someone

"Earl Kiosterud" wrote:

George,

The 4 is the third parameter of the VLOOKUP function, and tells it to
retrieve the cell in the 4th column of the range being looked up in.

Did
I
say being looked up in? Oh, well.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"George A. Yorks" .(donotspam) wrote in message
...
Thanks for the help. I know I'll fully understand the formula

structure
soon. One question, at end of formula ,4,0 what in fact does the 4

make
reference to?

"Ragdyer" wrote:

Try this:

=VLOOKUP(A1, 'Sheet2'!$A$1:$D$10, 4, 0)

--
HTH,

RD



-------------------------------------------------------------------------

--
Please keep all correspondence within the NewsGroup, so all may

benefit
!


-------------------------------------------------------------------------

--

"George A. Yorks" .(donotspam) wrote in message
...
Thanks for the help. One further question.
In the formul In sheet 2 $A$1:$b$10makes reference to two adjacent
columns.
If there is a column between so there is data in a and c and none

in
b
the
result return err,the formula in this cell referes to cells that

are
currently emply. How to get around this. ie" if sheet two has data

in
column
a and column d.

thanks

George Yorks

"Earl Kiosterud" wrote:

George,

In B1 of sheet 1:
=VLOOKUP(A1, 'Sheet2'!$A$1:$B$10, 2, FALSE)

Copy down with fill handle to B10.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"George A. Yorks" .(donotspam) wrote in

message
...
Trying to create a formula to do the following:
Sheet 1 column A a list of personal names a1-a10

Sheet 2 has list of names a1-a10 and list of dollar amounts

colums
d1-d10

want to search sheet one and if any name from sheet 2 found on
sheet 1
than
the corresponding dollar amount is entered.

Any help appreciated.
--
George












  #24  
Old March 3rd, 2005, 07:09 PM
George A. Yorks
external usenet poster
 
Posts: n/a
Default

I tried this formula and get a "not found" also this is how the formula gets
entered. Don't know why. It was entered as below.
=IF(ISNA(MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18,0)),"not
found",INDEX([GEO.xls]Sheet2!$Q$4:$Q$18,MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18,0)))
"RagDyeR" wrote:

Try this in Ay3 of "Miriam":

=IF(ISNA(MATCH(AX3,Geo!$B$4:$B$18,0)),"Not
Found",INDEX(Geo!$K$4:$K$18,MATCH(AX3,Geo!$B$4:$B$ 18,0)))

You can then *double click* on the "fill handle" in the lower right corner
of AY3, which will *automatically* copy the formula in AY3 down Column AY,
as far as there is data in Column AX.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"George A. Yorks" .(donotspam) wrote in message
...
Thanks for the help. I am confronted with a new scenario which I'll present
for help.

One worksheet titled geo I have a list of names b4 thru b18 and data in
k4thru k18. I want to transfere the data to worksheet titled miriam after
searching for the exact names. The names of the second worksheet are column
ax3 thru ax89. Hope this makes sense to you.

George

"RagDyer" wrote:

First question:

Replace error message with a null ( "" ), which can then be added:

=IF(ISNA(MATCH(A1,'Sheet2'!$A$1:$A$10,0)),"",VLOOK UP(A1,
'Sheet2'!$A$1:$D$10, 4, 0)
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"George A. Yorks" .(donotspam) wrote in message
news I keep saying thanks, your help is outstanding. I would like to ask two
additional questions. ie: When I enter my formula into the cells of

column
A
in a number of cells it returns a value in a few it returns#N/A. There

is
no data in those cases but with the #n?A when I try to add the columns

will
not do so as it cant enter a non digit. I'm using 0 for the last number

in
my formula. How best to get around this.

Also I've copied a table from USA internet. In pasting to worksheet all

the
cells are obliterated and nothing is recognized by excel. The data is
however recognized in (pardon me) lotus. Is there anyway of having my
vlookup formula search 123 in place of a sheet in excel. =vlookup(a1,
sheet2!$A$1:$j$31, 10,0). Hope these questions make sense to someone

"Earl Kiosterud" wrote:

George,

The 4 is the third parameter of the VLOOKUP function, and tells it to
retrieve the cell in the 4th column of the range being looked up in.

Did
I
say being looked up in? Oh, well.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"George A. Yorks" .(donotspam) wrote in message
...
Thanks for the help. I know I'll fully understand the formula

structure
soon. One question, at end of formula ,4,0 what in fact does the 4

make
reference to?

"Ragdyer" wrote:

Try this:

=VLOOKUP(A1, 'Sheet2'!$A$1:$D$10, 4, 0)

--
HTH,

RD



-------------------------------------------------------------------------

--
Please keep all correspondence within the NewsGroup, so all may

benefit
!


-------------------------------------------------------------------------

--

"George A. Yorks" .(donotspam) wrote in message
...
Thanks for the help. One further question.
In the formul In sheet 2 $A$1:$b$10makes reference to two adjacent
columns.
If there is a column between so there is data in a and c and none

in
b
the
result return err,the formula in this cell referes to cells that

are
currently emply. How to get around this. ie" if sheet two has data

in
column
a and column d.

thanks

George Yorks

"Earl Kiosterud" wrote:

George,

In B1 of sheet 1:
=VLOOKUP(A1, 'Sheet2'!$A$1:$B$10, 2, FALSE)

Copy down with fill handle to B10.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"George A. Yorks" .(donotspam) wrote in

message
...
Trying to create a formula to do the following:
Sheet 1 column A a list of personal names a1-a10

Sheet 2 has list of names a1-a10 and list of dollar amounts

colums
d1-d10

want to search sheet one and if any name from sheet 2 found on
sheet 1
than
the corresponding dollar amount is entered.

Any help appreciated.
--
George













  #25  
Old March 4th, 2005, 04:18 AM
RagDyer
external usenet poster
 
Posts: n/a
Default

I would guess that perhaps the data that's in AX3 doesn't *exactly* match
what's in Column B.

Do you import any of your data?
Are the names "full" names, first, and/or middle and last names, where there
might be a possibilty that the spaces between them might not be a normal
Char(32) space?
Could there be a possibility of leading and/or trailing spaces?

For a test, key a name into Column B.
Enter that same name in *exactly* the same way into AX3, and see if you get
a correct return from your formula.
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

"George A. Yorks" .(donotspam) wrote in message
...
I tried this formula and get a "not found" also this is how the formula gets
entered. Don't know why. It was entered as below.
=IF(ISNA(MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18,0)),"not
found",INDEX([GEO.xls]Sheet2!$Q$4:$Q$18,MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18
,0)))
"RagDyeR" wrote:

Try this in Ay3 of "Miriam":

=IF(ISNA(MATCH(AX3,Geo!$B$4:$B$18,0)),"Not
Found",INDEX(Geo!$K$4:$K$18,MATCH(AX3,Geo!$B$4:$B$ 18,0)))

You can then *double click* on the "fill handle" in the lower right corner
of AY3, which will *automatically* copy the formula in AY3 down Column AY,
as far as there is data in Column AX.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"George A. Yorks" .(donotspam) wrote in message
...
Thanks for the help. I am confronted with a new scenario which I'll

present
for help.

One worksheet titled geo I have a list of names b4 thru b18 and data in
k4thru k18. I want to transfere the data to worksheet titled miriam after
searching for the exact names. The names of the second worksheet are

column
ax3 thru ax89. Hope this makes sense to you.

George

"RagDyer" wrote:

First question:

Replace error message with a null ( "" ), which can then be added:

=IF(ISNA(MATCH(A1,'Sheet2'!$A$1:$A$10,0)),"",VLOOK UP(A1,
'Sheet2'!$A$1:$D$10, 4, 0)
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"George A. Yorks" .(donotspam) wrote in message
news I keep saying thanks, your help is outstanding. I would like to ask two
additional questions. ie: When I enter my formula into the cells of

column
A
in a number of cells it returns a value in a few it returns#N/A. There

is
no data in those cases but with the #n?A when I try to add the columns

will
not do so as it cant enter a non digit. I'm using 0 for the last number

in
my formula. How best to get around this.

Also I've copied a table from USA internet. In pasting to worksheet all

the
cells are obliterated and nothing is recognized by excel. The data is
however recognized in (pardon me) lotus. Is there anyway of having my
vlookup formula search 123 in place of a sheet in excel. =vlookup(a1,
sheet2!$A$1:$j$31, 10,0). Hope these questions make sense to someone

"Earl Kiosterud" wrote:

George,

The 4 is the third parameter of the VLOOKUP function, and tells it to
retrieve the cell in the 4th column of the range being looked up in.

Did
I
say being looked up in? Oh, well.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"George A. Yorks" .(donotspam) wrote in message
...
Thanks for the help. I know I'll fully understand the formula

structure
soon. One question, at end of formula ,4,0 what in fact does the 4

make
reference to?

"Ragdyer" wrote:

Try this:

=VLOOKUP(A1, 'Sheet2'!$A$1:$D$10, 4, 0)

--
HTH,

RD




-------------------------------------------------------------------------
--
Please keep all correspondence within the NewsGroup, so all may

benefit
!



-------------------------------------------------------------------------
--

"George A. Yorks" .(donotspam) wrote in message
...
Thanks for the help. One further question.
In the formul In sheet 2 $A$1:$b$10makes reference to two

adjacent
columns.
If there is a column between so there is data in a and c and none

in
b
the
result return err,the formula in this cell referes to cells that

are
currently emply. How to get around this. ie" if sheet two has

data
in
column
a and column d.

thanks

George Yorks

"Earl Kiosterud" wrote:

George,

In B1 of sheet 1:
=VLOOKUP(A1, 'Sheet2'!$A$1:$B$10, 2, FALSE)

Copy down with fill handle to B10.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"George A. Yorks" .(donotspam) wrote in

message
...
Trying to create a formula to do the following:
Sheet 1 column A a list of personal names a1-a10

Sheet 2 has list of names a1-a10 and list of dollar amounts

colums
d1-d10

want to search sheet one and if any name from sheet 2 found

on
sheet 1
than
the corresponding dollar amount is entered.

Any help appreciated.
--
George














  #26  
Old March 8th, 2005, 05:17 AM
George A. Yorks
external usenet poster
 
Posts: n/a
Default

In using the lookup formula I find that the data in column AX3 is not exactly
like that in sheet 2 B4. ie: the spacing between first and last name is not
the same. Any way to correct this. I only know of the differences when the
dollar amounts are not tranfered. I then have to correct these errors
manually. Hope this makes sense

Thanks for all the help

"George A. Yorks" wrote:

I tried this formula and get a "not found" also this is how the formula gets
entered. Don't know why. It was entered as below.
=IF(ISNA(MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18,0)),"not
found",INDEX([GEO.xls]Sheet2!$Q$4:$Q$18,MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18,0)))
"RagDyeR" wrote:

Try this in Ay3 of "Miriam":

=IF(ISNA(MATCH(AX3,Geo!$B$4:$B$18,0)),"Not
Found",INDEX(Geo!$K$4:$K$18,MATCH(AX3,Geo!$B$4:$B$ 18,0)))

You can then *double click* on the "fill handle" in the lower right corner
of AY3, which will *automatically* copy the formula in AY3 down Column AY,
as far as there is data in Column AX.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"George A. Yorks" .(donotspam) wrote in message
...
Thanks for the help. I am confronted with a new scenario which I'll present
for help.

One worksheet titled geo I have a list of names b4 thru b18 and data in
k4thru k18. I want to transfere the data to worksheet titled miriam after
searching for the exact names. The names of the second worksheet are column
ax3 thru ax89. Hope this makes sense to you.

George

"RagDyer" wrote:

First question:

Replace error message with a null ( "" ), which can then be added:

=IF(ISNA(MATCH(A1,'Sheet2'!$A$1:$A$10,0)),"",VLOOK UP(A1,
'Sheet2'!$A$1:$D$10, 4, 0)
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"George A. Yorks" .(donotspam) wrote in message
news I keep saying thanks, your help is outstanding. I would like to ask two
additional questions. ie: When I enter my formula into the cells of

column
A
in a number of cells it returns a value in a few it returns#N/A. There

is
no data in those cases but with the #n?A when I try to add the columns

will
not do so as it cant enter a non digit. I'm using 0 for the last number

in
my formula. How best to get around this.

Also I've copied a table from USA internet. In pasting to worksheet all

the
cells are obliterated and nothing is recognized by excel. The data is
however recognized in (pardon me) lotus. Is there anyway of having my
vlookup formula search 123 in place of a sheet in excel. =vlookup(a1,
sheet2!$A$1:$j$31, 10,0). Hope these questions make sense to someone

"Earl Kiosterud" wrote:

George,

The 4 is the third parameter of the VLOOKUP function, and tells it to
retrieve the cell in the 4th column of the range being looked up in.

Did
I
say being looked up in? Oh, well.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"George A. Yorks" .(donotspam) wrote in message
...
Thanks for the help. I know I'll fully understand the formula

structure
soon. One question, at end of formula ,4,0 what in fact does the 4

make
reference to?

"Ragdyer" wrote:

Try this:

=VLOOKUP(A1, 'Sheet2'!$A$1:$D$10, 4, 0)

--
HTH,

RD



-------------------------------------------------------------------------
--
Please keep all correspondence within the NewsGroup, so all may

benefit
!


-------------------------------------------------------------------------
--

"George A. Yorks" .(donotspam) wrote in message
...
Thanks for the help. One further question.
In the formul In sheet 2 $A$1:$b$10makes reference to two adjacent
columns.
If there is a column between so there is data in a and c and none

in
b
the
result return err,the formula in this cell referes to cells that

are
currently emply. How to get around this. ie" if sheet two has data
in
column
a and column d.

thanks

George Yorks

"Earl Kiosterud" wrote:

George,

In B1 of sheet 1:
=VLOOKUP(A1, 'Sheet2'!$A$1:$B$10, 2, FALSE)

Copy down with fill handle to B10.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"George A. Yorks" .(donotspam) wrote in

message
...
Trying to create a formula to do the following:
Sheet 1 column A a list of personal names a1-a10

Sheet 2 has list of names a1-a10 and list of dollar amounts
colums
d1-d10

want to search sheet one and if any name from sheet 2 found on
sheet 1
than
the corresponding dollar amount is entered.

Any help appreciated.
--
George













  #27  
Old March 8th, 2005, 05:42 AM
Myrna Larson
external usenet poster
 
Posts: n/a
Default

If you sometimes have double or triple spaces, you can do a search and replace
on column AX: search for two spaces and replace with 1 space. Repeat until
Excel tell you there are no more matches.

On Mon, 7 Mar 2005 20:17:02 -0800, George A. Yorks
.(donotspam) wrote:

In using the lookup formula I find that the data in column AX3 is not exactly
like that in sheet 2 B4. ie: the spacing between first and last name is not
the same. Any way to correct this. I only know of the differences when the
dollar amounts are not tranfered. I then have to correct these errors
manually. Hope this makes sense

Thanks for all the help

"George A. Yorks" wrote:

I tried this formula and get a "not found" also this is how the formula

gets
entered. Don't know why. It was entered as below.
=IF(ISNA(MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18,0)),"not

found",INDEX([GEO.xls]Sheet2!$Q$4:$Q$18,MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18,0)))
"RagDyeR" wrote:

Try this in Ay3 of "Miriam":

=IF(ISNA(MATCH(AX3,Geo!$B$4:$B$18,0)),"Not
Found",INDEX(Geo!$K$4:$K$18,MATCH(AX3,Geo!$B$4:$B$ 18,0)))

You can then *double click* on the "fill handle" in the lower right

corner
of AY3, which will *automatically* copy the formula in AY3 down Column

AY,
as far as there is data in Column AX.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"George A. Yorks" .(donotspam) wrote in message
...
Thanks for the help. I am confronted with a new scenario which I'll

present
for help.

One worksheet titled geo I have a list of names b4 thru b18 and data in
k4thru k18. I want to transfere the data to worksheet titled miriam

after
searching for the exact names. The names of the second worksheet are

column
ax3 thru ax89. Hope this makes sense to you.

George

"RagDyer" wrote:

First question:

Replace error message with a null ( "" ), which can then be added:

=IF(ISNA(MATCH(A1,'Sheet2'!$A$1:$A$10,0)),"",VLOOK UP(A1,
'Sheet2'!$A$1:$D$10, 4, 0)
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"George A. Yorks" .(donotspam) wrote in message
news I keep saying thanks, your help is outstanding. I would like to ask

two
additional questions. ie: When I enter my formula into the cells of
column
A
in a number of cells it returns a value in a few it returns#N/A.

There
is
no data in those cases but with the #n?A when I try to add the columns
will
not do so as it cant enter a non digit. I'm using 0 for the last

number
in
my formula. How best to get around this.

Also I've copied a table from USA internet. In pasting to worksheet

all
the
cells are obliterated and nothing is recognized by excel. The data is
however recognized in (pardon me) lotus. Is there anyway of having my
vlookup formula search 123 in place of a sheet in excel. =vlookup(a1,
sheet2!$A$1:$j$31, 10,0). Hope these questions make sense to someone

"Earl Kiosterud" wrote:

George,

The 4 is the third parameter of the VLOOKUP function, and tells it to
retrieve the cell in the 4th column of the range being looked up in.
Did
I
say being looked up in? Oh, well.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"George A. Yorks" .(donotspam) wrote in message
...
Thanks for the help. I know I'll fully understand the formula
structure
soon. One question, at end of formula ,4,0 what in fact does the 4
make
reference to?

"Ragdyer" wrote:

Try this:

=VLOOKUP(A1, 'Sheet2'!$A$1:$D$10, 4, 0)

--
HTH,

RD




-------------------------------------------------------------------------
--
Please keep all correspondence within the NewsGroup, so all may
benefit
!



-------------------------------------------------------------------------
--

"George A. Yorks" .(donotspam) wrote in

message
...
Thanks for the help. One further question.
In the formul In sheet 2 $A$1:$b$10makes reference to two

adjacent
columns.
If there is a column between so there is data in a and c and

none
in
b
the
result return err,the formula in this cell referes to cells that
are
currently emply. How to get around this. ie" if sheet two has

data
in
column
a and column d.

thanks

George Yorks

"Earl Kiosterud" wrote:

George,

In B1 of sheet 1:
=VLOOKUP(A1, 'Sheet2'!$A$1:$B$10, 2, FALSE)

Copy down with fill handle to B10.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"George A. Yorks" .(donotspam) wrote in
message
...
Trying to create a formula to do the following:
Sheet 1 column A a list of personal names a1-a10

Sheet 2 has list of names a1-a10 and list of dollar amounts
colums
d1-d10

want to search sheet one and if any name from sheet 2 found

on
sheet 1
than
the corresponding dollar amount is entered.

Any help appreciated.
--
George














  #28  
Old March 16th, 2005, 09:17 PM
George A. Yorks
external usenet poster
 
Posts: n/a
Default

Have looked through help menu for how to perform a search and replace you
speak of. I can't find anything. Could you give me some direction. Thanks
for all your help

"Myrna Larson" wrote:

If you sometimes have double or triple spaces, you can do a search and replace
on column AX: search for two spaces and replace with 1 space. Repeat until
Excel tell you there are no more matches.

On Mon, 7 Mar 2005 20:17:02 -0800, George A. Yorks
.(donotspam) wrote:

In using the lookup formula I find that the data in column AX3 is not exactly
like that in sheet 2 B4. ie: the spacing between first and last name is not
the same. Any way to correct this. I only know of the differences when the
dollar amounts are not tranfered. I then have to correct these errors
manually. Hope this makes sense

Thanks for all the help

"George A. Yorks" wrote:

I tried this formula and get a "not found" also this is how the formula

gets
entered. Don't know why. It was entered as below.
=IF(ISNA(MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18,0)),"not

found",INDEX([GEO.xls]Sheet2!$Q$4:$Q$18,MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18,0)))
"RagDyeR" wrote:

Try this in Ay3 of "Miriam":

=IF(ISNA(MATCH(AX3,Geo!$B$4:$B$18,0)),"Not
Found",INDEX(Geo!$K$4:$K$18,MATCH(AX3,Geo!$B$4:$B$ 18,0)))

You can then *double click* on the "fill handle" in the lower right

corner
of AY3, which will *automatically* copy the formula in AY3 down Column

AY,
as far as there is data in Column AX.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"George A. Yorks" .(donotspam) wrote in message
...
Thanks for the help. I am confronted with a new scenario which I'll

present
for help.

One worksheet titled geo I have a list of names b4 thru b18 and data in
k4thru k18. I want to transfere the data to worksheet titled miriam

after
searching for the exact names. The names of the second worksheet are

column
ax3 thru ax89. Hope this makes sense to you.

George

"RagDyer" wrote:

First question:

Replace error message with a null ( "" ), which can then be added:

=IF(ISNA(MATCH(A1,'Sheet2'!$A$1:$A$10,0)),"",VLOOK UP(A1,
'Sheet2'!$A$1:$D$10, 4, 0)
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"George A. Yorks" .(donotspam) wrote in message
news I keep saying thanks, your help is outstanding. I would like to ask

two
additional questions. ie: When I enter my formula into the cells of
column
A
in a number of cells it returns a value in a few it returns#N/A.

There
is
no data in those cases but with the #n?A when I try to add the columns
will
not do so as it cant enter a non digit. I'm using 0 for the last

number
in
my formula. How best to get around this.

Also I've copied a table from USA internet. In pasting to worksheet

all
the
cells are obliterated and nothing is recognized by excel. The data is
however recognized in (pardon me) lotus. Is there anyway of having my
vlookup formula search 123 in place of a sheet in excel. =vlookup(a1,
sheet2!$A$1:$j$31, 10,0). Hope these questions make sense to someone

"Earl Kiosterud" wrote:

George,

The 4 is the third parameter of the VLOOKUP function, and tells it to
retrieve the cell in the 4th column of the range being looked up in.
Did
I
say being looked up in? Oh, well.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"George A. Yorks" .(donotspam) wrote in message
...
Thanks for the help. I know I'll fully understand the formula
structure
soon. One question, at end of formula ,4,0 what in fact does the 4
make
reference to?

"Ragdyer" wrote:

Try this:

=VLOOKUP(A1, 'Sheet2'!$A$1:$D$10, 4, 0)

--
HTH,

RD




-------------------------------------------------------------------------
--
Please keep all correspondence within the NewsGroup, so all may
benefit
!



-------------------------------------------------------------------------
--

"George A. Yorks" .(donotspam) wrote in

message
...
Thanks for the help. One further question.
In the formul In sheet 2 $A$1:$b$10makes reference to two

adjacent
columns.
If there is a column between so there is data in a and c and

none
in
b
the
result return err,the formula in this cell referes to cells that
are
currently emply. How to get around this. ie" if sheet two has

data
in
column
a and column d.

thanks

George Yorks

"Earl Kiosterud" wrote:

George,

In B1 of sheet 1:
=VLOOKUP(A1, 'Sheet2'!$A$1:$B$10, 2, FALSE)

Copy down with fill handle to B10.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"George A. Yorks" .(donotspam) wrote in
message
...
Trying to create a formula to do the following:
Sheet 1 column A a list of personal names a1-a10

Sheet 2 has list of names a1-a10 and list of dollar amounts
colums
d1-d10

want to search sheet one and if any name from sheet 2 found

on
sheet 1
than
the corresponding dollar amount is entered.

Any help appreciated.
--
George















  #29  
Old March 16th, 2005, 10:26 PM
Dave Peterson
external usenet poster
 
Posts: n/a
Default

Look under Edit|Replace on the worksheet toolbar.

George A. Yorks wrote:

Have looked through help menu for how to perform a search and replace you
speak of. I can't find anything. Could you give me some direction. Thanks
for all your help

"Myrna Larson" wrote:

If you sometimes have double or triple spaces, you can do a search and replace
on column AX: search for two spaces and replace with 1 space. Repeat until
Excel tell you there are no more matches.

On Mon, 7 Mar 2005 20:17:02 -0800, George A. Yorks
.(donotspam) wrote:

In using the lookup formula I find that the data in column AX3 is not exactly
like that in sheet 2 B4. ie: the spacing between first and last name is not
the same. Any way to correct this. I only know of the differences when the
dollar amounts are not tranfered. I then have to correct these errors
manually. Hope this makes sense

Thanks for all the help

"George A. Yorks" wrote:

I tried this formula and get a "not found" also this is how the formula

gets
entered. Don't know why. It was entered as below.
=IF(ISNA(MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18,0)),"not

found",INDEX([GEO.xls]Sheet2!$Q$4:$Q$18,MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18,0)))
"RagDyeR" wrote:

Try this in Ay3 of "Miriam":

=IF(ISNA(MATCH(AX3,Geo!$B$4:$B$18,0)),"Not
Found",INDEX(Geo!$K$4:$K$18,MATCH(AX3,Geo!$B$4:$B$ 18,0)))

You can then *double click* on the "fill handle" in the lower right

corner
of AY3, which will *automatically* copy the formula in AY3 down Column

AY,
as far as there is data in Column AX.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"George A. Yorks" .(donotspam) wrote in message
...
Thanks for the help. I am confronted with a new scenario which I'll

present
for help.

One worksheet titled geo I have a list of names b4 thru b18 and data in
k4thru k18. I want to transfere the data to worksheet titled miriam

after
searching for the exact names. The names of the second worksheet are

column
ax3 thru ax89. Hope this makes sense to you.

George

"RagDyer" wrote:

First question:

Replace error message with a null ( "" ), which can then be added:

=IF(ISNA(MATCH(A1,'Sheet2'!$A$1:$A$10,0)),"",VLOOK UP(A1,
'Sheet2'!$A$1:$D$10, 4, 0)
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"George A. Yorks" .(donotspam) wrote in message
news I keep saying thanks, your help is outstanding. I would like to ask

two
additional questions. ie: When I enter my formula into the cells of
column
A
in a number of cells it returns a value in a few it returns#N/A.

There
is
no data in those cases but with the #n?A when I try to add the columns
will
not do so as it cant enter a non digit. I'm using 0 for the last

number
in
my formula. How best to get around this.

Also I've copied a table from USA internet. In pasting to worksheet

all
the
cells are obliterated and nothing is recognized by excel. The data is
however recognized in (pardon me) lotus. Is there anyway of having my
vlookup formula search 123 in place of a sheet in excel. =vlookup(a1,
sheet2!$A$1:$j$31, 10,0). Hope these questions make sense to someone

"Earl Kiosterud" wrote:

George,

The 4 is the third parameter of the VLOOKUP function, and tells it to
retrieve the cell in the 4th column of the range being looked up in.
Did
I
say being looked up in? Oh, well.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"George A. Yorks" .(donotspam) wrote in message
...
Thanks for the help. I know I'll fully understand the formula
structure
soon. One question, at end of formula ,4,0 what in fact does the 4
make
reference to?

"Ragdyer" wrote:

Try this:

=VLOOKUP(A1, 'Sheet2'!$A$1:$D$10, 4, 0)

--
HTH,

RD




-------------------------------------------------------------------------
--
Please keep all correspondence within the NewsGroup, so all may
benefit
!



-------------------------------------------------------------------------
--

"George A. Yorks" .(donotspam) wrote in

message
...
Thanks for the help. One further question.
In the formul In sheet 2 $A$1:$b$10makes reference to two

adjacent
columns.
If there is a column between so there is data in a and c and

none
in
b
the
result return err,the formula in this cell referes to cells that
are
currently emply. How to get around this. ie" if sheet two has

data
in
column
a and column d.

thanks

George Yorks

"Earl Kiosterud" wrote:

George,

In B1 of sheet 1:
=VLOOKUP(A1, 'Sheet2'!$A$1:$B$10, 2, FALSE)

Copy down with fill handle to B10.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"George A. Yorks" .(donotspam) wrote in
message
...
Trying to create a formula to do the following:
Sheet 1 column A a list of personal names a1-a10

Sheet 2 has list of names a1-a10 and list of dollar amounts
colums
d1-d10

want to search sheet one and if any name from sheet 2 found

on
sheet 1
than
the corresponding dollar amount is entered.

Any help appreciated.
--
George
















--

Dave Peterson
  #30  
Old March 27th, 2005, 05:15 PM
George A. Yorks
external usenet poster
 
Posts: n/a
Default

My column is comprised of names with varied spaces between first and last
name.As you suggested went to edit find/replace and in the find place the
name with extra spaces. in the replace entered the name with one place and
clicked on find. Nothing. The help menu is of no help. Has to be
something obvious that I am not doing

"Dave Peterson" wrote:

Look under Edit|Replace on the worksheet toolbar.

George A. Yorks wrote:

Have looked through help menu for how to perform a search and replace you
speak of. I can't find anything. Could you give me some direction. Thanks
for all your help

"Myrna Larson" wrote:

If you sometimes have double or triple spaces, you can do a search and replace
on column AX: search for two spaces and replace with 1 space. Repeat until
Excel tell you there are no more matches.

On Mon, 7 Mar 2005 20:17:02 -0800, George A. Yorks
.(donotspam) wrote:

In using the lookup formula I find that the data in column AX3 is not exactly
like that in sheet 2 B4. ie: the spacing between first and last name is not
the same. Any way to correct this. I only know of the differences when the
dollar amounts are not tranfered. I then have to correct these errors
manually. Hope this makes sense

Thanks for all the help

"George A. Yorks" wrote:

I tried this formula and get a "not found" also this is how the formula
gets
entered. Don't know why. It was entered as below.
=IF(ISNA(MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18,0)),"not

found",INDEX([GEO.xls]Sheet2!$Q$4:$Q$18,MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18,0)))
"RagDyeR" wrote:

Try this in Ay3 of "Miriam":

=IF(ISNA(MATCH(AX3,Geo!$B$4:$B$18,0)),"Not
Found",INDEX(Geo!$K$4:$K$18,MATCH(AX3,Geo!$B$4:$B$ 18,0)))

You can then *double click* on the "fill handle" in the lower right
corner
of AY3, which will *automatically* copy the formula in AY3 down Column
AY,
as far as there is data in Column AX.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"George A. Yorks" .(donotspam) wrote in message
...
Thanks for the help. I am confronted with a new scenario which I'll
present
for help.

One worksheet titled geo I have a list of names b4 thru b18 and data in
k4thru k18. I want to transfere the data to worksheet titled miriam
after
searching for the exact names. The names of the second worksheet are
column
ax3 thru ax89. Hope this makes sense to you.

George

"RagDyer" wrote:

First question:

Replace error message with a null ( "" ), which can then be added:

=IF(ISNA(MATCH(A1,'Sheet2'!$A$1:$A$10,0)),"",VLOOK UP(A1,
'Sheet2'!$A$1:$D$10, 4, 0)
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"George A. Yorks" .(donotspam) wrote in message
news I keep saying thanks, your help is outstanding. I would like to ask
two
additional questions. ie: When I enter my formula into the cells of
column
A
in a number of cells it returns a value in a few it returns#N/A.
There
is
no data in those cases but with the #n?A when I try to add the columns
will
not do so as it cant enter a non digit. I'm using 0 for the last
number
in
my formula. How best to get around this.

Also I've copied a table from USA internet. In pasting to worksheet
all
the
cells are obliterated and nothing is recognized by excel. The data is
however recognized in (pardon me) lotus. Is there anyway of having my
vlookup formula search 123 in place of a sheet in excel. =vlookup(a1,
sheet2!$A$1:$j$31, 10,0). Hope these questions make sense to someone

"Earl Kiosterud" wrote:

George,

The 4 is the third parameter of the VLOOKUP function, and tells it to
retrieve the cell in the 4th column of the range being looked up in.
Did
I
say being looked up in? Oh, well.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"George A. Yorks" .(donotspam) wrote in message
...
Thanks for the help. I know I'll fully understand the formula
structure
soon. One question, at end of formula ,4,0 what in fact does the 4
make
reference to?

"Ragdyer" wrote:

Try this:

=VLOOKUP(A1, 'Sheet2'!$A$1:$D$10, 4, 0)

--
HTH,

RD




-------------------------------------------------------------------------
--
Please keep all correspondence within the NewsGroup, so all may
benefit
!



-------------------------------------------------------------------------
--

"George A. Yorks" .(donotspam) wrote in
message
...
Thanks for the help. One further question.
In the formul In sheet 2 $A$1:$b$10makes reference to two
adjacent
columns.
If there is a column between so there is data in a and c and
none
in
b
the
result return err,the formula in this cell referes to cells that
are
currently emply. How to get around this. ie" if sheet two has
data
in
column
a and column d.

thanks

George Yorks

"Earl Kiosterud" wrote:

George,

In B1 of sheet 1:
=VLOOKUP(A1, 'Sheet2'!$A$1:$B$10, 2, FALSE)

Copy down with fill handle to B10.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"George A. Yorks" .(donotspam) wrote in
message
...
Trying to create a formula to do the following:
Sheet 1 column A a list of personal names a1-a10

Sheet 2 has list of names a1-a10 and list of dollar amounts
colums
d1-d10

want to search sheet one and if any name from sheet 2 found
on
sheet 1
than
the corresponding dollar amount is entered.

Any help appreciated.
--
George
















--

Dave Peterson

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Using Mail Merge for Creating a Document of Biographies... Sam Clarke General Discussion 1 January 4th, 2005 05:28 PM
Using Mail Merge for Creating a Document of Biographies... Sam Clarke Mailmerge 1 December 17th, 2004 11:17 AM
Using Mail Merge for Creating a Document of Biographies... Sam Clarke General Discussions 0 December 16th, 2004 08:35 PM
Creating Forms using VBA Suzi General Discussion 1 November 18th, 2004 04:46 PM
Problem Creating Report [email protected] Using Forms 1 June 7th, 2004 07:51 AM


All times are GMT +1. The time now is 02:01 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.