Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Jul 2009
    Location
    North Carolina, the Beach
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am unable to reference a worksheet in the same workbook with muliple cells. I would like to use the autofil if possible, also.

    Any suggestions?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Welcome to the Lounge!

    The easiest way to refer to cells on another sheet in a formula is to point to them. Let's say that you are on Sheet1 and you want to sum cells A1:E1 on Sheet2. Here is a step-by-step description:

    - Select the cell on Sheet1 where you want the formula.
    - Type = to start a formula.
    - Type SUM( to specify that you want to sum cells.
    - Click on the sheet tab of Sheet2. This will activate Sheet2.
    - Select cells A1:E1.
    - Type ) and press Enter or click the red check mark to confirm the formula.

    The cell where you entered this should now contain the formula

    =SUM(Sheet2!A1:E1)

    If you fill this down, the cell references will be adjusted automatically.

    Post back with more details if this doesn't solve your problem.

  3. #3
    New Lounger
    Join Date
    Jul 2009
    Location
    North Carolina, the Beach
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    thank You so much.
    Unfortunately I am not working with numbers I can sum, I am working with dates.

    Wkshet 1 (A:18) are housing addresses and wkshet 2 (E2,E3) are renovation dates and reactivated dates with the corresponding address in (A1). They are not lined up in a way that I can copy and paste, and I recently took an 03 excel class, but nothing is coming back to me... I checked all the books from, class.. I am missing something

    Bianca






    [quote name='HansV' post='787036' date='31-Jul-2009 16:01']Welcome to the Lounge!

    The easiest way to refer to cells on another sheet in a formula is to point to them. Let's say that you are on Sheet1 and you want to sum cells A1:E1 on Sheet2. Here is a step-by-step description:

    - Select the cell on Sheet1 where you want the formula.
    - Type = to start a formula.
    - Type SUM( to specify that you want to sum cells.
    - Click on the sheet tab of Sheet2. This will activate Sheet2.
    - Select cells A1:E1.
    - Type ) and press Enter or click the red check mark to confirm the formula.

    The cell where you entered this should now contain the formula

    =SUM(Sheet2!A1:E1)

    If you fill this down, the cell references will be adjusted automatically.

    Post back with more details if this doesn't solve your problem.[/quote]

  4. #4
    New Lounger
    Join Date
    Jul 2009
    Location
    North Carolina, the Beach
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you so much for your help. Unfortunately I am not working with numbers I can sum, working with dates.

    In wksht 1 (A:18) housing addresses and wkshet 2 (E:2,E:3) are renovations dates and reactivated dates with the corresponding addresses.

    my goal is to get all the dates from wk sht 2 into the proper columns and rows in wkst 1.

    I recently took an 03 excel class, but nothing is coming back to me...I know I am missing something. Again thank you.

    [quote name='HansV' post='787036' date='31-Jul-2009 16:01']Welcome to the Lounge!

    The easiest way to refer to cells on another sheet in a formula is to point to them. Let's say that you are on Sheet1 and you want to sum cells A1:E1 on Sheet2. Here is a step-by-step description:

    - Select the cell on Sheet1 where you want the formula.
    - Type = to start a formula.
    - Type SUM( to specify that you want to sum cells.
    - Click on the sheet tab of Sheet2. This will activate Sheet2.
    - Select cells A1:E1.
    - Type ) and press Enter or click the red check mark to confirm the formula.

    The cell where you entered this should now contain the formula

    =SUM(Sheet2!A1:E1)

    If you fill this down, the cell references will be adjusted automatically.

    Post back with more details if this doesn't solve your problem.[/quote]

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Could you post a small workbook that shows the data layout, with an indication of what you want to accomplish? Use dummy data.

  6. #6
    New Lounger
    Join Date
    Jul 2009
    Location
    North Carolina, the Beach
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='787308' date='03-Aug-2009 08:49']Could you post a small workbook that shows the data layout, with an indication of what you want to accomplish? Use dummy data.[/quote]

    ok, I will try to post...

    on wkshet 1 (BkMnr) there are housing addresses, on wkshet 2 (renovations) there are corresponding dates. My goal is to get dates from wksht 2 to wksht 1 or reference and/or combined on wkshet 3...if possible...
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Thank you. If I understand your description correctly, you can use the following formula in cell D2 on BkMnr:

    =IF(ISERROR(MATCH($A2,Renovations!B:B,0)),"",INDEX (Renovations!G:G,MATCH($A2,Renovations!B:B,0)+1))

    and in cell I2:

    =IF(ISERROR(MATCH($A2,Renovations!B:B,0)),"",INDEX (Renovations!G:G,MATCH($A2,Renovations!B:B,0)+2))

    The IF(ISERROR(...) part ensures that the formula will return a blank if there is no matching address.

    You can fill down both formulas as far as needed.

    I have attached your workbook with these formulas.

    Note: there was a space after the address in cell A5 on BkMnr, this caused the MATCH to fail; I have corrected this.
    Attached Files Attached Files

  8. #8
    New Lounger
    Join Date
    Jul 2009
    Location
    North Carolina, the Beach
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    WOW!!!
    That is excellent. Thank you.

    Thank is a very difficult formula.

    I have 3 other workbooks just like that.

    Can I copy this formula and changed some of the locations(columns and rows)?



    [quote name='HansV' post='787315' date='03-Aug-2009 09:27']Thank you. If I understand your description correctly, you can use the following formula in cell D2 on BkMnr:

    =IF(ISERROR(MATCH($A2,Renovations!B:B,0)),"",INDEX (Renovations!G:G,MATCH($A2,Renovations!B:B,0)+1))

    and in cell I2:

    =IF(ISERROR(MATCH($A2,Renovations!B:B,0)),"",INDEX (Renovations!G:G,MATCH($A2,Renovations!B:B,0)+2))

    The IF(ISERROR(...) part ensures that the formula will return a blank if there is no matching address.

    You can fill down both formulas as far as needed.

    I have attached your workbook with these formulas.

    Note: there was a space after the address in cell A5 on BkMnr, this caused the MATCH to fail; I have corrected this.[/quote]

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Yes, you can copy and adapt the formula. In

    =IF(ISERROR(MATCH($A2,Renovations!B:B,0)),"",INDEX (Renovations!G:G,MATCH($A2,Renovations!B:B,0)+1))

    $A2 is the cell containing the address to look up.
    Renovations!B:B is the column on the second sheet containing the addresses.
    Renovations!G:G is the column on the second sheet containing the dates.
    +1 is used because the renovation date is one row down from the address.
    In the other formula, +2 is used because the reactivation date is two rows down from the address.

    I hope this helps you with adapting the formula.

  10. #10
    New Lounger
    Join Date
    Jul 2009
    Location
    North Carolina, the Beach
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank You, you helped me tremendously!

    [quote name='HansV' post='787334' date='03-Aug-2009 11:13']Yes, you can copy and adapt the formula. In

    =IF(ISERROR(MATCH($A2,Renovations!B:B,0)),"",INDEX (Renovations!G:G,MATCH($A2,Renovations!B:B,0)+1))

    $A2 is the cell containing the address to look up.
    Renovations!B:B is the column on the second sheet containing the addresses.
    Renovations!G:G is the column on the second sheet containing the dates.
    +1 is used because the renovation date is one row down from the address.
    In the other formula, +2 is used because the reactivation date is two rows down from the address.

    I hope this helps you with adapting the formula.[/quote]

Posting Permissions

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