CODEWITHSUNDEEP
excelexcel-formula
Cek123123
Cek123123

Reputation: 13

Autofilling table rows from one data

I have a table with some data (all unique) like:

id   Name    Surname    Age
 1    Jack    Gong      21 
 2   Danny   Manny      24

If I insert one more row and write only surname as Gong to the cell, then is there any Excel function that fills the "Age", "Name" and "id" automatically to 21, Jack and 1?

Upvotes: 1

Views: 48

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27249

There is no automatic way (outside of a VBA procedure, perhaps) to do this, but you can set something up that will do it through formulas:

  1. Using the below picture, place the formula shown in the formula bar into cell A4, then copy into cell B4 and D4.
  2. Then to insert a new row, copy this row and paste in the next row and input the new surname in column C. The id, name and age columns will look if that value has been entered above and fill the results if so.
  3. If the value is not listed, the id, name and age columns will return blanks, at which point you can enter them manually.
  4. Each time you insert a new row, ensure that you have a row full of formulas for id, name and age. (Alternatively, you can copy these down from above rows individually as well - notice all the appropriate cell locking to ensure it always captures the entire range).

enter image description here

Upvotes: 1

Related Questions