How to automatically link sheets in excel workbook

[james0902]

I am trying to link a mastersheet to 20 worksheets within the workbook; the mastersheet will feed the worksheets. I know this can be done manually by typing = in the cell in the worksheet and linking it to the mastersheet. However, I want excel to do this automatically. The data in the mastersheet is in the same column and I’d like cell A2 in the mastersheet to link to worksheet1 and cell A3 in the mastersheet to link to worksheet 2 and so on.
Currently I populate worksheet 2 with the forum =mastersheetA2 but when I try to copy and past this formula into the other worksheets (by copying the forum, selecting the other work sheets and pasting the forum) the formula =mastersheetA2 is populated in into all of my worksheets but I want each subsequent worksheet to capture the cell below in the mastersheeet i.e. =mastersheetA3 and =mastersheet A4. Anyone know how I can do this?

 

[xlbo]

...and how is excel supposed to know you want it to do that?

There is no way of doing this without at least having a lookup table that holds all the sheet names...and by the time you've created that, you may as well have linked the cells manually.

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[james0902]

you can tell excel to hold a part cell using the f5 key, isn't there a way to tell excel to select the cell below?

 

[xlbo]

you can tell excel to hold a part cell

I have no idea what you mean by that but you are not talking about changing cell references - telling excel to move to the cell below won;t get you anywhere with your issue - you are trying to equate moving down a column with moving across worksheets. What you could do with is a worksheet index reference that increments as you move through worksheets which you can compare to the row your formula is in. Unfortunately, unless you use VBA code, worksheets aren't referred to by index number so there is no way of incrementing the reference that you need

You either use code or a lookup table

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[james0902]

sorry, what i meant is that you can tell excel to hold a cell for a formula e.g. d$5$ - is it possible to tell excel that you want the next cell down!
Thanks for your help!!

 

[SkipVought]

$D5 holds the COLUMN absolute and the ROW relative.
D$5 holds the ROW absolute and the COLUMN relative.

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[AnotherHiggins]

Presumably your mastersheet contains data for different [customers/employees/departments/etc.] and you want to provide a report to each individual without giving them data on others.

Is that right?

Excel is GREAT at producing reports. But you have to give it some basic information to be able to use its myriad reporting tools.

What does your mastersheet look like? If you provide a simple example (using fake information, of course), we can better help you.

(You can use the [ignore][TT][/TT][/ignore] tags around your table to force it to display properly when you post.)

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

I'm not a big advocate of cell links like you describe.

Since you do have a master sheet (good for you, since many novice spreadsheet users try to get summary data from 20 sheets) it should be a piece of cake to generate 20 reports to 20 sheets, using PivotTable or MS Query, completely updateable, requiring no links at all.

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}