Here's my situation:
What I need to do is create a simple formula that automatically grabs the
data from a spreadsheet ('Phone Exts.xls' - 'Sheet1') and put it in the
correct rows in another spreadsheet. The trouble is that the Phone Exts.xls
sheet has one column devoted to apartments formatted as so: 1001A; but, the
other sheet has two columns devoted to the apartment number, and bedroom
letter. So the sheets would look like:
Phone Exts.xls
Apt# | Ext
1001A | 26001
1001B | 26002

Other Sheet.xls
Apt# | Rm | Ext
1001 | A |
1001 | B |

So I thought I would create a simple formula to be run on each row in the
Ext column of 'Other Sheet.xls'. That's where I have trouble. How could I
do this? Is it even possible? Any help would be greatly appreciated. And
for the record, I am still googleing on how to do this, and I have searched
this community before posting.

~Brett

You can use something like this:

dim txt as String
txt = "3123C"

Range("A1") = Left(txt,Len(txt)-1)
Range("B1")=Right(txt,1)

set txt = to a variable and put the whole thing in a loop.

Or you can put the formulas into the worksheet with the proper
cell references in place of txt
--
steveB

Remove "AYN" from email to respond
"Brett Patterson" wrote in
message ...
Here's my situation:
What I need to do is create a simple formula that automatically grabs the
data from a spreadsheet ('Phone Exts.xls' - 'Sheet1') and put it in the
correct rows in another spreadsheet. The trouble is that the Phone
Exts.xls
sheet has one column devoted to apartments formatted as so: 1001A; but,
the
other sheet has two columns devoted to the apartment number, and bedroom
letter. So the sheets would look like:
Phone Exts.xls
Apt# | Ext
1001A | 26001
1001B | 26002

Other Sheet.xls
Apt# | Rm | Ext
1001 | A |
1001 | B |

So I thought I would create a simple formula to be run on each row in the
Ext column of 'Other Sheet.xls'. That's where I have trouble. How could
I
do this? Is it even possible? Any help would be greatly appreciated.
And
for the record, I am still googleing on how to do this, and I have
searched
this community before posting.

~Brett

In C2 of the sheetin Other Sheet.xls put in the formula:

=vlookup(A2&B2,'[Phone Exts.xls]!Sheet1'$A$1:$B$500,2,False)

then drag fill down the column

--
Regards,
Tom Ogilvy


"Brett Patterson" wrote in
message ...
Here's my situation:
What I need to do is create a simple formula that automatically grabs the
data from a spreadsheet ('Phone Exts.xls' - 'Sheet1') and put it in the
correct rows in another spreadsheet. The trouble is that the Phone
Exts.xls
sheet has one column devoted to apartments formatted as so: 1001A; but,
the
other sheet has two columns devoted to the apartment number, and bedroom
letter. So the sheets would look like:
Phone Exts.xls
Apt# | Ext
1001A | 26001
1001B | 26002

Other Sheet.xls
Apt# | Rm | Ext
1001 | A |
1001 | B |

So I thought I would create a simple formula to be run on each row in the
Ext column of 'Other Sheet.xls'. That's where I have trouble. How could
I
do this? Is it even possible? Any help would be greatly appreciated.
And
for the record, I am still googleing on how to do this, and I have
searched
this community before posting.

~Brett

Thanks for the replies.

I think Tom's answer is more on the track of what I'm looking for. Mainly
because it is contained within the spreadsheet. I run into a problem when I
try to use it though. I get the error saying there's something wrong with
the formula and it selects the table_array value: '[Phone

I thought maybe it has to do with a space being in the title, but it
doesn't. Any other suggestions? I know it's the right track, but it's just
not working.

~Brett

"Tom Ogilvy" wrote:

In C2 of the sheetin Other Sheet.xls put in the formula:

=vlookup(A2&B2,'[Phone Exts.xls]!Sheet1'$A$1:$B$500,2,False)

then drag fill down the column

--
Regards,
Tom Ogilvy


"Brett Patterson" wrote in
message ...
Here's my situation:
What I need to do is create a simple formula that automatically grabs the
data from a spreadsheet ('Phone Exts.xls' - 'Sheet1') and put it in the
correct rows in another spreadsheet. The trouble is that the Phone
Exts.xls
sheet has one column devoted to apartments formatted as so: 1001A; but,
the
other sheet has two columns devoted to the apartment number, and bedroom
letter. So the sheets would look like:
Phone Exts.xls
Apt# | Ext
1001A | 26001
1001B | 26002

Other Sheet.xls
Apt# | Rm | Ext
1001 | A |
1001 | B |

So I thought I would create a simple formula to be run on each row in the
Ext column of 'Other Sheet.xls'. That's where I have trouble. How could
I
do this? Is it even possible? Any help would be greatly appreciated.
And
for the record, I am still googleing on how to do this, and I have
searched
this community before posting.

~Brett

In d2 put in an = sign. Then navigate to Phone Exts.xls (in the menu, select
windows, then Phone Exts.xls) then go to the proper sheet and highlight the
data. Hit enter.

This will put in the proper reference to the sheet for you and you can copy
it into the formula I provided.

--
Regards ,
Tom Ogilvy


"Brett Patterson" wrote in
message ...
Thanks for the replies.

I think Tom's answer is more on the track of what I'm looking for. Mainly
because it is contained within the spreadsheet. I run into a problem when
I
try to use it though. I get the error saying there's something wrong with
the formula and it selects the table_array value: '[Phone

I thought maybe it has to do with a space being in the title, but it
doesn't. Any other suggestions? I know it's the right track, but it's
just
not working.

~Brett

"Tom Ogilvy" wrote:

In C2 of the sheetin Other Sheet.xls put in the formula:

=vlookup(A2&B2,'[Phone Exts.xls]!Sheet1'$A$1:$B$500,2,False)

then drag fill down the column

--
Regards,
Tom Ogilvy


"Brett Patterson" wrote in
message ...
Here's my situation:
What I need to do is create a simple formula that automatically grabs
the
data from a spreadsheet ('Phone Exts.xls' - 'Sheet1') and put it in
the
correct rows in another spreadsheet. The trouble is that the Phone
Exts.xls
sheet has one column devoted to apartments formatted as so: 1001A;
but,
the
other sheet has two columns devoted to the apartment number, and
bedroom
letter. So the sheets would look like:
Phone Exts.xls
Apt# | Ext
1001A | 26001
1001B | 26002

Other Sheet.xls
Apt# | Rm | Ext
1001 | A |
1001 | B |

So I thought I would create a simple formula to be run on each row in
the
Ext column of 'Other Sheet.xls'. That's where I have trouble. How
could
I
do this? Is it even possible? Any help would be greatly appreciated.
And
for the record, I am still googleing on how to do this, and I have
searched
this community before posting.

~Brett

Thanks for that. Still nothing yet. The formula I'm using is:

=vlookup(A2&B2,'[Phone EXTs.xls]Sheet1'!$A$2:$B$583,2,false)

I did as you instructed, and now, it doesn't do anything. It just gives me
the formula (not the output) or any errors when I hit enter.

~Brett

"Tom Ogilvy" wrote:

In d2 put in an = sign. Then navigate to Phone Exts.xls (in the menu, select
windows, then Phone Exts.xls) then go to the proper sheet and highlight the
data. Hit enter.

This will put in the proper reference to the sheet for you and you can copy
it into the formula I provided.

--
Regards ,
Tom Ogilvy


"Brett Patterson" wrote in
message ...
Thanks for the replies.

I think Tom's answer is more on the track of what I'm looking for. Mainly
because it is contained within the spreadsheet. I run into a problem when
I
try to use it though. I get the error saying there's something wrong with
the formula and it selects the table_array value: '[Phone

I thought maybe it has to do with a space being in the title, but it
doesn't. Any other suggestions? I know it's the right track, but it's
just
not working.

~Brett

"Tom Ogilvy" wrote:

In C2 of the sheetin Other Sheet.xls put in the formula:

=vlookup(A2&B2,'[Phone Exts.xls]!Sheet1'$A$1:$B$500,2,False)

then drag fill down the column

--
Regards,
Tom Ogilvy


"Brett Patterson" wrote in
message ...
Here's my situation:
What I need to do is create a simple formula that automatically grabs
the
data from a spreadsheet ('Phone Exts.xls' - 'Sheet1') and put it in
the
correct rows in another spreadsheet. The trouble is that the Phone
Exts.xls
sheet has one column devoted to apartments formatted as so: 1001A;
but,
the
other sheet has two columns devoted to the apartment number, and
bedroom
letter. So the sheets would look like:
Phone Exts.xls
Apt# | Ext
1001A | 26001
1001B | 26002

Other Sheet.xls
Apt# | Rm | Ext
1001 | A |
1001 | B |

So I thought I would create a simple formula to be run on each row in
the
Ext column of 'Other Sheet.xls'. That's where I have trouble. How
could
I
do this? Is it even possible? Any help would be greatly appreciated.
And
for the record, I am still googleing on how to do this, and I have
searched
this community before posting.

~Brett

first, Go into Tools=Options=View and make sure that "formulas" is
unchecked.

then make sure the cell isn't formatted as Text.

If that is not the case, then

select the column and do

Edit=Replace

in both boxes put an equal sign.

Then click replace all (replace an equal sign with an equal sign). this
usually causes it to be evaluated as a formula

--
Regards,
Tom Ogilvy

"Brett Patterson" wrote in
message ...
Thanks for that. Still nothing yet. The formula I'm using is:

=vlookup(A2&B2,'[Phone EXTs.xls]Sheet1'!$A$2:$B$583,2,false)

I did as you instructed, and now, it doesn't do anything. It just gives
me
the formula (not the output) or any errors when I hit enter.

~Brett

"Tom Ogilvy" wrote:

In d2 put in an = sign. Then navigate to Phone Exts.xls (in the menu,
select
windows, then Phone Exts.xls) then go to the proper sheet and highlight
the
data. Hit enter.

This will put in the proper reference to the sheet for you and you can
copy
it into the formula I provided.

--
Regards ,
Tom Ogilvy


"Brett Patterson" wrote in
message ...
Thanks for the replies.

I think Tom's answer is more on the track of what I'm looking for.
Mainly
because it is contained within the spreadsheet. I run into a problem
when
I
try to use it though. I get the error saying there's something wrong
with
the formula and it selects the table_array value: '[Phone

I thought maybe it has to do with a space being in the title, but it
doesn't. Any other suggestions? I know it's the right track, but
it's
just
not working.

~Brett

"Tom Ogilvy" wrote:

In C2 of the sheetin Other Sheet.xls put in the formula:

=vlookup(A2&B2,'[Phone Exts.xls]!Sheet1'$A$1:$B$500,2,False)

then drag fill down the column

--
Regards,
Tom Ogilvy


"Brett Patterson" wrote
in
message ...
Here's my situation:
What I need to do is create a simple formula that automatically
grabs
the
data from a spreadsheet ('Phone Exts.xls' - 'Sheet1') and put it
in
the
correct rows in another spreadsheet. The trouble is that the
Phone
Exts.xls
sheet has one column devoted to apartments formatted as so: 1001A;
but,
the
other sheet has two columns devoted to the apartment number, and
bedroom
letter. So the sheets would look like:
Phone Exts.xls
Apt# | Ext
1001A | 26001
1001B | 26002

Other Sheet.xls
Apt# | Rm | Ext
1001 | A |
1001 | B |

So I thought I would create a simple formula to be run on each row
in
the
Ext column of 'Other Sheet.xls'. That's where I have trouble.
How
could
I
do this? Is it even possible? Any help would be greatly
appreciated.
And
for the record, I am still googleing on how to do this, and I have
searched
this community before posting.

~Brett