Retrieving non adjacent data from other workbooks?

[Guest]

I have designed a tax receipt under Excel 2003. Each receipt uses 20 rows
and 8 columns and are laid 6 per A4 page. What I would like to know is how I
can get my tax year end summary workbook, which is a separate workbook, to
find the receipt no. cell and the total dollar value cell for a particular
person so I don't have to search manually and input the data.
I am using the Vlookup function for other stuff but I am stuck on this one
because the cells are not in the same row or column. There are a total of
150 receipts on the worksheet.
Example:
Receipt No. 001: Receipt No. 002:
Persons name is in B7 Persons name is in L7
receipt no. is in G1 Receipt No. is in Q1
Total dollar value is D15 Total dollar value is N15

Receipt No. 003: Receipt No. 004:
Persons name is in B27 Persons name is in L27
receipt no. is in G21 Receipt No. is in Q21
Total dollar value is D25 Total dollar value is N25

The pattern continues as each receipt takes 20 rows.
My tax end summary workbook:
Receipt no. cell(V2) looks up name cell (B2) and goes to my tax receipts
workbook. It looks up that name amongst the 150 receipts in both the B and L
columns to find the name. Once it is located, it has to read the cell that
has the receipt no. which will be in either column G or Q and whatever row no.
Same process again to get then the total dollar value.
Total dollar value cell (W2) looks up name cell (B2) goes to my tax receipts
workbook looks again for the name amongst the 150 receipts in both the B and
L columns to find the name. Once located it has to read the cell that has
the total dollar value in it which will be in either column D or N and
whatever row.
On the receipt workbook:
Is there a way to tell my name cell that its part of a group of cells so the
associated receipt no. cell and the total dollar value cell can be located.
Would grouping help? Those 8 columns and 20 rows need to be connected as a
set somehow so that when the Vlookup finds the name it will then know which
column and row the cell with the receipt no. in it for example would be.
Can anyone help or have I made this too complicated?

 

[Guest]

There are two ways I can think of.
My prefered way would be to set up a list of names and locations
Name1][B7
Name2][L7
Name 3][B27
etc
then use offset/indirect functions to gather data
=offset(indirect(vlookup(name,nameloclist,2,false)),-6,5,1,1) for receipt
number
and
=offset(indirect(vlookup(name,nameloclist,2,false)),8,2,1,1) for total
dollar amount.

the other way is using sumproduct
=sumproduct(--([wb]shnm!$B$7:$L$207=name),$G$1:Q201) for receipt
=sumproduct(--([wb]shnm!$B$7:$L$207=name),$D$15:$N$215) for dollar amount

Both of these could be played with to meet your needs. The first option is
much more flexible.

 

[Guest]

Hi BJ,
Thanks for your reply. It is now 12.30am here so I will try it in the
morning and get back to you if I need to. Hopefully more likely to think
clearly.
Thanks so much
Sue

There are two ways I can think of.
My prefered way would be to set up a list of names and locations
Name1][B7
Name2][L7
Name 3][B27
etc
then use offset/indirect functions to gather data
=offset(indirect(vlookup(name,nameloclist,2,false)),-6,5,1,1) for receipt
number
and
=offset(indirect(vlookup(name,nameloclist,2,false)),8,2,1,1) for total
dollar amount.

the other way is using sumproduct
=sumproduct(--([wb]shnm!$B$7:$L$207=name),$G$1:Q201) for receipt
=sumproduct(--([wb]shnm!$B$7:$L$207=name),$D$15:$N$215) for dollar amount

Both of these could be played with to meet your needs. The first option is
much more flexible.

I have designed a tax receipt under Excel 2003. Each receipt uses 20 rows
and 8 columns and are laid 6 per A4 page. What I would like to know is how I
can get my tax year end summary workbook, which is a separate workbook, to
find the receipt no. cell and the total dollar value cell for a particular
person so I don't have to search manually and input the data.
I am using the Vlookup function for other stuff but I am stuck on this one
because the cells are not in the same row or column. There are a total of
150 receipts on the worksheet.
Example:
Receipt No. 001: Receipt No. 002:
Persons name is in B7 Persons name is in L7
receipt no. is in G1 Receipt No. is in Q1
Total dollar value is D15 Total dollar value is N15

Receipt No. 003: Receipt No. 004:
Persons name is in B27 Persons name is in L27
receipt no. is in G21 Receipt No. is in Q21
Total dollar value is D25 Total dollar value is N25

The pattern continues as each receipt takes 20 rows.
My tax end summary workbook:
Receipt no. cell(V2) looks up name cell (B2) and goes to my tax receipts
workbook. It looks up that name amongst the 150 receipts in both the B and L
columns to find the name. Once it is located, it has to read the cell that
has the receipt no. which will be in either column G or Q and whatever row no.
Same process again to get then the total dollar value.
Total dollar value cell (W2) looks up name cell (B2) goes to my tax receipts
workbook looks again for the name amongst the 150 receipts in both the B and
L columns to find the name. Once located it has to read the cell that has
the total dollar value in it which will be in either column D or N and
whatever row.
On the receipt workbook:
Is there a way to tell my name cell that its part of a group of cells so the
associated receipt no. cell and the total dollar value cell can be located.
Would grouping help? Those 8 columns and 20 rows need to be connected as a
set somehow so that when the Vlookup finds the name it will then know which
column and row the cell with the receipt no. in it for example would be.
Can anyone help or have I made this too complicated?

 

[Guest]

Hi bj,
I not sure if I follow you. I don't know whose names will be where in the
receipt workbook until I go in there. Are you suggesting I would then have
to make a list at that stage? If that is the case then it would be easier
actually to feed the receipt no. and amount back into my tax end summary
sheet manually after I printed out the receipts. Have I interpreted your
info. wrong?
Thanks Sue

There are two ways I can think of.
My prefered way would be to set up a list of names and locations
Name1][B7
Name2][L7
Name 3][B27
etc
then use offset/indirect functions to gather data
=offset(indirect(vlookup(name,nameloclist,2,false)),-6,5,1,1) for receipt
number
and
=offset(indirect(vlookup(name,nameloclist,2,false)),8,2,1,1) for total
dollar amount.

the other way is using sumproduct
=sumproduct(--([wb]shnm!$B$7:$L$207=name),$G$1:Q201) for receipt
=sumproduct(--([wb]shnm!$B$7:$L$207=name),$D$15:$N$215) for dollar amount

Both of these could be played with to meet your needs. The first option is
much more flexible.

I have designed a tax receipt under Excel 2003. Each receipt uses 20 rows
and 8 columns and are laid 6 per A4 page. What I would like to know is how I
can get my tax year end summary workbook, which is a separate workbook, to
find the receipt no. cell and the total dollar value cell for a particular
person so I don't have to search manually and input the data.
I am using the Vlookup function for other stuff but I am stuck on this one
because the cells are not in the same row or column. There are a total of
150 receipts on the worksheet.
Example:
Receipt No. 001: Receipt No. 002:
Persons name is in B7 Persons name is in L7
receipt no. is in G1 Receipt No. is in Q1
Total dollar value is D15 Total dollar value is N15

Receipt No. 003: Receipt No. 004:
Persons name is in B27 Persons name is in L27
receipt no. is in G21 Receipt No. is in Q21
Total dollar value is D25 Total dollar value is N25

The pattern continues as each receipt takes 20 rows.
My tax end summary workbook:
Receipt no. cell(V2) looks up name cell (B2) and goes to my tax receipts
workbook. It looks up that name amongst the 150 receipts in both the B and L
columns to find the name. Once it is located, it has to read the cell that
has the receipt no. which will be in either column G or Q and whatever row no.
Same process again to get then the total dollar value.
Total dollar value cell (W2) looks up name cell (B2) goes to my tax receipts
workbook looks again for the name amongst the 150 receipts in both the B and
L columns to find the name. Once located it has to read the cell that has
the total dollar value in it which will be in either column D or N and
whatever row.
On the receipt workbook:
Is there a way to tell my name cell that its part of a group of cells so the
associated receipt no. cell and the total dollar value cell can be located.
Would grouping help? Those 8 columns and 20 rows need to be connected as a
set somehow so that when the Vlookup finds the name it will then know which
column and row the cell with the receipt no. in it for example would be.
Can anyone help or have I made this too complicated?

 

[Guest]

Hi bj,
Figured out what you meant and it works beautifully. Thanks so much
Sue

There are two ways I can think of.
My prefered way would be to set up a list of names and locations
Name1][B7
Name2][L7
Name 3][B27
etc
then use offset/indirect functions to gather data
=offset(indirect(vlookup(name,nameloclist,2,false)),-6,5,1,1) for receipt
number
and
=offset(indirect(vlookup(name,nameloclist,2,false)),8,2,1,1) for total
dollar amount.

the other way is using sumproduct
=sumproduct(--([wb]shnm!$B$7:$L$207=name),$G$1:Q201) for receipt
=sumproduct(--([wb]shnm!$B$7:$L$207=name),$D$15:$N$215) for dollar amount

Both of these could be played with to meet your needs. The first option is
much more flexible.

I have designed a tax receipt under Excel 2003. Each receipt uses 20 rows
and 8 columns and are laid 6 per A4 page. What I would like to know is how I
can get my tax year end summary workbook, which is a separate workbook, to
find the receipt no. cell and the total dollar value cell for a particular
person so I don't have to search manually and input the data.
I am using the Vlookup function for other stuff but I am stuck on this one
because the cells are not in the same row or column. There are a total of
150 receipts on the worksheet.
Example:
Receipt No. 001: Receipt No. 002:
Persons name is in B7 Persons name is in L7
receipt no. is in G1 Receipt No. is in Q1
Total dollar value is D15 Total dollar value is N15

Receipt No. 003: Receipt No. 004:
Persons name is in B27 Persons name is in L27
receipt no. is in G21 Receipt No. is in Q21
Total dollar value is D25 Total dollar value is N25

The pattern continues as each receipt takes 20 rows.
My tax end summary workbook:
Receipt no. cell(V2) looks up name cell (B2) and goes to my tax receipts
workbook. It looks up that name amongst the 150 receipts in both the B and L
columns to find the name. Once it is located, it has to read the cell that
has the receipt no. which will be in either column G or Q and whatever row no.
Same process again to get then the total dollar value.
Total dollar value cell (W2) looks up name cell (B2) goes to my tax receipts
workbook looks again for the name amongst the 150 receipts in both the B and
L columns to find the name. Once located it has to read the cell that has
the total dollar value in it which will be in either column D or N and
whatever row.
On the receipt workbook:
Is there a way to tell my name cell that its part of a group of cells so the
associated receipt no. cell and the total dollar value cell can be located.
Would grouping help? Those 8 columns and 20 rows need to be connected as a
set somehow so that when the Vlookup finds the name it will then know which
column and row the cell with the receipt no. in it for example would be.
Can anyone help or have I made this too complicated?