Consulting

Results 1 to 5 of 5

Thread: "INDIRECT" worksheet fuction problem

  1. #1

    "INDIRECT" worksheet fuction problem

    Hi All,

    I have implemented the "INDIRECT" function on A2 and A3. A2 find out the "one month ago" date on a "$H2:$IV2" data. Then, A3 get the corrsponding data base on A2. Note that I will insert the latest data in column H and H2 will be the latest date.

    A2=INDEX(INDIRECT("$H2:$IV2"),MATCH(INDIRECT("$H2")-30,INDIRECT("$H2:$IV2"),-1))

    A3=HLOOKUP(INDIRECT("A$2"),INDIRECT("$H$2:$IV$79"),2,FALSE)

    It is OK when i insert the new column to column H and not affect the A2,A3. However, i will insert the new ROW(s) on the top row. Then, the "INDIRECT" function will not work since "INDIRECT" function fixed on the cell.

    For example. if i insert the new row on top row, A2 becomes A3 and A3 becomes A4. Then i would like the formula will change to:

    A3=INDEX(INDIRECT("$H3:$IV3"),MATCH(INDIRECT("$H3")-30,INDIRECT("$H3:$IV3"),-1))

    A4=HLOOKUP(INDIRECT("A$3"),INDIRECT("$H$3:$IV$80"),2,FALSE)

    How to implement the "INDIRECT" function work on inserting column but not inserting row? Thanks

    Ann

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    I don't really get this, why are you using INDIRECT. Get rid of them and it all works fine.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    Actually, i have a marco that will insert new column in column H and then copy a data from other file and paste into it. Then, data of new column H will be a latest data. As we know if insert the new column, it will move change the cell format. That's why i use the INDIRECT Function. But now, i need to insert the new row to the top row. Since i used the INDIRECT function, then it will not work.

    Hope that you can understand my meanings. Thanks again !!

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    I am afraid I don't.INDIRECT is (usually) used when a cell contains the address of the real cell. YOurs all contain data, so INDIRECT is wasted (and expensive).
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,720
    Location
    You could try using:
    A2=INDEX(INDIRECT("$H"&ROW()&":$IV"&ROW()),MATCH(INDIRECT("$H"&ROW())-30,INDIRECT("$H"&ROW()&":$IV"&ROW()),-1))
    but I think you would be better off either rethinking your data layout, or adding a blank 'anchor' column and inserting your new data to the right of that. (you can then include the anchor column in your formula)

Posting Permissions

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