I have an extensive spreadsheet that contains tables of data for account
names by year. I would like to have a cell return data from the tables when
the account name and applicable year are typed into a worksheet. For
example, in workbook 1, separate 4 x 3 tables are set up for Accounts "A"
thru ""G", for years "1994" -"2004" (77 tables in all--7 accounts/11 years).
In workbook 2, when the user types in "A" in A1 and "2004" in A2, I would
like A3 to show the applicable table. If I have to reproduce the table cell
by cell that's OK. I just need help with reproducing the cell results from
the applicable table when the appropriate account/year are entered. I can't
nest IF's because I have too many conditions to test for.

Any ideas?

Ellie,

How are your 77 tables arranged? Tell uswhether they are on separate sheets, single sheet, what the
cell locations are, etc.

It is likely that some combination of INDEX, MATCH, VLOOKUP, etc. will do the trick.

HTH,
Bernie
MS Excel MVP


"elliekev26" wrote in message
...
I have an extensive spreadsheet that contains tables of data for account
names by year. I would like to have a cell return data from the tables when
the account name and applicable year are typed into a worksheet. For
example, in workbook 1, separate 4 x 3 tables are set up for Accounts "A"
thru ""G", for years "1994" -"2004" (77 tables in all--7 accounts/11 years).
In workbook 2, when the user types in "A" in A1 and "2004" in A2, I would
like A3 to show the applicable table. If I have to reproduce the table cell
by cell that's OK. I just need help with reproducing the cell results from
the applicable table when the appropriate account/year are entered. I can't
nest IF's because I have too many conditions to test for.

Any ideas?

The tables are essentially 3 columns by 4 rows and and simply stacked on top
on one another with a few emply cells in between. For example,

1st table (Account A, 2004) covers L15:N18
2nd table (Acount A, 2003) covers L32:N35
3rd table (Account A, 2002) covers L40:N43
....
77th table (Account G, 1994) covers L222:N225

The spaces (empty cells) between the tables are not uniform due to other
data on the spreadsheet. My goal is for the user to use a separate workbook
to input the Account name in A1, the year in B1 and have the appropriate
table appear in C1:E4

Thanks,

Kevin



"Bernie Deitrick" wrote:

Ellie,

How are your 77 tables arranged? Tell uswhether they are on separate sheets, single sheet, what the
cell locations are, etc.

It is likely that some combination of INDEX, MATCH, VLOOKUP, etc. will do the trick.

HTH,
Bernie
MS Excel MVP


"elliekev26" wrote in message
...
I have an extensive spreadsheet that contains tables of data for account
names by year. I would like to have a cell return data from the tables when
the account name and applicable year are typed into a worksheet. For
example, in workbook 1, separate 4 x 3 tables are set up for Accounts "A"
thru ""G", for years "1994" -"2004" (77 tables in all--7 accounts/11 years).
In workbook 2, when the user types in "A" in A1 and "2004" in A2, I would
like A3 to show the applicable table. If I have to reproduce the table cell
by cell that's OK. I just need help with reproducing the cell results from
the applicable table when the appropriate account/year are entered. I can't
nest IF's because I have too many conditions to test for.

Any ideas?

KEvin,

You don't say where your labels a I will assume that a label (Account A, etc) for accounts appear
in column J, and that the year label is in column K. IF that isn't the case, change the column
letters in the next formula as appropriate.

In cell B1, array enter (enter using Ctrl-Shift-Enter) the formula

=SUM(MIN(IF((J1:J225=A1)*(K1:K225=B1),ROW(J1:J225) )))

Then in C1, enter the formula

=INDEX(L:L,$B$2+ROW()-1)

and copy cell C1 to C1:E4.

HTH,
Bernie
MS Excel MVP


"elliekev26" wrote in message
...
The tables are essentially 3 columns by 4 rows and and simply stacked on top
on one another with a few emply cells in between. For example,

1st table (Account A, 2004) covers L15:N18
2nd table (Acount A, 2003) covers L32:N35
3rd table (Account A, 2002) covers L40:N43
...
77th table (Account G, 1994) covers L222:N225

The spaces (empty cells) between the tables are not uniform due to other
data on the spreadsheet. My goal is for the user to use a separate workbook
to input the Account name in A1, the year in B1 and have the appropriate
table appear in C1:E4

Thanks,

Kevin



"Bernie Deitrick" wrote:

Ellie,

How are your 77 tables arranged? Tell uswhether they are on separate sheets, single sheet, what
the
cell locations are, etc.

It is likely that some combination of INDEX, MATCH, VLOOKUP, etc. will do the trick.

HTH,
Bernie
MS Excel MVP


"elliekev26" wrote in message
...
I have an extensive spreadsheet that contains tables of data for account
names by year. I would like to have a cell return data from the tables when
the account name and applicable year are typed into a worksheet. For
example, in workbook 1, separate 4 x 3 tables are set up for Accounts "A"
thru ""G", for years "1994" -"2004" (77 tables in all--7 accounts/11 years).
In workbook 2, when the user types in "A" in A1 and "2004" in A2, I would
like A3 to show the applicable table. If I have to reproduce the table cell
by cell that's OK. I just need help with reproducing the cell results from
the applicable table when the appropriate account/year are entered. I can't
nest IF's because I have too many conditions to test for.

Any ideas?

Sorry, I missed the part about the separate worksheet. Get the formulas working on the same
worksheet, then select the range, cut and paste it into a clean worksheet.

HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
KEvin,

You don't say where your labels a I will assume that a label (Account A, etc) for accounts
appear in column J, and that the year label is in column K. IF that isn't the case, change the
column letters in the next formula as appropriate.

In cell B1, array enter (enter using Ctrl-Shift-Enter) the formula

=SUM(MIN(IF((J1:J225=A1)*(K1:K225=B1),ROW(J1:J225) )))

Then in C1, enter the formula

=INDEX(L:L,$B$2+ROW()-1)

and copy cell C1 to C1:E4.

HTH,
Bernie
MS Excel MVP


"elliekev26" wrote in message
...
The tables are essentially 3 columns by 4 rows and and simply stacked on top
on one another with a few emply cells in between. For example,

1st table (Account A, 2004) covers L15:N18
2nd table (Acount A, 2003) covers L32:N35
3rd table (Account A, 2002) covers L40:N43
...
77th table (Account G, 1994) covers L222:N225

The spaces (empty cells) between the tables are not uniform due to other
data on the spreadsheet. My goal is for the user to use a separate workbook
to input the Account name in A1, the year in B1 and have the appropriate
table appear in C1:E4

Thanks,

Kevin



"Bernie Deitrick" wrote:

Ellie,

How are your 77 tables arranged? Tell uswhether they are on separate sheets, single sheet, what
the
cell locations are, etc.

It is likely that some combination of INDEX, MATCH, VLOOKUP, etc. will do the trick.

HTH,
Bernie
MS Excel MVP


"elliekev26" wrote in message
...
I have an extensive spreadsheet that contains tables of data for account
names by year. I would like to have a cell return data from the tables when
the account name and applicable year are typed into a worksheet. For
example, in workbook 1, separate 4 x 3 tables are set up for Accounts "A"
thru ""G", for years "1994" -"2004" (77 tables in all--7 accounts/11 years).
In workbook 2, when the user types in "A" in A1 and "2004" in A2, I would
like A3 to show the applicable table. If I have to reproduce the table cell
by cell that's OK. I just need help with reproducing the cell results from
the applicable table when the appropriate account/year are entered. I can't
nest IF's because I have too many conditions to test for.

Any ideas?