linking data

[john cadagin]

I'm getting data from one spreadsheet to another. Right now I'm using
vlookup, but this is unwieldy because the ranges change each month.
The data look like this:

Store number Account name Amount Amount
2 Revenue 1 2
3 Revenue 2 3
4 Revenue 3 4
5 Revenue 4 5
6 Revenue 5 6

2 Payroll 1 4
3 Payroll 2 5
4 Payroll 3 6
5 Payroll 4 7
6 Payroll 5 8


etc.


I have another workbook that looks like this:
Revenue Wages rent etc
2
3
4
5
6

To get the data from the first to the second, I've been using vlookup.
But since the ranges change every month, this is awfully
time-consuming. Is there some way of doing this more efficiently?

Thanks

john

 

[Gord Dibben]

I don't think you have given enough information.

Is a "spreadsheet" a worksheet in same workbook or another workbook?

Where do wages, rent, etc come from?

How many items would "etc" encompass?

3 or 23?

Where are they located on source worksheet?

All I see are four columns in source sheet.

Please try to be more explicit rather than "etc"

In what way do the ranges change each month?


Gord Dibben MS Excel MVP

 

[john]

I don't think you have given enough information.

Is a "spreadsheet" a worksheet in same workbook or another workbook?

Another workbook.

Where do wages, rent, etc come from?

The source is a detailed income statement. It has roughly 60 lines per
store (it varies by location), about 40000 lines total.

How many items would "etc" encompass?

3 or 23?

There are 16 total items I want, from columns C,D, H and I.

Where are they located on source worksheet?

The store number is in A, the account descrition is in B, and the
numbers I want to pull as above. While doing the vlookups, I've sorted
the source workbook by column B and I then look up A. That's why I
have to change the ranges manually each month.

EG, this month total wages is in rows 39696 to 40449. So my formula is

=VLOOKUP($A7,[1011stores.xls]sort!$A$39696:$I$40449,3,FALSE)

All I see are four columns in source sheet.

Well I figured if I could get data from column C, I could get them
from H as well.

Please try to be more explicit rather than "etc"

The report I'm populating has store # in A, descriptions in Cols B-F,
and the numbers I'm looking for in G-V, namely

Revenue
Rent
Man Fee
Man Fee 2
Expenses
Profit
Wages
Wages %

for the month and YTD.

In what way do the ranges change each month?

Stores open. Stores may have new accounts. The number of lines in any
statement isn't fixed, so the number of lines in the file changes
every month

I hope this is enough info. Thanks for the reply.