populating a row from a cell
populating a row from a cell
(OP)
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
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





RE: populating a row from a cell
RE: populating a row from a cell
Hope it helps!
RE: populating a row from a cell
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
RE: populating a row from a cell
RE: populating a row from a cell
RE: populating a row from a cell
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