"INDIRECT" worksheet fuction problem

Printable View

10-01-2009, 08:18 PM
Ann_BBO

1 Attachment(s)

"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
10-02-2009, 01:32 AM
Bob Phillips

I don't really get this, why are you using INDIRECT. Get rid of them and it all works fine.
10-02-2009, 02:01 AM
Ann_BBO

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 !!
10-02-2009, 03:00 AM
Bob Phillips

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).
10-02-2009, 06:01 AM
Aflatoon

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)