balances in a single worksheet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

spartan111 - 12 Mar 2008 06:09 GMT

Hello everyone,

I have 26 customer account excel files on a network drive (shared) and
I would like to create an excel file on my computer that shows all of
my customer balances in a single worksheet. I tried doing this by using
links.

My problem is that every customer's balance changes cell daily. For
example if customer A's balance value was in cell H6, the next day it
will be in cell H7 and so on. Therefore the links in my computer excel
file are not updated.

I would really appreciate it if anyone could help out,

Thanx in advance,

gianni

Signature

spartan111

Reply to this Message

Bill Manville - 12 Mar 2008 08:43 GMT

Spartan111 wrote:

> My problem is that every customer's balance changes cell daily.

Accessing a variable cell via a link is unwise.
Even using the address of a cell is not ideal; a range name is the best
idea.
I would create a formula in a fixed cell in each customer account file
which contains the current balance.

For example, in J1 - to which you might give the range name
"CurrentBalance", put the formula =OFFSET(H1,COUNT(H:H),0)
This will reference the current balance if you have headings in row 1
and figures in each row below with the last figure being the current
balance - if that is not your layout you may need to modify the
formula.

Then in your summary workbook you can use links such as
='F:\Account Files\[AccountA.xls]'!CurrentBalance

Bill Manville
MVP - Microsoft Excel, Oxford, England
Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Reply to this Message

Rate this thread: