INDIRECT FUNCTION

[Mark2Aus]

Hi everyone

In cell A2 I will key each time a different number representing the row with the data I would like to check.

In my sheet, from row 5 to 1000 I have data already entered (each row contains data from column B to column CZ).

I would like to have in cell A3 the formula.
=INDIRECT("B"&$A$2) and then copy to the right up to CZ3.

In that way, when I change the content of the value in cell A2, say to 17, row 3 will show the data contained in row 17 from A17 to CZ17), etc.

I tried this, but when I copied right, all the cells in row 3 contained exactly the same formula, not changing the columnm!!
I expected the cell F3 to contain the formula:
=INDIRECT ("F"&$A$2);
the cell M3 to contain =INDIRECT("M"&$A$2); etc...

Can I write a formula with indirect reference that will change as I copy right?
Thanks

 

[macropod]

Hi Mark2Aus,

No, that's not the way INDIRECT works. Depending on what you're trying to do, you might find an OFFSET or INDEX funtion better suited to your needs.


Cheers
[MS MVP - Word]

 

[SkipVought]

Hi Mark2Aus,

Please explain the nature of your requirement, not how you think it should be solved.

Perhaps post some sample data that is representative of your requirement.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Mark2Aus]

We have thousands of rows, each containing all the prices of a financial futures index prices during each trading day. In this way: say row 1231 contains all prices (every second or in smaller intervals) for 23/12/2009.
We want to test a small change in our proprietary trading system (a separate spreadsheet containg several steps until a decision to buy or sell is reached).
In order to test the changes, we are going to test it randomly.
In my sheet, in cell A17 I put a function to choose a random date. I want that, as soon I put a new random date, the complete row containing the prices for that date will be shown in a test area (row 3: from A3 to ...say CZ3). This test area will contain the formula that I am trying to copy to the right and then , based on this row, the sytenm will test the changes and give the net result.

I thought again about the INDIRECT function and I came up with a formula that solved my problem:

in A3: =INDIRECT(ADDRESS($A$17,COLUMN()))
In this way I am able to copy it to the right and every time I put a random number in cell A17, the row 3 is filled with the data related to the random date.
So, problem solved. Thanks for the help.
So the formula must