Summary page linking to cells not data

I'm sure this is up here somewhere but I'm trying to build a spreedsheet that details hard drive usage. I'll upload what I have so far, but I have 3 sheets inside the workbook, summary, disktotals, server1.

My thought process was enter in the data into the server1 sheet, then use a funtion on the summary page such as "=(server1!b2)" to display that data. Then when the next month comes around, I would be able to insert a new line infront of last months data (in sense a new row 2) and not have to make any changes on the summary page and have the updated data displayed.

The problem comes when I go to insert a new line into Server1, my formula changes on the summary page from b2 to b3 (to accomidate the new line added).

Is there a way to lock down that summary page so that the forumulas wont change when I make changes with the other sheets? In sense always pull data from Server1!b2, not the data that origionally was associated with it.

Current workbook with forumulas and data attached or available for download at:
http://www.networkfabrication.com/Server1_test.zip

Any help would be much appriciated!

Hi

Try using the INDIRECT function.

summary!C3: =INDIRECT("Server1!B2")

This will always point to server1!B2 even if you add a new row.

HTH

rylo

After much tinkering around and reading many posts on this site with the guideline of indirect, I believe I was able to get all of my functions working. that last problem with multiple links I was able to get working like this:

=((INDIRECT("server1!B2"))/(INDIRECT("server1!B3"))-1)/(INDIRECT("DiskTotals!B2"))

if there is a better way of writing out the code that would be nice to know but for now I guess this is working.

Thanks for the help!

Hi

Only other way I can think of would be to use VLOOKUP on B1. This would have the same effect. Don't know that the formulas would be any easier to read though....


rylo