VLOOKUP vs direct cell reference with linked spreadsheets

[Guest]

Hey all,

I've got a bunch of information about store producton that is spread across
various spreadsheets. For instance, I have a sheet "Sep" that has info for
September, with a row of data for each store. Each store has a unique store
number.

At the end of each row, there is a cell with a SUM() function. Say, for
example, this function falls in cell H10. There is another workbook with a
sheet for store production. To refer to the September production for that
store, there is a function in that cell that looks something like
=[043production.xls]Sep!$H10

That scheme is very finicky. If we add or delete a row (or do any sorting),
there is the potential to mess up the cell references. I'm thinking of
replacing those direct cell references with VLOOKUPS (for instance:
VLOOKUP(A10,[043production.xls]Sep!$A:$H,8). That way, sort order and row
insertion doesn't matter.

However, I have a feeling it would take a lot longer to update links this
way.

Is there any documentation about how long it would take to update a linked
VLOOKUP as opposed to a linked direct cell reference? We'd have about 800
rows, and 3 sheets with linked info.

 

[Guest]

I don't know of any studies or documentation.
However, you might consider using Names. This eliminates the need for cell
references.

 

[Guest]

What do you mean, use names? Named ranges? I'm not sure how that would be
helpful to me...

I don't know of any studies or documentation.
However, you might consider using Names. This eliminates the need for cell
references.

--
Robert Zimmer
Palm Bay, FL

Hey all,

I've got a bunch of information about store producton that is spread across
various spreadsheets. For instance, I have a sheet "Sep" that has info for
September, with a row of data for each store. Each store has a unique store
number.

At the end of each row, there is a cell with a SUM() function. Say, for
example, this function falls in cell H10. There is another workbook with a
sheet for store production. To refer to the September production for that
store, there is a function in that cell that looks something like
=[043production.xls]Sep!$H10

That scheme is very finicky. If we add or delete a row (or do any sorting),
there is the potential to mess up the cell references. I'm thinking of
replacing those direct cell references with VLOOKUPS (for instance:
VLOOKUP(A10,[043production.xls]Sep!$A:$H,8). That way, sort order and row
insertion doesn't matter.

However, I have a feeling it would take a lot longer to update links this
way.

Is there any documentation about how long it would take to update a linked
VLOOKUP as opposed to a linked direct cell reference? We'd have about 800
rows, and 3 sheets with linked info.