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.
"Sue" wrote:
> 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?
>
Bookmarks