Linking Worksheets 4

[vince1209]

I am trying to create a spreadsheet where I have several tabs or worksheets in the spreadsheet. How can I create a formula from a worksheet to link to another worksheet? For instance, I have a master spreadsheet as one worksheet and I want to update that spreadsheet by inputting data into the other worksheets that will automatically update the master worksheet.

 

[GlennUK]

In the master sheet type = and then navigate to the sheet and cell that you want to link to.

Or are you asking for something more complicated?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[vince1209]

Thanks, GlennUK for your tip :

In the master sheet type = and then navigate to the sheet and cell that you want to link to.

Or are you asking for something more complicated?

I was able to do that for the first tab and link cells to the main worksheet. I have about 27 worksheets that I want to link to the main worksheet. Do I have to follow the same procedure?

 

[SkipVought]

"I have about 27 worksheets that I want to link to the main worksheet. "

What do you mean my LINK? is this scattered data or are these tables that will ALL stack, one under the other on the Master sheet?

There is probably a better way!

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[vince1209]

Thanks Skip:

It is scattered data on different worksheets and I want to be able to input data in the other 27 worksheets that will automatically be on the main worksheet. Like I mentioned earlier I was able to do that for the first worksheet and was able to see the data on the main worksheet. I have about 27 worksheets that I want to accomplish the same thing. Do I have to follow the same procedure?

 

[SkipVought]

Yup!

What are these 27 sheets?

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[vince1209]

here is the scenario: I have a master spreadsheet of employee information concerning merits. I have a main worksheet with all 79 employees who are getting a merit or bonus. I split the data up by VP level; meaning that I have 27 different VP's that will get a spreadsheet with data that they will view for their employee(s). That is the reasoning for the 27 different spreadsheets. Once the VP's look at their employees past year performance and determine what percentage they will fill out the part on their spreadsheet and then send it back to me and I will update my master spreadsheet. I just want to be able to input the data given to me on my master on thier worksheet as it updates the main worksheet.

 

[SkipVought]

" I split the data up by VP level; meaning that I have 27 different VP's that will get a spreadsheet with data that they will view for their employee(s)."

THAT is the crux of the great difficulty. It is very difficult, as you are currently experiencing, to gather data from similar sheets. It is much MUCH MUCH easier to summarize by VP from ONE SINGLE TABLE.

You have the cart before the horse, a typical mistake made my novice spreadsheet users.

I'd strongly recommend consolidating ALL your employee merit data, including a column for VP. From such a properly structured table, you will be able to so much more with less wasted time and effort.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[Gavona]

I have about 27 worksheets that I want to accomplish the same thing. Do I have to follow the same procedure?

Having created the formulae for the first workbook you should be able to copy the formulae. Then with these copies of the formulae selected use Edit, Replace to change them to refer to the second workbook.

Gavin

 

[SkipVought]

Start by posting a typical sample from one of the 27 sheets.

Then show how that data is summarized on the master.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[Salut39]

if you list all your sheetNames in the colunm A then in B use INDIRECT formula which will bring references from the other sheets.

Yuri

 

[Salut39]

This is an example but in this case sheet name in B1 and criteria for sum is in A2, formula in B2

=SUMIF(INDIRECT( "'" & B$1 & "'" & "!$H:$H"),$A2,INDIRECT( "'" & B$1 & "'" & "!$E:$E"))

 

[vince1209]

I am having a problem with the several of the worksheets in my master spreadsheet. As I mentioned I was able to create the formula from the Master worksheet and the first worksheet. When I tried to do the same procedure for the next worksheet I was not able to get the formula to work as I did for the first worksheet. Is it possible to send you my spreadsheet so that you can understand what I am trying to do?

 

[SkipVought]

Please distinguish between WORKBOOK and WORKSHEET. Spreadsheet is a somewhat vague term that could refer to either.
[tt]
Select a cell in the Master Worksheet and enter the EQUAL SIGN.

Without doing AMYTHING ELSE, select the worksheet tab of interest and then the range on that worksheet.

NOW hit the ENTER key[/tt]

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[AnotherHiggins]

I'd like to second what Skip said: Consolidate your data into a single table on a single worksheet.

Once you have done that, creating the various sheets (which are acting as reports) for each of the VPs will be simple. Unlike what you're running into now.

Believe me, I know from experience that it is much better to start off with properly-stored data.

[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]

creating the various sheets (which are acting as reports) for each of the VPs will be simple.

You could get a VP's report in mere SECONDS from a single source table! LITERALLY, SECONDS!!!

Sticking with this dredful structure will be a chronic and acute series of HOURS & HOURS of headaches, blood sweat and tears.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[dallen43]

I was reading the thread and wondered, if you combine all the data into a single sheet, how easy would it be to update?

"Once the VP's look at their employees past year performance and determine what percentage they will fill out the part on their spreadsheet and then send it back to me and I will update my master spreadsheet."

Thanks,
Deb

 

[SkipVought]

A far sight easier than having the headache of 27 different sheets.

What happens when employees of a VP come and go? MAINTENCE NIGHTMARE!

What happens when the CEO restrucrures the company, acquires other companies, etc. MAINTENCE NIGHTMARE!

So you "duplicate" the entry effort, at least until your company grows to the point where they get a PeopleSoft or the like application. Small price to pay.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[vince1209]

I want to thank everyone for your assistance. I was able to do what I needed to do. I have another question. On my spreadsheet I call a column with a employees name i.e John,Doe. I want to create another column and separate the first and and last name. I created a formula =RIGHT(A2,5) which gives me the last 5 characters of the employees first name. By the names being difeerent in each cell what is a formula I can write that will separate the name at the comma?

 

[SkipVought]

check out Data > Text to columns - DELIMITED using the COMMA. You will need an enpty column.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}