balances in a single worksheet
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 -- spartan111 |
balances in a single worksheet
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 |
All times are GMT +1. The time now is 01:43 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com