populating a row from a cell 1

[dvd]

I am stubbornly trying to solve a problem, for which I choose not to use macro's or VBA. Is there some way to populate a row in excel from a single cell? I have been able to get the cell location for a maximum value and now I would like to copy the contents of the entire row to a different location on the spreadsheet.

My formula looks like this:
=INDIRECT(CONCATENATE("a",8+MATCH(MAX(U9:U143),U9:U143,0)))

I can get any data that I choose by changing the column index, and entering the formula multiple times, but what I would like to do is enter a formula in colum A and have the values for the whole row populate my new row.

Comments on methods using this approach would be appreciated.

Best regards,

DVD

 

[MintJulep]

Basically, no. A formula in a cell cannot alter the contents of any other cell.

 

[yakpol]

I may not entirely understand what are you trying to achieve. To copy a row from the table with a maximum value in column "U" ? Type formula =INDEX(A9:A143,MATCH(MAX($U$9:$U$143),$U$9:$U$143,0)) in column A and then copy it across entire row.

Hope it helps!

 

[yogia]

Hi DVD:

If I understand you correctly, in any row below row 143, select the entire row, and with the cell in column A of the row as the active cell, key-in the following formula

=INDEX(1:65536,MATCH(MAX($U$9:$U$14),$U$1:$U$14,0),0)

and use CTRL+ENTER rather than just ENTER -- this will populate the entire row with the contents of the reference row (with the MAX value in U9:U143) -- the empy cells in the reference row will be converted to 0s.

If you would want to have the empty cells in the reference row diplayed as blanks rather than 0s, then you can use the following modified formula ...

=IF(LEN(INDEX(1:65536,MATCH(MAX($U$9:$U$14),$U$1:$U$14,0),0)),INDEX(1:65536,MATCH(MAX($U$9:$U$14),$U$1:$U$14,0),0),"")

Is this what you are looking for?


Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC

http://www.energyefficientbuild.com

 

[IFRs]

The program "Mesa" was a replacement for Excel and it had a neat feature. You could designate a single cell to be copied down or across a row by placing ditto marks in the other cells. If you changed the header formula, all the others changed as well. Of course cell references were updated, etc. Neat. Unfortunately, never emulated.

 

[dvd]

Thank you, Yogi. That was just what the doctor ordered.

 

[yogia]

Hi dvd:

You Are Very Welcome ... I am glad you got the solution you were looking for.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC

http://www.energyefficientbuild.com