Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Conditional Lookups or Dynamic Cell References?

  1. #1
    New Member
    Join Date
    May 2002
    Location
    England
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This is a follow on to my earlier mistaken conditional formatting query.

    Is it possible, using only Excel functions, for one cell to absolutely reference a second cell value in another worksheet based on the value of a third cell?

    I cannot use a nested 'IF' statement as there are 12 different conditions. Can a lookup function fit the bill here to first check the value of the third cell and then fetch the value of specific cell in another 'hidden' sheet.?

    Furthermore, if the data is then changed by the user in the first cell, those changes are updated in the second cell.

    I have uploaded the source file here.

    http://us.f1.yahoofs.com/users/df94e...3J_38AIdk0yVti







  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =INDIRECT("Sheet2!"&Sheet1!A2)

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    Trussville, AL
    Posts
    134
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If you want to check the cell and then do one of 12 different actions I have used the CHOOSE formula. Much nicer than nested Ifs and not limited to 7.

    I have not used INDIRECT. But, it looks interesting.

    Philip

  4. #4
    New Member
    Join Date
    May 2002
    Location
    England
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for that PDuPre'

    The CHOOSE function works fine for "getting" the correct values from the second cell(s) but if I want to update the values in the second cell using the first cell, it overwrites the CHOOSE formula.

    I've tried using an IF [Conditional Cell]=RequiredValue then 2nd Cell Value=1st Cell Value. However, this creates a circular reference and again overwrites the CHOOSE formula in the first cell.

    I don't really know Excel, I'm learning all the time, but I think there's an 'easy' method to sort this out that's eluding me.



    Lee

    [ This Message was edited by: Worried Guy on 2002-05-13 13:55 ]

  5. #5
    New Member
    Join Date
    May 2002
    Location
    England
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I think this is going to require the timely intervention of a VBA Don to solve this chestnut. Every formula I use will be overwritten the first time data is entered into a cell and if I want to go back to amend any values, it will not work anymore.

  6. #6
    Board Regular
    Join Date
    Apr 2002
    Location
    Trussville, AL
    Posts
    134
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Not sure why the original formula is being overwritten. Is someone actually typeing a number in the cell over the formula? Or are you using an excel add-in to retrieve data from a database?

    If the former then you can have the data entry in a third cell. If the latter then there is probably an option to retain formulas. This is Excel, so the add-in would expect people to have complex formulas.

    Still, if you have a solution, but the formula keeps getting over-written, you could simply record a macro of your actions to enter the required formulas etc. after each retrieve.

    I hope this helps.

    Philip

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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