using data from another spreadsheet

[Andy Roberts]

Is it possible to "populate" cells with info from a difference
spreadsheet similar to a mail merge with a word doc?

Andy
Office 2007
Win XP

 

[Gord Dibben]

Yes.

See help on "linking"


Gord Dibben MS Excel MVP

 

[Dave Peterson]

If you're retrieving a value from a worksheet in a different workbook, you can
open excel and open both workbooks.

Go to the sending cell and do Edit|Copy (xl2003 menus)
Then go to the receiving cell and Edit|Paste special|paste link

You'll see that excel builds a formula for you using the correct syntax. And if
you close the sending workbook, you'll see the formula adjusts nicely, too.

==========
On the other hand, if you want to cycle through data in one worksheet and (say)
print a report based on each row in that worksheet -- but in a nice format in a
different worksheet, you can use a macro.

Debra Dalgleish has some sample code here:
http://contextures.com/xlForm03.html
and maybe here:
http://contextures.com/xlForm02.html

It actually uses worksheets in the same workbook, but that can be changed
easily.

 

[Andy Roberts]

Thanks Guys

I'm not sure either suggestion is quite what i'm looking for. I wouldn't
have thought my requirement was that unique but it seems it may be.

Let me explain a little more.

In my first excel file I have a list of jobs :-

Job No Site Postcode
-------- ----- ------------
1000 London SW16 7ED
1001 Manchester M1 3DJ
1002 Bristol B2 3GF


In a totally different excel file I have it saved as a template which allows
me to calculate our fee for each job based on many factors. This sheet is
set up so I can input number of hours against different elements to the job
which then all add up to the main price. The sheet then gets printed and
filed.

At the top of this calculation sheet I have some cells where I type in Job
No, Site and Postcode. What I'd like to do is save the time doing this as
the information already appears in the first spreadsheet. I simply want to
populate these cells with the values from the first sheet, but each time the
row i want to use from the first sheet will change, so I could do with the
ability to "cycle" through the rows once in the second sheet, or
alternatively highlight the row in the first sheet and then run a macro to
create the second sheetfrom the template and add the data.

I have this type of scenario working between word and excel as mail merges
withother types of data, but the resulting print out needs to come from
excel as I need the calculation elements of thespreadsheet.

I think Dave's suggestion is the closest but I have looked into this before
and didn't see how it could be adapted as once the second spreadsheet was
created I then wanted to manuipulate the figures, rather than go straight to
print.

--
Regards

Andy

Andy Roberts
Win XP, Office 2007

 

[Roger Govier]

Hi Andy

I am assuming that Job numbers are unique, and only appear once in your
file1.

That being the case, and assuming you enter Job No in A2 of File 2, then
on in cell B2 of File2 enter

=IF($A2="","",VLOOKUP($A2,[file1.xls]Sheet1!$A:$C,COLUMN(B2),0))
Copy across and down as required

Column(B2) will return a value of 2 when entered in column B, which will
return the data from the 2nd column of your lookup.
As you copy across, it will change to C2 and return 3 therby picking up
data from the 3rd column.

If you had more data in File1 that needs to be picked up, then extend
the lookup range, say to $A:$H or whatever you need.

File2 does not need to be open for this to work.

 

[Andy Roberts]

Roger



Thanks for this - its exactly what I want. I have a small issue with
getting the formula to work. I have amended as follows:-



=IF($D2="","",VLOOKUP($D2,[Admin.xlsx]T 09-10!$A:$C,COLUMN(D2),0))



The problem is that the sheet name in the lookup file has a space in it. I
cant change this as there are other mail merges etc which rely on it. I've
tried to use "" but still get an error.


--
Regards

Andy

Andy Roberts
Win XP, Office 2007

Hi Andy

I am assuming that Job numbers are unique, and only appear once in your
file1.

That being the case, and assuming you enter Job No in A2 of File 2, then
on in cell B2 of File2 enter

=IF($A2="","",VLOOKUP($A2,[file1.xls]Sheet1!$A:$C,COLUMN(B2),0))
Copy across and down as required

Column(B2) will return a value of 2 when entered in column B, which will
return the data from the 2nd column of your lookup.
As you copy across, it will change to C2 and return 3 therby picking up
data from the 3rd column.

If you had more data in File1 that needs to be picked up, then extend the
lookup range, say to $A:$H or whatever you need.

File2 does not need to be open for this to work.

--
Regards
Roger Govier

Thanks Guys

I'm not sure either suggestion is quite what i'm looking for. I wouldn't
have thought my requirement was that unique but it seems it may be.

Let me explain a little more.

In my first excel file I have a list of jobs :-

Job No Site Postcode
-------- ----- ------------
1000 London SW16 7ED
1001 Manchester M1 3DJ
1002 Bristol B2 3GF


In a totally different excel file I have it saved as a template which
allows me to calculate our fee for each job based on many factors. This
sheet is set up so I can input number of hours against different elements
to the job which then all add up to the main price. The sheet then gets
printed and filed.

At the top of this calculation sheet I have some cells where I type in
Job No, Site and Postcode. What I'd like to do is save the time doing
this as the information already appears in the first spreadsheet. I
simply want to populate these cells with the values from the first sheet,
but each time the row i want to use from the first sheet will change, so
I could do with the ability to "cycle" through the rows once in the
second sheet, or alternatively highlight the row in the first sheet and
then run a macro to create the second sheetfrom the template and add the
data.

I have this type of scenario working between word and excel as mail
merges withother types of data, but the resulting print out needs to come
from excel as I need the calculation elements of thespreadsheet.

I think Dave's suggestion is the closest but I have looked into this
before and didn't see how it could be adapted as once the second
spreadsheet was created I then wanted to manuipulate the figures, rather
than go straight to print.

 

[John]

Hi Andy
Try it this way;
=IF($D2="","",VLOOKUP($D2,'[Admin.xlsx]T 09-10'!$A:$C,COLUMN(D2),0))
Copy and past it, don't retype it.
HTH
John

Roger



Thanks for this - its exactly what I want. I have a small issue with getting
the formula to work. I have amended as follows:-



=IF($D2="","",VLOOKUP($D2,[Admin.xlsx]T 09-10!$A:$C,COLUMN(D2),0))



The problem is that the sheet name in the lookup file has a space in it. I
cant change this as there are other mail merges etc which rely on it. I've
tried to use "" but still get an error.


--
Regards

Andy

Andy Roberts
Win XP, Office 2007

Hi Andy

I am assuming that Job numbers are unique, and only appear once in your
file1.

That being the case, and assuming you enter Job No in A2 of File 2, then on
in cell B2 of File2 enter

=IF($A2="","",VLOOKUP($A2,[file1.xls]Sheet1!$A:$C,COLUMN(B2),0))
Copy across and down as required

Column(B2) will return a value of 2 when entered in column B, which will
return the data from the 2nd column of your lookup.
As you copy across, it will change to C2 and return 3 therby picking up data
from the 3rd column.

If you had more data in File1 that needs to be picked up, then extend the
lookup range, say to $A:$H or whatever you need.

File2 does not need to be open for this to work.

--
Regards
Roger Govier

Thanks Guys

I'm not sure either suggestion is quite what i'm looking for. I wouldn't
have thought my requirement was that unique but it seems it may be.

Let me explain a little more.

In my first excel file I have a list of jobs :-

Job No Site Postcode
-------- ----- ------------
1000 London SW16 7ED
1001 Manchester M1 3DJ
1002 Bristol B2 3GF


In a totally different excel file I have it saved as a template which allows
me to calculate our fee for each job based on many factors. This sheet is
set up so I can input number of hours against different elements to the job
which then all add up to the main price. The sheet then gets printed and
filed.

At the top of this calculation sheet I have some cells where I type in Job
No, Site and Postcode. What I'd like to do is save the time doing this as
the information already appears in the first spreadsheet. I simply want to
populate these cells with the values from the first sheet, but each time the
row i want to use from the first sheet will change, so I could do with the
ability to "cycle" through the rows once in the second sheet, or
alternatively highlight the row in the first sheet and then run a macro to
create the second sheetfrom the template and add the data.

I have this type of scenario working between word and excel as mail merges
withother types of data, but the resulting print out needs to come from
excel as I need the calculation elements of thespreadsheet.

I think Dave's suggestion is the closest but I have looked into this before
and didn't see how it could be adapted as once the second spreadsheet was
created I then wanted to manuipulate the figures, rather than go straight to
print.

 

[Andy Roberts]

Thanks John

The formula sort of works but i just need to get the references right as I'm
getting #Name, but I dont fully understand the breakdown of the syntax.

=IF($D2="","",VLOOKUP($D2,'[Admin.xlsx]T 09-10'!$A:$C,COLUMN(D2),0))

If I understand it D2 is the cell in which I type the job number. Which
then looks up values on the same row from the Admin.xlsx file. I dont
understand the remainder of the equation. I want E4 (in the main sheet) to
"populate" with the text contained in the "S" column of the same row as the
value I type in (which is in column A of the first sheet).

I could also do with "merging" two columns of data if possible. So column
"S" is an address and column "T" is a postcode which I'd like to stick in E4
separated by a comma (i.e. "Column_S_text", "Column_T_text")

--
Regards

Andy

Andy Roberts
Win XP, Office 2007

Hi Andy
Try it this way;
=IF($D2="","",VLOOKUP($D2,'[Admin.xlsx]T 09-10'!$A:$C,COLUMN(D2),0))
Copy and past it, don't retype it.
HTH
John

Roger



Thanks for this - its exactly what I want. I have a small issue with
getting the formula to work. I have amended as follows:-



=IF($D2="","",VLOOKUP($D2,[Admin.xlsx]T 09-10!$A:$C,COLUMN(D2),0))



The problem is that the sheet name in the lookup file has a space in it.
I cant change this as there are other mail merges etc which rely on it.
I've tried to use "" but still get an error.


--
Regards

Andy

Andy Roberts
Win XP, Office 2007

Hi Andy

I am assuming that Job numbers are unique, and only appear once in your
file1.

That being the case, and assuming you enter Job No in A2 of File 2, then
on in cell B2 of File2 enter

=IF($A2="","",VLOOKUP($A2,[file1.xls]Sheet1!$A:$C,COLUMN(B2),0))
Copy across and down as required

Column(B2) will return a value of 2 when entered in column B, which will
return the data from the 2nd column of your lookup.
As you copy across, it will change to C2 and return 3 therby picking up
data from the 3rd column.

If you had more data in File1 that needs to be picked up, then extend
the lookup range, say to $A:$H or whatever you need.

File2 does not need to be open for this to work.

--
Regards
Roger Govier

Andy Roberts wrote:
Thanks Guys

I'm not sure either suggestion is quite what i'm looking for. I
wouldn't have thought my requirement was that unique but it seems it
may be.

Let me explain a little more.

In my first excel file I have a list of jobs :-

Job No Site Postcode
-------- ----- ------------
1000 London SW16 7ED
1001 Manchester M1 3DJ
1002 Bristol B2 3GF


In a totally different excel file I have it saved as a template which
allows me to calculate our fee for each job based on many factors.
This sheet is set up so I can input number of hours against different
elements to the job which then all add up to the main price. The sheet
then gets printed and filed.

At the top of this calculation sheet I have some cells where I type in
Job No, Site and Postcode. What I'd like to do is save the time doing
this as the information already appears in the first spreadsheet. I
simply want to populate these cells with the values from the first
sheet, but each time the row i want to use from the first sheet will
change, so I could do with the ability to "cycle" through the rows once
in the second sheet, or alternatively highlight the row in the first
sheet and then run a macro to create the second sheetfrom the template
and add the data.

I have this type of scenario working between word and excel as mail
merges withother types of data, but the resulting print out needs to
come from excel as I need the calculation elements of thespreadsheet.

I think Dave's suggestion is the closest but I have looked into this
before and didn't see how it could be adapted as once the second
spreadsheet was created I then wanted to manuipulate the figures,
rather than go straight to print.

 

[John]

Hi Andy
The formula is looking up information in cell D2 then find the same information
in Workbook Admin.xlsx
worksheet name T 09-10, that information should be in column A, does't matter if
its on the same row.
Your range is Column A to C and your requesting what is in column D "ERROR"
I don't see your form so try replacing in your formula Column D(2) and just put
2 or 3.
2 would represent column B and 3 Column C.
HTH
John

Thanks John

The formula sort of works but i just need to get the references right as I'm
getting #Name, but I dont fully understand the breakdown of the syntax.

=IF($D2="","",VLOOKUP($D2,'[Admin.xlsx]T 09-10'!$A:$C,COLUMN(D2),0))

If I understand it D2 is the cell in which I type the job number. Which then
looks up values on the same row from the Admin.xlsx file. I dont understand
the remainder of the equation. I want E4 (in the main sheet) to "populate"
with the text contained in the "S" column of the same row as the value I type
in (which is in column A of the first sheet).

I could also do with "merging" two columns of data if possible. So column "S"
is an address and column "T" is a postcode which I'd like to stick in E4
separated by a comma (i.e. "Column_S_text", "Column_T_text")

--
Regards

Andy

Andy Roberts
Win XP, Office 2007

Hi Andy
Try it this way;
=IF($D2="","",VLOOKUP($D2,'[Admin.xlsx]T 09-10'!$A:$C,COLUMN(D2),0))
Copy and past it, don't retype it.
HTH
John

Roger



Thanks for this - its exactly what I want. I have a small issue with
getting the formula to work. I have amended as follows:-



=IF($D2="","",VLOOKUP($D2,[Admin.xlsx]T 09-10!$A:$C,COLUMN(D2),0))



The problem is that the sheet name in the lookup file has a space in it. I
cant change this as there are other mail merges etc which rely on it. I've
tried to use "" but still get an error.


--
Regards

Andy

Andy Roberts
Win XP, Office 2007
Hi Andy

I am assuming that Job numbers are unique, and only appear once in your
file1.

That being the case, and assuming you enter Job No in A2 of File 2, then on
in cell B2 of File2 enter

=IF($A2="","",VLOOKUP($A2,[file1.xls]Sheet1!$A:$C,COLUMN(B2),0))
Copy across and down as required

Column(B2) will return a value of 2 when entered in column B, which will
return the data from the 2nd column of your lookup.
As you copy across, it will change to C2 and return 3 therby picking up
data from the 3rd column.

If you had more data in File1 that needs to be picked up, then extend the
lookup range, say to $A:$H or whatever you need.

File2 does not need to be open for this to work.

--
Regards
Roger Govier

Andy Roberts wrote:
Thanks Guys

I'm not sure either suggestion is quite what i'm looking for. I wouldn't
have thought my requirement was that unique but it seems it may be.

Let me explain a little more.

In my first excel file I have a list of jobs :-

Job No Site Postcode
-------- ----- ------------
1000 London SW16 7ED
1001 Manchester M1 3DJ
1002 Bristol B2 3GF


In a totally different excel file I have it saved as a template which
allows me to calculate our fee for each job based on many factors. This
sheet is set up so I can input number of hours against different elements
to the job which then all add up to the main price. The sheet then gets
printed and filed.

At the top of this calculation sheet I have some cells where I type in Job
No, Site and Postcode. What I'd like to do is save the time doing this as
the information already appears in the first spreadsheet. I simply want
to populate these cells with the values from the first sheet, but each
time the row i want to use from the first sheet will change, so I could do
with the ability to "cycle" through the rows once in the second sheet, or
alternatively highlight the row in the first sheet and then run a macro to
create the second sheetfrom the template and add the data.

I have this type of scenario working between word and excel as mail merges
withother types of data, but the resulting print out needs to come from
excel as I need the calculation elements of thespreadsheet.

I think Dave's suggestion is the closest but I have looked into this
before and didn't see how it could be adapted as once the second
spreadsheet was created I then wanted to manuipulate the figures, rather
than go straight to print.

 

[Roger Govier]

Hi Andy

You have rather moved the goal posts on your request.
=IF($D2="","",VLOOKUP($D2,'[Admin.xlsx]T 09-10'!$A:$C,COLUMN(D2),0))

the generalised part of the vlookup formula is
=vlookup(lookupvalue,range_to_look_up,offset,True or False)

What I gave you initially, assumed the data as you had shown it was in
columns a, b and c
=IF($A2="","",VLOOKUP($A2,[file1.xls]Sheet1!$A:$C,COLUMN(B2),0))
where I had also assumed the point at which you were entering the Job
number was in column A of the second sheet.

I explained, that column(B2) would return a value of 2, rather than hard
coding 2 into the formula, so it would adjust as you copy the formula
across.


In my posting I had not enclosed my sheet name in single quotes as there
were no spaces. John informed you how to overcome that problem.

But you now say you want to collect data from column S of the
lookup_table. That's fine, but the lookup_range has to be extended.
Is the column containing the job number, column A on sheet 1?
if so, then the formula would be

=IF($D2="","",VLOOKUP($D2,'[Admin.xlsx]T 09-10'!$A:$Z,19,0))
or, if you were wanting to drag the formula across the page and have it
automatically adjust to pick up other data, then
=IF($D2="","",VLOOKUP($D2,'[Admin.xlsx]T 09-10'!$A:$Z,column(S2),0))

If the data were such that your Job Number was in column J of sheet2,
and the value you wanted to retrieve was still in column S, then the
formula would be

=IF($D2="","",VLOOKUP($D2,'[Admin.xlsx]T 09-10'!$J:$Z,9,0))
or
=IF($D2="","",VLOOKUP($D2,'[Admin.xlsx]T 09-10'!$A:$Z,column(I),0))

With regards to concatenating, then it would be
=IF($D2="","",VLOOKUP($D2,'[Admin.xlsx]T 09-10'!$A:$Z,19,0) &
", " & VLOOKUP($D2,'[Admin.xlsx]T 09-10'!$A:$Z,20,0))

Again, adjust the ranges and offset to suit your data layout.

 

[Andy Roberts]

Thanks Roger

This makes a little more sense - I apologise for moving the goal posts but I
try sometimes to simplify my problem for explanation purposes and then
expand once things are explained.

I'll give it a go tomorrow

--
Regards

Andy

Andy Roberts
Win XP, Office 2007

Hi Andy

You have rather moved the goal posts on your request.
=IF($D2="","",VLOOKUP($D2,'[Admin.xlsx]T 09-10'!$A:$C,COLUMN(D2),0))

the generalised part of the vlookup formula is
=vlookup(lookupvalue,range_to_look_up,offset,True or False)

What I gave you initially, assumed the data as you had shown it was in
columns a, b and c
=IF($A2="","",VLOOKUP($A2,[file1.xls]Sheet1!$A:$C,COLUMN(B2),0))
where I had also assumed the point at which you were entering the Job
number was in column A of the second sheet.

I explained, that column(B2) would return a value of 2, rather than hard
coding 2 into the formula, so it would adjust as you copy the formula
across.


In my posting I had not enclosed my sheet name in single quotes as there
were no spaces. John informed you how to overcome that problem.

But you now say you want to collect data from column S of the
lookup_table. That's fine, but the lookup_range has to be extended.
Is the column containing the job number, column A on sheet 1?
if so, then the formula would be

=IF($D2="","",VLOOKUP($D2,'[Admin.xlsx]T 09-10'!$A:$Z,19,0))
or, if you were wanting to drag the formula across the page and have it
automatically adjust to pick up other data, then
=IF($D2="","",VLOOKUP($D2,'[Admin.xlsx]T 09-10'!$A:$Z,column(S2),0))

If the data were such that your Job Number was in column J of sheet2, and
the value you wanted to retrieve was still in column S, then the formula
would be

=IF($D2="","",VLOOKUP($D2,'[Admin.xlsx]T 09-10'!$J:$Z,9,0))
or
=IF($D2="","",VLOOKUP($D2,'[Admin.xlsx]T 09-10'!$A:$Z,column(I),0))

With regards to concatenating, then it would be
=IF($D2="","",VLOOKUP($D2,'[Admin.xlsx]T 09-10'!$A:$Z,19,0) &
", " & VLOOKUP($D2,'[Admin.xlsx]T 09-10'!$A:$Z,20,0))

Again, adjust the ranges and offset to suit your data layout.
--
Regards
Roger Govier

Thanks John

The formula sort of works but i just need to get the references right as
I'm getting #Name, but I dont fully understand the breakdown of the
syntax.

=IF($D2="","",VLOOKUP($D2,'[Admin.xlsx]T 09-10'!$A:$C,COLUMN(D2),0))

If I understand it D2 is the cell in which I type the job number. Which
then looks up values on the same row from the Admin.xlsx file. I dont
understand the remainder of the equation. I want E4 (in the main sheet)
to "populate" with the text contained in the "S" column of the same row
as the value I type in (which is in column A of the first sheet).

I could also do with "merging" two columns of data if possible. So
column "S" is an address and column "T" is a postcode which I'd like to
stick in E4 separated by a comma (i.e. "Column_S_text", "Column_T_text")

 

[Andy Roberts]

Excellent Roger - job done thanks

--
Regards

Andy

Andy Roberts
Win XP, Office 2007

Hi Andy

You have rather moved the goal posts on your request.
=IF($D2="","",VLOOKUP($D2,'[Admin.xlsx]T 09-10'!$A:$C,COLUMN(D2),0))

the generalised part of the vlookup formula is
=vlookup(lookupvalue,range_to_look_up,offset,True or False)

What I gave you initially, assumed the data as you had shown it was in
columns a, b and c
=IF($A2="","",VLOOKUP($A2,[file1.xls]Sheet1!$A:$C,COLUMN(B2),0))
where I had also assumed the point at which you were entering the Job
number was in column A of the second sheet.

I explained, that column(B2) would return a value of 2, rather than hard
coding 2 into the formula, so it would adjust as you copy the formula
across.


In my posting I had not enclosed my sheet name in single quotes as there
were no spaces. John informed you how to overcome that problem.

But you now say you want to collect data from column S of the
lookup_table. That's fine, but the lookup_range has to be extended.
Is the column containing the job number, column A on sheet 1?
if so, then the formula would be

=IF($D2="","",VLOOKUP($D2,'[Admin.xlsx]T 09-10'!$A:$Z,19,0))
or, if you were wanting to drag the formula across the page and have it
automatically adjust to pick up other data, then
=IF($D2="","",VLOOKUP($D2,'[Admin.xlsx]T 09-10'!$A:$Z,column(S2),0))

If the data were such that your Job Number was in column J of sheet2, and
the value you wanted to retrieve was still in column S, then the formula
would be

=IF($D2="","",VLOOKUP($D2,'[Admin.xlsx]T 09-10'!$J:$Z,9,0))
or
=IF($D2="","",VLOOKUP($D2,'[Admin.xlsx]T 09-10'!$A:$Z,column(I),0))

With regards to concatenating, then it would be
=IF($D2="","",VLOOKUP($D2,'[Admin.xlsx]T 09-10'!$A:$Z,19,0) &
", " & VLOOKUP($D2,'[Admin.xlsx]T 09-10'!$A:$Z,20,0))

Again, adjust the ranges and offset to suit your data layout.
--
Regards
Roger Govier

Thanks John

The formula sort of works but i just need to get the references right as
I'm getting #Name, but I dont fully understand the breakdown of the
syntax.

=IF($D2="","",VLOOKUP($D2,'[Admin.xlsx]T 09-10'!$A:$C,COLUMN(D2),0))

If I understand it D2 is the cell in which I type the job number. Which
then looks up values on the same row from the Admin.xlsx file. I dont
understand the remainder of the equation. I want E4 (in the main sheet)
to "populate" with the text contained in the "S" column of the same row
as the value I type in (which is in column A of the first sheet).

I could also do with "merging" two columns of data if possible. So
column "S" is an address and column "T" is a postcode which I'd like to
stick in E4 separated by a comma (i.e. "Column_S_text", "Column_T_text")