×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

populating a row from a cell

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

RE: populating a row from a cell

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

RE: populating a row from a cell

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!



RE: populating a row from a cell

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

RE: populating a row from a cell

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.

RE: populating a row from a cell

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

RE: populating a row from a cell

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources