Rookie at linking - need a tudor!!!

[The Smuffer]

I run a Hockey League website and input stats using excel. I have a
spreadsheet for each team (one for players and one for goalies) plus
league stats as well. I have been adding the data in each sheet
seperatly but know there is a way to just enter them once. Can someone
walk me through this? The website is www.smbghl.com if you want to take
a look and see what I'm talking about.

Thanks

 

[Ken Johnson]

Hi The Smuffer,
Two simple examples are:

Example1.
Say you want whatever you type into A1 on Sheet 1 to also appear in B1
on Sheet 2, then..
1) Click on B1 on Sheet 2 then type an equals sign. DON'T hit Enter.
2) Click on the Sheet 1 tab (towards the bottom of the screen).
3) Click on A1 on Sheet 1.
4) Now hit Enter. Excel should automatically jump back to Sheet 2 and
you will see a zero in B1.
5) If you don't want a zero when A1 on Sheet 1 is empty then you will
have to change the formula that you see in the formula bar when Sheet 2
B1 is selected. That formula at the moment is =Sheet1!A1 ( or 'Sheet
Name'!A1 if your first sheet has a two word name eg 'All Saints'!A1).
Change the formula to = IF(Sheet1!A1 = "","",Sheet1!A1)

Now, when Sheet1 A1 is blank so is Sheet B1 and what ever you type into
Sheet 1 A1 will automatically appear in Sheet B1.

Example 2.
Say you want what ever you type into Sheet 1 A1 to appear in Sheet 2
B1, Sheet 3 B1 and Sheet 4 B1 then...
1) Click on the Sheet 2's sheet tab.
2) Hold down the Shift key while you then click on Sheet 4's sheet tab.
This groups together Sheets 2, 3 and 4.
3) Repeat steps 1) to 5) of Example 1.

If you want different cells in multiple sheets to be linked to the same
cell on sheet 1 then they will all have to be done separately. However,
if you want say B1:B20 in one sheet (or a group of sheets) to be linked
to the same number of column cells on another sheet then you only have
to follow the above steps for the top cell, then fill down to copy the
linking formula to all the other cells in the column.

Hope this helps.

Ken [Henry VIII] Johnson

 

[Peo Sjoblom]

Hope this helps.

Ken [Henry VIII] Johnson

LOL! I thought you Tudors had died out?

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon

 

[Ken Johnson]

Hi Peo,
It's nice to know someone out there appreciates my humour!
Ken Johnson

 

[The Smuffer]

Thanks for the help, that was pretty simple. I created a master player
worksheet with all the players from my league. I also created seperate
team worksheets for all my teams. I now have them linked together.

My next question is: If I need to add a new player is there a way to
only add him once on the master and have it automaticly show up on the
team sheet, or visa versa?

 

[Ken Johnson]

Hi Smuffer,
If the new player is an extra player, not a replacement for a departing
player, the team sheet could have a linking formula in the next vacant
cell of the team's list of player names. This linking formula should
link that cell to the cell on the master sheet where you will be
entering the new player's name. So it's just a matter of having extra
linked cells on your team sheets linked back to empty cells on your
master sheet. If you are using the IF version of the linking formula
then these extra linked cells on your team sheets will just show up as
blank cells until you add a new player's name to the master sheet.

I hope this makes sense.

Ken Johnson

 

[The Smuffer]

Thanks a bunch so far, I'm almost where I want to be.

Hopefully this last question will finish off this Thread.

How do I make my worksheets Auto Sort. Takes a lot of time to go back
and sort each page individually each time I enter data.

I know it can be done but haven't found a way online that I've
understand yet.

 

[Ken Johnson]

Hi Smuffer,
Not knowing the structure of your workbook, or the steps you carry out
when you do your sorting, makes it very difficult for me to advise you
about this.
However, have you ever recorded a macro?
The macro recorder is the easiest way to automate repetitive tasks such
as sorting a number of worksheets. All the steps you go through to
manually sort your sheets can then be condensed down to a keyboard
shortcut or a button click.
If you haven't done much macro recording then I suggest you make a
backup copy of your workbook then experiment to see how it goes.
Just before you are about to do the sorting go Tools>Macro>Record New
Macro...
The Record Macro dialog, that then appears, has a box where you type in
a name for the macro. Some characters are not allowed eg space, but
Excel lets you have as many goes as you need to get it right. A
descriptive name is best eg SortTeamSheets.
Also, if you want your macro to run after you press a certain key
combination you can enter that key combination into the Shortcut key
box. Avoid any key combinations that you already use such as Ctrl + s,
which is reserved for saving, and will be lost. Ctrl + Shift + s would
be a better choice.
There is also a box for telling Excel where to store the macro. The
default This Workbook is the usual place, so that can be left alone.
The last box is where you can type a description of your recorded
macro. I usually don't bother, you only see the description when you
view the macro code in the Visual Basic Editor.
After clicking OK a stop recording macro dialog appears. This tiny
dialog has a blue Stop button and a Relative Reference button.
The Relative Reference button is tricky for two reasons 1) it is
difficult to tell whether it is on or off, and 2) it affects the way
your macro works.
If the button's edges are a lighter colour than the dialog's background
colour then Relative Reference is turned on.
To understand the effect it has consider the following trivial sequence
of recorded steps:
1) select A1
2) Go Tools>Macro>Record New Macro... etc to start recording
3) Turn the Relative reference on
4) Drag the contents of A1 down to A2
5) Click the blue Stop button on the Stop Recording macro dialog

Now, if you select B1 then run your macro you will see the contents of
B1 move down to B2, because Relative reference was turned on before
recording the macro.
If Relative Reference was turned off before you recorded your macro,
selecting B1 then running the macro (Absolute Reference) you would see
A1 selected then A1's contents would be dragged down to A2.

Anyhow, for your purposes I think you will require Relative Reference
be turned off so that your macro always sorts the same columns, rather
than have the columns to be sorted depend on the cell that happened to
be active just before your macro is run.

After you have recorded all your steps and pressed the stop button, you
can run the macro using the Shortcut you nominated or you can go
Tools>Macro>Macros... then select it from the list so that its name
appears in the top Macro name: box then click Run. If you click Edit
you will be taken to the VBA Editor where you can view and alter the
code produced by the macro recorder.
Another way is to place a button (I use only the button from the Forms
toolbar so that my macros can work on PC and Mac, Mac doesn't have
Control buttons) on the master sheet, but I won't go into that right
now, I think I've said enough, and who knows, you might know this
stuff already.
Hope this all makes sense.

Ken Johnson

 

[The Smuffer]

Thanks for all your help. What I did is created some marco's and inputed
the data into the VBA so that the linked pages would sort the data
automatically as I enter data into the master worksheet. I'm not 100%
sure how I did it but copyied the format from another post.

Thanks again for all your help. I now have my Workbook functioning like
I always wanted it too and is saving me a lot of work!

End of Thread!!!!

 

[Ken Johnson]

Hi Smuffer,
You're welcome.
It's nice to know you've had success.
Thanks for the feedback.
Ken Johnson