[varadhu]

Hi guys!

I have a problem. I want all of your help in solving the problem. For the purpose, I have created a sample sheet and enclosed:
The sample sheet contains four worksheets in it.
The first worksheet titled “schools performance” is the master sheet and all the next three work sheets (sub sheets) were created out of the master sheet.
Now the requirement is
1) I want to link all the worksheets and
2) as I start updating the data in the master sheet( school performance), I want the data to get updated simultaneously in all the other three sub-sheets.

Note: Please also guide whether the solution you provide would work only if all the sheets( sub sheets) are arranged within the same excel sheet ( as arranged in the sample sheet) or otherwise even if the sub sheets are placed as individual excel sheets(separate sheets)

Thanking you
Varadhu

[JBeaucaire]

You can use VLOOKUP for this.

In B2 put this formula:

=VLOOKUP($A2, 'Schools performance'!$A:$G, COLUMN(), 0)

Now copy B2 down and across the table.


Once you have the other sheets working, you can move those sheets to another workbook. When you move the sheets, be sure to SAVE the workbook with the raw data sheet first, then save/close the new workbook with the moved sheeets. This will properly update the link to the original data.

[varadhu]

You can use VLOOKUP for this.

In B2 put this formula:

=VLOOKUP($A2, 'Schools performance'!$A:$G, COLUMN(), 0)

Now copy B2 down and across the table.


Once you have the other sheets working, you can move those sheets to another workbook. When you move the sheets, be sure to SAVE the workbook with the raw data sheet first, then save/close the new workbook with the moved sheeets. This will properly update the link to the original data.

Excellent sir,

It has worked as I was wanting in my mind,

My Hearty Thanks and I will expand the solution provided by you for my
Daily needs!

You have done it once again for me sir,

Thanking you once again and
Wish you a Happy Christmas,
Happy New Year 2013+++
Varadhu

[varadhu]

Respected Sir,

An Additional help required please!

I want to modify the solution provided by you in furtherance of my requirement. Please do help on the same.

I once again, attach a modified query sheet please. In all the three sub sheets, I have inserted additional columns with data relating to previous years on the same subject (shown in blue cell colours). The parent sheet (Schools performance) remains the same and unaltered.

Now when I apply the formula provided by yourself in the sub-sheets in current data columns (column H to column M), the formula does not pick up the reference and was not giving the desired result(i.e. linking and updating of information from the parent sheet viz. "school performance").

Whether any changes are to be made in the original formula to obtain the result to this type of modification? Please help.

Even if you consider this sheet%

[JBeaucaire]

You need to read up and fully understand the VLOOKUP() formula. Press F1 and fully read up on it, make sure you understand what each of the parameters is in the formula and exactly what it is doing.

The COLUMN() trick I added in the 3rd parameter may be confusing. The 3rd parameter is simply a number indicating which column in the range of columns defined by 2nd parameter that you want to return a value from.

A normal vlookup usually looks like this:

=VLOOKUP($A2, Sheet1!$A:$C, 3, 0)

In that formula, the value in A2 is looked up in column A of Sheet1, then value from column C is returned because C is the 3rd column in the defined range A:C.

=VLOOKUP($A2, Sheet1!$P:$Z, 2, 0)

This version would search column B and return a value from column Q.

Now, with that, go back and fix your VLOOKUP formulas to account for the added columns you inserted.



The COLUMN() trick I added inside the VLOOKUP may be too much right now.

[varadhu]

You need to read up and fully understand the VLOOKUP() formula. Press F1 and fully read up on it, make sure you understand what each of the parameters is in the formula and exactly what it is doing.

The COLUMN() trick I added in the 3rd parameter may be confusing. The 3rd parameter is simply a number indicating which column in the range of columns defined by 2nd parameter that you want to return a value from.

A normal vlookup usually looks like this:

=VLOOKUP($A2, Sheet1!$A:$C, 3, 0)

In that formula, the value in A2 is looked up in column A of Sheet1, then value from column C is returned because C is the 3rd column in the defined range A:C.

=VLOOKUP($A2, Sheet1!$P:$Z, 2, 0)

This version would search column B and return a value from column Q.

Now, with that, go back and fix your VLOOKUP formulas to account for the added columns you inserted.



The COLUMN() trick I added inside the VLOOKUP may be too much right now.

Sir,

I will try your suggestions and revert back,

Meanwhile, I Thank You Sir for your immediate response,

varadhu