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
  #41  
Old June 28th, 2005, 11:11 PM
George A. Yorks
external usenet poster
 
Posts: n/a
Default

I would like to send the sheet to you however all I have is a list of names
and dollar amounts. This represent earnings that I am tracking. There is
the additional letter as a trailer to some of the names.(this designates
participation in an additional group. The use of clean function does
deletethe letter but the hidden space remains. The use of the trim function
can not correct this situation. I copy the list from the web. If you can
suggest how I might send it to you I will be pleasedto do so. Or any other
suggestions you might have
--
George


"Ragdyer" wrote:

If you wish, you can send me your sheet, and I'll see what I can figure out.

Cut out cutout from my address.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"George A. Yorks" .(donotspam) wrote in message
...
I have tried to use the suggested formula (=code(mid(a1,5,1,)) and in case
one received 32 as result. when I then go to search replace I have

entered
spaces to search and spaces to replace( four blank spaces to search and

two
blank spaces to replace.)This does not work and can't figure how else to

use
the search replace. Any further help appreciated
--
George


"RagDyer" wrote:

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

















  #42  
Old July 5th, 2005, 05:09 PM
George A. Yorks
external usenet poster
 
Posts: n/a
Default

A new question.
I am able to copy data from column of numbers to a corresponding column
using the following basic formula =b11 to represent column b row 11. In the
corresponding column I can drag the first cell data down the column and all
data is transfered. I there a formula to use to data from rows to columns
using the same concept. I am looking to have the column heading change and
the data row remaing the same.
--
George


"George A. Yorks" wrote:

I would like to send the sheet to you however all I have is a list of names
and dollar amounts. This represent earnings that I am tracking. There is
the additional letter as a trailer to some of the names.(this designates
participation in an additional group. The use of clean function does
deletethe letter but the hidden space remains. The use of the trim function
can not correct this situation. I copy the list from the web. If you can
suggest how I might send it to you I will be pleasedto do so. Or any other
suggestions you might have
--
George


"Ragdyer" wrote:

If you wish, you can send me your sheet, and I'll see what I can figure out.

Cut out cutout from my address.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"George A. Yorks" .(donotspam) wrote in message
...
I have tried to use the suggested formula (=code(mid(a1,5,1,)) and in case
one received 32 as result. when I then go to search replace I have

entered
spaces to search and spaces to replace( four blank spaces to search and

two
blank spaces to replace.)This does not work and can't figure how else to

use
the search replace. Any further help appreciated
--
George


"RagDyer" wrote:

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

















  #43  
Old July 5th, 2005, 10:29 PM
RagDyer
external usenet poster
 
Posts: n/a
Default

This will copy Row 10, from Column A, to as many columns as you drag down
the formula:
..
=INDEX($10:$10,,ROW(A1))
--
HTH,

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



"George A. Yorks" .(donotspam) wrote in message
...
A new question.
I am able to copy data from column of numbers to a corresponding column
using the following basic formula =b11 to represent column b row 11. In

the
corresponding column I can drag the first cell data down the column and

all
data is transfered. I there a formula to use to data from rows to columns
using the same concept. I am looking to have the column heading change

and
the data row remaing the same.
--
George


"George A. Yorks" wrote:

I would like to send the sheet to you however all I have is a list of

names
and dollar amounts. This represent earnings that I am tracking. There

is
the additional letter as a trailer to some of the names.(this designates
participation in an additional group. The use of clean function does
deletethe letter but the hidden space remains. The use of the trim

function
can not correct this situation. I copy the list from the web. If you

can
suggest how I might send it to you I will be pleasedto do so. Or any

other
suggestions you might have
--
George


"Ragdyer" wrote:

If you wish, you can send me your sheet, and I'll see what I can

figure out.

Cut out cutout from my address.
--
Regards,

RD


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

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

benefit !

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

-
"George A. Yorks" .(donotspam) wrote in message
...
I have tried to use the suggested formula (=code(mid(a1,5,1,)) and

in case
one received 32 as result. when I then go to search replace I have
entered
spaces to search and spaces to replace( four blank spaces to search

and
two
blank spaces to replace.)This does not work and can't figure how

else to
use
the search replace. Any further help appreciated
--
George


"RagDyer" wrote:

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


















  #44  
Old October 13th, 2006, 10:36 PM posted to microsoft.public.excel.misc
George A. Yorks
external usenet poster
 
Posts: 38
Default creating a formul

I have two questions that hope I can get help with.

have lotus worksheet that is read only. I followed instructions from help
menu but get an error message: an error occured applying atributes to the
file, access denied. The file is on a CD, selected it, went to
file,properties and removed the check for read only. Then the error message.

Second question, Have a worksheet with 50 rows. have a second worksheet
with 50 calculations. Want to create a formula that will transfere numbers
at 29 number intervals to each of the 50 rows any and all help appreciated
--
George


"George A. Yorks" wrote:

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

 




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 04:07 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.