Hello All,
I have a semi-complicated workbook that encompasses about 11 sheets all referencing one another. The main sheet is basically a sum of all the information from the other sheets. We are using this sheet to calculate the counters from the copy machine to charge each individual separatly. However, not all users pay the same price per copy (so the numbers get a little complicated). The problem comes in because I need to update the sheet with four new users (users are based on a number) that need to be placed in numerical order with the existing users. Numbers 225, 461,969, and 7937.
Example of Formula:
=SUM('Canon IR7095 Back Office'!C57,'Canon IR3030 6th Flr.'!B57,'Canon IR5065 7th Flr.'!B57,'Canon IR3320N 7th Flr.'!
This perticular formula needs all references to reference B57 which is the line that it is on in the spreadsheet which is currently correct. Now when I try to insert a new row (whether it be copy/pasted, brand new, above or below) this formula will not change, even though the line has changed to 58 or 56 (depending on whether it was inserted above of below). When this happens it messes up all the totals on the main sheet.
Does anyone know how to get this to update itself when a new row is inserted? Or a better way to add these new users? I'm really at wits end with this and its more than a days work if I have to manually update each line... Its over 200 users. I have a deadline, please help!!!
Using Excel 2007, on Windows XP Pro
I have a semi-complicated workbook that encompasses about 11 sheets all referencing one another. The main sheet is basically a sum of all the information from the other sheets. We are using this sheet to calculate the counters from the copy machine to charge each individual separatly. However, not all users pay the same price per copy (so the numbers get a little complicated). The problem comes in because I need to update the sheet with four new users (users are based on a number) that need to be placed in numerical order with the existing users. Numbers 225, 461,969, and 7937.
Example of Formula:
=SUM('Canon IR7095 Back Office'!C57,'Canon IR3030 6th Flr.'!B57,'Canon IR5065 7th Flr.'!B57,'Canon IR3320N 7th Flr.'!
This perticular formula needs all references to reference B57 which is the line that it is on in the spreadsheet which is currently correct. Now when I try to insert a new row (whether it be copy/pasted, brand new, above or below) this formula will not change, even though the line has changed to 58 or 56 (depending on whether it was inserted above of below). When this happens it messes up all the totals on the main sheet.
Does anyone know how to get this to update itself when a new row is inserted? Or a better way to add these new users? I'm really at wits end with this and its more than a days work if I have to manually update each line... Its over 200 users. I have a deadline, please help!!!
Using Excel 2007, on Windows XP Pro
