Linking sheets

If I am understanding you problem correctly then type an "=" (without the quotes into the cell on sheet2, then navigate back to sheet1 and click on the cell you want to read from (C3 in your case) then hit ENTER. Your formula in the sheet2 cell should now look like: =sheet1!C3

Or you could just type =sheet1!C3 into your sheet2 cell.

I'm guessing you are going to end up with a lot of manual data copying. It might be possible to come up with a formula that you can drag across multiple cells to do things in one go, but we would need a description of what you are doing as well as an uploaded workbook to pursue that further.

Each teachers in sheet1 has different rating form as shown in sheet2. if the value in C3 is >2 then it should display in H9, if <3 then to G9, and so on. So your post #1 cannot be the solution.

It would help if you attached a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Don't try to use the Paperclip icon, as it doesn't work on this forum.

Hope this helps.

Pete

Kindly please check it again. I reuploaded it. thank you

Your uploaded workbook does not match the description in post #4.
Are you trying to link Observations!C6 to 'Teaching Competence'!M18 as implied by the yellow highlights in your uploaded workbook or to 'Teaching Competence'!M17 as implied by your attached image in post #1? I will assume the former.

I will also assume that you plan ultimately to add additional sets of columns to the 'Teaching Competence' worksheet, one set per teacher, starting in col-AG with Camille.

Please try the following formula in 'Teaching Competence'!L18 copied across to P18 and then copied again to the range AB18:AF18 (and ultimately to every student that you add).

Formula:

Please Login or Register  to view this content.

If you change scores in Observations row 6 for either Alice or Bernice then you should see those scores reflected in the correct column in the 'Teaching Competence' worksheet.


If you add additional teacher column sets to 'Teaching Competence' worksheet in exactly the same form as with Alice and Bernice then the above formula can be pasted into the appropriate columns.

Note that if you add or remove columns in 'Teaching Competence' within the column set of a particular teacher then the formula will need adjustment.

The attached workbook implements the above changes.

Let me know if this is heading in the right direction.

Just a couple of thoughts on my previous post . . .

The formula I suggest above is complex (although maybe someone can simplify). Much of the complexity comes from figuring out the appropriate column of 'Teaching Competence' in which to put the score. If you are just looking for a visually easy way to see the high and low scores then as an alternative you could explore using the Conditional Formatting "Data bars" capability.

I'm guessing that you will want to be mapping many more scores from different rows in "Observations". Unless I'm missing something I don't see any obvious means of associating rows in "Observations" with the corresponding row in 'Teaching Competence' - hence the hard coded 6's in my formula. There needs to be a unique key that can be used to match rows between the two worksheets. Without that you will have different formulas in every row which is going to be very hard to maintain.

I hope this helps

What does this part of formula mean? Why divide by 16 and multiply by 16?

Originally Posted by GeoffW283

Please Login or Register  to view this content.

In reply to post #12:

Why not simply the following in 'Teaching Competence'!L63 copied across and down to P65. You can then copy the same block of formulas to the next teacher.

Formula:

Please Login or Register  to view this content.

Second formula (averages): Place in Efficiency!F23 and copy across to J23. Then copy F23:J23 as-is to Bernice's scores at P23:T23 and so on for the remaining teachers.

Formula:

Please Login or Register  to view this content.

I'm still concerned that we're en route to creating an unmaintainable monster here!

See the attached updated workbook

Originally Posted by Simply_Me

What does this part of formula mean? Why divide by 16 and multiply by 16?

Responding to Simply_Me's post #13:

The question refers to the formula fragment:

Formula:

Please Login or Register  to view this content.

After dividing by 16 I take the integer part of the result and then multiply by 16 so the divide and multiply don't cancel. The result of INT((COLUMN()-1)/16)*16 as it is copied across columns is:

0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 16, 16, 16, 16, 16, 16, 16, 16, 16, 16, 16, 16, 16, 16, 16, 16, 32, 32, 32, 32, 32, 32 etc.

You have 16 columns per teacher, so for the first teacher, Alice, the OFFSET() function returns A1 which is the cell containing this teacher's name. I'd have liked to avoid all of this complexity and simply reference A1 directly to get the teacher's name, but then I couldn't copy the formula unchanged from 'Teaching Competence'L18 to M18, N18,O18 and P18. If instead I reference $A$1 then I don't have this particular problem, instead I have a problem when copying the formula to the next teacher. Now I don't want cell A1, I want cell Q1. The formula fragment INT((COLUMN()-1)/16)*16 solves this issue by generating an offset of 16 from A1 for all of Bernice's columns.

Originally Posted by GeoffW283

In reply to post #12:

Why not simply the following in 'Teaching Competence'!L63 copied across and down to P65. You can then copy the same block of formulas to the next teacher.

Formula:

Please Login or Register  to view this content.

Why did I not think of that...I was over thinking it I guess to the point of over looking at the obvious formula. Thank you again.

Originally Posted by GeoffW283

In reply to post #12:

I'm still concerned that we're en route to creating an unmaintainable monster here!

I think it will not be in my case. I will be having at most 12 teachers.

OK thanks for the feedback. If you’re all set then can you please mark the thread as solved. To do this select Thread Tools from the menu link above your first post and mark this thread as SOLVED. Thanks!

And thanks for the reputation points

I just manually entered the formula for each teachers in efficiency sheets (the one with color blue and orange in the attachment)

Below are the formula for 1 teacher only. So I had to do it for the rest of the teachers (colored blue in the attachment)

Please Login or Register  to view this content.

Please Login or Register  to view this content.

What I can't solve is when averaging is involved (colored orange in the attachment).

I am going to tackle these one by one in separate posts.

So (A) linking between Teaching Competence and Efficiency (Colored blue)
You are using the following simple formula for the linking:

Formula:

Please Login or Register  to view this content.

This only works if the number of columns per teacher in the "Efficiency" worksheet is the same as the number of columns per teacher in the "Teaching Competence" worksheet. There is no reason you can't make the columns consistent between all sheets except "Observations" as you already have varying numbers of unnecessary blank columns in each worksheet. Once you make the worksheets consistent then the above formula will work.

As a minor side note you could simplify the formula further to simply

Formula:

Please Login or Register  to view this content.

and get rid of unwanted 0's by formatting.

Now let me take a look at Part-2 . . .

Here's a response to the remaining two of your three issues:

(B) Gen Eval and Efficiency (Colored Orange)

The complex formula that you have in F16 that is returning #VALUE! can be replace with a simple formula like that discussed in post #22 above once the number of columns per teacher is made consistent across worksheets as also discussed in post #22.


(C)

Also the previous formula (colored green) works only in 2nd teacher and the same result in the 2nd teacher is copied on the 3rd teacher and so on

This one's my fault. The column() part of the complex formula in F23 needs to be counting columns on the GenEval worksheet not the Efficiency worksheet. I'n not sure I can quickly get my head around what might need even further complexity. However, if as suggested above, you make the number of columns per teacher consistent across all worksheets (except "Observations") then I believe that the current formula will work with only a minor change (replace all occurrences of 16 with however many columns per teacher you end up with).

In summary, the next step has to be for you to make the number of columns per teacher consistent between all of the worksheets (except "Observations"). Once that is done, if necessary, we can pick up the pieces if anything breaks in the process.

General question: how may more linkages between scores on different worksheets will there be? Many? If so I may have a couple of suggestions for how this might be handled more robustly.