Autogenerated rows and links between multiple sheets

[CathyV]

I have some probably basic questions:
I have created a multi sheet document which records the plant species in a set location. Each sheet represents the data collected at a particular visit data. I have created a summary sheet to keep a running total of the different species frequencies in order to look at multiple years data at a glance and see trends. The summary sheet links back to each year's visit data (some of it). What I am trying to do is to create some sort of autogenerated set of cells to represent any new sheets and thus new years data as it is recorded, if it is possible. If not I would just like to know how I can change the summary sheet cells in question to link to a different sheet. This way I could at least copy some rows then make them look at the new data easily.

By the way I am pretty new at the program and not very savy as I have seen on this site already. Please be very descriptive in your responses because I am just a newbe.

Thanks for the help!!!!

 

[BrianB]

To link to another sheet you type = into the cell in the summary sheet, navigate to the cell you want to link to and click that. Press Enter. This makes a formula in the cell like :-
=Sheet1!A1
Or you can just type it in.

Concerning data, we all make the mistake, to start with, of looking at the process as summarising data from individual sheets. The process is managed much easier by adding all the data to a simple unformatted table with headings at the top and rows of data below. It may be necessary to add columns for dates or other calculations, but with Excel it usually means simply copying a formula down. Extracts and reports from this data can then be made extremely easily using the variety of powerful database tools available as standard - such as Pivot Tables and Data/Filter etc.

 

[CathyV]

Thank you for the reply.

I am looking for a short cut to transfer the link of multiple cells from one sheet to another in one step rather than changing the sheet for each cell in the summary table. Similar to changing selected cell's formatting. Or to create some type of autogenerated set of cells which will appear already linked to specified cells when I need another sheet for a new study date. I am building this for myself and another person to use, while trying to make it as simple and user friendly as possible for him.

As far as data organization goes...I'm not sure I could put all the data in one sheet for visual display purposes. The data is rather complex. Currently each data sheet has 7,830 cells(261x30) some of these are blank for visual spacing. Calculations occur in the sheets and then from here I want to look at minimum 102 cells from one sheet alone! Then I want to compare these figures between individual sheets (dates) to see trends over time. I don't know of a better way to do this without using a seperate summary sheet.

 

[BrianB]

Copy the original sheet then a simple Edit/Replace might do the job.

 

[CathyV]

This seemed to work nicely. I was able to copy and then edit/replace the sheet name with the one I wanted to link to. I had additional trouble with the software changing the cell descriptions when I copied them though. I think I resolved this issue by making all the cells "absolute rows and columns" i.e. addding the $ before the row or column.

Thanks for all the help.