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?