Page 1 of 3 123 LastLast
Results 1 to 15 of 38
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Formula to reference a cell based on another cell (Excel xp)

    I have a workbook with 2 sheets. Sheet2 is linked to Sheet1.

    The formula in b3 on sheet2 is =sheet1!B199

    I want to write a formula on sheet2 in cell b12 that will give me the value of 1 row down whatever the reference is in cell b3 of sheet2. In other words, I want b12 to have a formula that will give me sheet1!B200's value. I don't want to have to go to sheet1 and click on B200 and paste special back to sheet2. There are a lot of these and i'm hoping to use a formula that i can copy throughout sheet2.

    Thank you for the help.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula to reference a cell based on another cell (Excel xp)

    I don't know of any way to do that directly. The User Defined Function below will do it. Please note that the function does not do any error checking to make sure that what is in the cell pass is really a formula like =sheet1!b199.

    <pre>Public Function OneDown(oCell As Range) As Variant
    Dim strSName As String, strCAdd As String
    strSName = Mid(oCell.Formula, 2, InStr(oCell.Formula, "!") - 2)
    strCAdd = Right(oCell.Formula, Len(oCell.Formula) - InStr(oCell.Formula, "!"))
    OneDown = Worksheets(strSName).Range(strCAdd).Offset(1, 0).Value
    End Function
    </pre>

    Legare Coleman

  3. #3
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula to reference a cell based on another cell (Excel xp)

    LC, do you now have Excel 2002?

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula to reference a cell based on another cell (Excel xp)

    No, I will not install any product that includes the Registration/Activation Wizard.
    Legare Coleman

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula to reference a cell based on another cell (Excel xp)

    This works great.

    I need another one that will look on the other sheet and if the value (onedown) equals an "A", i want to change it to E, and if it's a "B" i want to change it to an AF.

    The problem is the cell on the other sheet may have a value of AB which means on the active sheet it will need to read E,AF (i need commas between the letters). There are 10 other letters that have to be substituted but hopefully i can change them if you can help me with this part. Thank you very much.

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Formula to reference a cell based on another cell (Excel xp)

    OK, try this:

    1. In your source worksheet (Sheet1), insert a new row at row 1.
    2. Open a new workbook.
    3. Cut (not copy) your target worksheet (Sheet2) and paste it into the new workbook
    4. Close the original workbook without saving.
    5. Re-open the original workbook.
    6. Copy (not cut) the new worksheet out of the new workbook and paste it back over the original target worksheet (Sheet2).
    All of the links from the source worksheet to the target worksheet will now be offset by one row down from their original positions. Links going the other way won't be affected.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  7. #7
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula to reference a cell based on another cell (Excel xp)

    Sorry, but i think i need the function from Legare altered. Something like

    sheet1, A6 has a value of AB
    sheet2, G15, has a formula using the onedown function. It looks at sheet2,G13's call to sheet1!A5, goes down 1 row to A6 which has a value of AB in it.

    i need the formula on sheet2, cell G15 to make the A an E, and the B an AF with a comma between. The value of G15 will now be E,AF. Thank you

  8. #8
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Formula to reference a cell based on another cell (Excel xp)

    Sorry about the confusion there - my reply was to your original post. Your later post wasn't there when I started replying.
    I'll have a think about the latter issue - at first blush it seem like you need a formula with a set of IF tests. Have you a sample worksheet we could look at?
    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  9. #9
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula to reference a cell based on another cell (Excel xp)

    Thank you. here is an example
    Attached Files Attached Files

  10. #10
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Formula to reference a cell based on another cell (Excel xp)

    Hi again,

    Check out the attached - I think it'll do what you want with cells containing numbers or up to 3 of the specified characters. To achieve this, I've used LOOKUP to cross-reference the returned characters with the required ones. Note too how I've used a redundant NOW function to create a volatile formula - the UDF doesn't recalculate without it.

    Cheers

    PS: Ignore my original reply - it was off on a completely different tangent.
    Attached Files Attached Files
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula to reference a cell based on another cell (Excel xp)

    A function that is called from a formula on a worksheet can not change a cell on the worksheet, it can only return a value to the formula that called it. You will have to find another way to do this.
    Legare Coleman

  12. #12
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula to reference a cell based on another cell (Excel xp)

    Thank you so much. Is there anyway for me to put this in a new function or in a sub procedure so that the worksheet will update via a macro? I'm just afraid that the "list" of substitutions will change and i will have to change all the formulaes. any thoughts are greatly appreciated.

  13. #13
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Formula to reference a cell based on another cell (Excel xp)

    Hi jha,

    Provided you define a large enough range in your formula for the lookup table and keep the data in its left column sorted in ascending order, you can add/remove as many substitutions as you like without having to change the formula. In the example I gave you, I defined the lookup table as occupying $A$1:$A$6 for the left column and $B$1:$B$6 for the right column. If you wanted to allow for, say, 20 possible sustitutions, you would change the lookup from $A$1:$A$6,$B$1:$B$6 to $A$1:$A$20,$B$1:$B$20. You could use all of columns A & B ($A:$A,$B:$[img]/forums/images/smilies/cool.gif[/img]. The lookup table could even be moved to another worksheet.

    The only limitation, as I previously said, is that my solution only substitutes non-numeric results for up to three characters - though it could readily be extended to cater for up to 11, after which Excel can no longer accomodate the formula's length.

    If need be, you could also use different lookup tables for different parts of your worksheet.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  14. #14
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula to reference a cell based on another cell (Excel xp)

    ok, thank you. I put the lookup list on a separate worksheet and it works. It gives me a #N/A when there is no value in one down. Can I add something to adjust for that?

  15. #15
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Formula to reference a cell based on another cell (Excel xp)

    That's easy. Change:
    IF(ISNUMBER
    at the start of the formula to:
    IF(ISNONTEXT
    to return 0 if the source cell is 0 or blank.

    It's a bit more complicated if you want a 'blank' result if the source is blank. Let me know if that's what you need.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Page 1 of 3 123 LastLast

Posting Permissions

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