Results 1 to 4 of 4

Thread: Reading Values from another sheet

  1. #1

    Reading Values from another sheet



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello from Raabi

    Would any geek help me in solving the following problem:

    I have two Worksheets: "sheet1" and "sheet2".
    In column "Value2" of the "sheet1", I need to put values from the corresponding row numbers and "event" column of the "sheet2".

    I tried, in vain, a formula, in each cell of the column "Value2" of the "sheet1"; similar to the following Pseudo syntax:

    Code:
    =CELL("value",sheet2!$B$Row())
    Please note that the two sheets are fairly large and frequently updated; and therefore just pasting the values from sheet2 is not a viable solution.

    Raabi

  2. #2
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Location
    Located near Abergavenny, South Wales, UK
    Posts
    113
    Articles
    0
    Hi
    You could try

    =Index(Sheet2!$1:$65535,match(row(),sheet2!$A:$A,match("event",sheet2!$1:$1,0))

    and copy down
    Regards

    Roger Govier
    Microsoft Excel MVP
    http://www.technology4u.co.uk

  3. #3
    Thank you so much, Roger, for your help.
    Although Excel reports some error "#REF", I will try to work it out. However, further tip in this direction will be appreciated.

    Regards

  4. #4
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Location
    Located near Abergavenny, South Wales, UK
    Posts
    113
    Articles
    0
    Hi

    My apologies - typing in the early hours of the morning is obviously not very productive!!!
    I made a complete hash of the formula.

    The formula should have been

    =INDEX(Sheet2!$1:$65535,MATCH(ROW(),Sheet2!$A:$A),MATCH("event",Sheet2!$1:$1,0))

    but I guess you amended the formula OK, otherwise you wouldn't have got anything.

    You would get #N/A errors if the number didn't exist in column A of sheet2, and a 0 if there was no value in the Event column of sheet2.

    Can you attach a copy of your workbook - click Go Advanced and then use the paper clip to attach the file.
    Regards

    Roger Govier
    Microsoft Excel MVP
    http://www.technology4u.co.uk

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •