Copy Data from External Spreadsheet

[Guest]

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

 

[STEVE BELL]

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

 

[Tom Ogilvy]

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

 

[Guest]

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

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

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

 

[Tom Ogilvy]

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

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

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

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

 

[Guest]

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

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

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

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


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

 

[Tom Ogilvy]

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

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

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

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

:

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


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