Autogenerated rows and links between multiple sheets

CathyV

New Member
Joined
Feb 19, 2004
Messages
3
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!!!!
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
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

New Member
Joined
Feb 19, 2004
Messages
3
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

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
Copy the original sheet then a simple Edit/Replace might do the job.
 

CathyV

New Member
Joined
Feb 19, 2004
Messages
3
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,684
Messages
5,597,530
Members
414,152
Latest member
ReservoirDodds

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top