Auto-Populate Columns Based on Cell-Lookup Result?

P

PacSum

New Member
Joined
Jan 8, 2017
Messages
30
STARTING POINT:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Beginning[/TD]
[TD]Change #1[/TD]
[TD]Change #2[/TD]
[TD]Change #3[/TD]
[TD]Ending[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sarah[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Data will be manually populated into the "Name" column above.

After populating into the name column, my goal is to have the subsequent columns automatically populated based on whether or not that name matches to male or female.


GENDER TABLE:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Gender[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD]Sarah[/TD]
[TD]F[/TD]
[/TR]
</tbody>[/TABLE]

EQUATION TABLE:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Gender[/TD]
[TD]Beginning[/TD]
[TD]Change #1[/TD]
[TD]Change #2[/TD]
[TD]Change #3[/TD]
[TD]Ending[/TD]
[/TR]
[TR]
[TD]M[/TD]
[TD]mEquation#1[/TD]
[TD]mEquation#2[/TD]
[TD]mEquation#3[/TD]
[TD]mEquation#4[/TD]
[TD]mEquation#5[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]fEquation#1[/TD]
[TD]fEquation#2[/TD]
[TD]fEquation#3[/TD]
[TD]fEquation#4[/TD]
[TD]fEquation#5[/TD]
[/TR]
</tbody>[/TABLE]

END GOAL:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Beginning[/TD]
[TD]Change #1[/TD]
[TD]Change #2[/TD]
[TD]Change #3[/TD]
[TD]Ending[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]mEquation#1[/TD]
[TD]mEquation#2[/TD]
[TD]mEquation#3[/TD]
[TD]mEquation#4[/TD]
[TD]mEquation#5[/TD]
[/TR]
[TR]
[TD]Sarah[/TD]
[TD]fEquation#1[/TD]
[TD]fEquation#2[/TD]
[TD]fEquation#3[/TD]
[TD]fEquation#4[/TD]
[TD]fEquation#5[/TD]
[/TR]
</tbody>[/TABLE]

What would be the best way to go about trying to solve this?

Thanks in advance!
 
Last edited:

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
try

Book1
ABCDEF
1NameBeginningChange #1Change #2Change #3Ending
2BobmEquation#1mEquation#2mEquation#3mEquation#4mEquation#5
3SarahfEquation#1fEquation#2fEquation#3fEquation#4fEquation#5
4
5
6
7
8
9
10NameGender
11BobM
12SarahF
13
14
15
16GenderBeginningChange #1Change #2Change #3Ending
17MmEquation#1mEquation#2mEquation#3mEquation#4mEquation#5
18FfEquation#1fEquation#2fEquation#3fEquation#4fEquation#5
Sheet4
Cell Formulas
RangeFormula
B2=VLOOKUP(VLOOKUP($A2,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A2:B2),0)
B3=VLOOKUP(VLOOKUP($A3,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A3:B3),0)
C2=VLOOKUP(VLOOKUP($A2,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A2:C2),0)
C3=VLOOKUP(VLOOKUP($A3,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A3:C3),0)
D2=VLOOKUP(VLOOKUP($A2,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A2:D2),0)
D3=VLOOKUP(VLOOKUP($A3,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A3:D3),0)
E2=VLOOKUP(VLOOKUP($A2,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A2:E2),0)
E3=VLOOKUP(VLOOKUP($A3,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A3:E3),0)
F2=VLOOKUP(VLOOKUP($A2,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A2:F2),0)
F3=VLOOKUP(VLOOKUP($A3,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A3:F3),0)
 
Upvote 0
And to add in case this creates another layer of complexity, the mEquation and fEquation will contain a cell reference to the first column. It is not a static equation. It will need to be able to reference column A.
 
Upvote 0
Scott T said:
try
ABCDEF
NameBeginningChange #1Change #2Change #3Ending
BobmEquation#1mEquation#2mEquation#3mEquation#4mEquation#5
SarahfEquation#1fEquation#2fEquation#3fEquation#4fEquation#5
NameGender
BobM
SarahF
GenderBeginningChange #1Change #2Change #3Ending
MmEquation#1mEquation#2mEquation#3mEquation#4mEquation#5
FfEquation#1fEquation#2fEquation#3fEquation#4fEquation#5

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: center"]17[/TD]

[TD="align: center"]18[/TD]

</tbody>
Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=VLOOKUP(VLOOKUP($A2,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A2:B2),0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=VLOOKUP(VLOOKUP($A2,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A2:C2),0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=VLOOKUP(VLOOKUP($A2,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A2:D2),0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=VLOOKUP(VLOOKUP($A2,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A2:E2),0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=VLOOKUP(VLOOKUP($A2,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A2:F2),0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B3[/TH]
[TD="align: left"]=VLOOKUP(VLOOKUP($A3,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A3:B3),0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C3[/TH]
[TD="align: left"]=VLOOKUP(VLOOKUP($A3,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A3:C3),0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D3[/TH]
[TD="align: left"]=VLOOKUP(VLOOKUP($A3,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A3:D3),0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E3[/TH]
[TD="align: left"]=VLOOKUP(VLOOKUP($A3,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A3:E3),0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F3[/TH]
[TD="align: left"]=VLOOKUP(VLOOKUP($A3,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A3:F3),0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

Makes sense. I had thought about this, but then as I was working through realized there was the qualifier that the equations are dynamic and will need to reference the first column. How would I implement a dynamic cell reference?
 
Upvote 0
Scott T said:
could you give some examples
Click to expand...

I'm sorry, just saw this now!

For example purposes, it'd be similar to a vlookup on the name, but using that specific equation which was previously determined.

For instance, Bob is male. Bob is cell A2.

His "Change #1 " column which is represented by mEquation#2 could be equal to =VLOOKUP(A2,[another table of data],3)+20*12

Sarah is female. Sasrah is cell A3.

Her "Change #1 " column which is determined to be fEquation#2 could be equal to =VLOOKUP(A3,[another table of data],5)+100/2
 
Upvote 0
The formulas posted earlier still work.

Book1
ABCDEF
1NameBeginningChange #1Change #2Change #3Ending
2BobmEquation#1245mEquation#3mEquation#4mEquation#5
3SarahfEquation#157fEquation#3fEquation#4fEquation#5
4
5
6
7
8
9
10NameGender
11BobM
12SarahF
13
14
15
16GenderBeginningChange #1Change #2Change #3Ending
17MmEquation#1245mEquation#3mEquation#4mEquation#5
18FfEquation#157fEquation#3fEquation#4fEquation#5
19
20
21
22bobfsa5
23rickgsh2
24sarahsome7777
Sheet1
Cell Formulas
RangeFormula
B2=VLOOKUP(VLOOKUP($A2,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A2:B2),0)
B3=VLOOKUP(VLOOKUP($A3,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A3:B3),0)
C2=VLOOKUP(VLOOKUP($A2,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A2:C2),0)
C3=VLOOKUP(VLOOKUP($A3,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A3:C3),0)
C17=VLOOKUP(A2,A22:C28,3)+20*12
C18=VLOOKUP(A3,A22:E29,5)+100/2
D2=VLOOKUP(VLOOKUP($A2,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A2:D2),0)
D3=VLOOKUP(VLOOKUP($A3,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A3:D3),0)
E2=VLOOKUP(VLOOKUP($A2,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A2:E2),0)
E3=VLOOKUP(VLOOKUP($A3,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A3:E3),0)
F2=VLOOKUP(VLOOKUP($A2,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A2:F2),0)
F3=VLOOKUP(VLOOKUP($A3,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A3:F3),0)
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Create list of last(family) names when parents have children in more than 1 class
Replies
7
Views
172
Peter_SSs
Peter_SSs
airforceone
  • Solved
Populate Rows & Columns by Comma Split
2
Replies
12
Views
363
airforceone
airforceone
L
  • Solved
Insert formulas to new lines in table
Replies
3
Views
162
Ltuk85
L
K
  • Question Question
Range of columns change colors based on cell column value
Replies
3
Views
59
Joe4
Joe4
WERNER SLABBERT
  • Question Question
Run-time error 70 after code changed
Replies
3
Views
118
RoryA
RoryA

Forum statistics

Threads
1,224,823
Messages
6,181,170
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top