Ask Experts Questions for FREE Help !
Ask
    varadhu's Avatar
    varadhu Posts: 14, Reputation: 1
    New Member
     
    #1

    Dec 7, 2012, 12:04 PM
    Linking of Excel sheets and updating the data from one sheet to all the sheets
    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
    Attached Files
  1. File Type: zip ask me help query.zip (8.3 KB, 24 views)
  2. JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #2

    Dec 12, 2012, 09:32 AM
    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's Avatar
    varadhu Posts: 14, Reputation: 1
    New Member
     
    #3

    Dec 15, 2012, 08:57 PM
    Quote Originally Posted by JBeaucaire View Post
    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's Avatar
    varadhu Posts: 14, Reputation: 1
    New Member
     
    #4

    Dec 15, 2012, 10:09 PM
    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%
    Attached Files
  3. File Type: zip ask me help query_modified.zip (9.4 KB, 17 views)
  4. JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #5

    Dec 17, 2012, 03:10 PM
    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's Avatar
    varadhu Posts: 14, Reputation: 1
    New Member
     
    #6

    Dec 19, 2012, 06:44 AM
    Quote Originally Posted by JBeaucaire View Post
    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

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

How to link multiple excel sheets to one main source excel sheet [ 7 Answers ]

I have a master price list and multiple ( individule ) sheets for customers.. I would like to , when input data in the price list to link and update to all other sheets... ( I do not want to have a source sheet with tabs) Thanks

Modulation of data in excel spread sheets [ 0 Answers ]

Dear members, I have attached an excel sheet named “Ask me model table” for reference: 1) In the attached table, I have presented the trade data of 10 specific shares for a day, as on 19.11.2012. 2) The table has four sheets. 3) In sheet no.1 named basic data, based on the HIGH, LOW values of...

Linking All Sheets in Workbook to 1 Sheet [ 1 Answers ]

I have an excel workbook with multiple sheets. My first sheet is a hyperlink list to all the different sheets. I would like to have a hyperlink in each sheet back to the list sheet. Is there a formula or simple way to do this without going to each sheet and manually doing it? I am thinking cell A1...


View more questions Search