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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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
3
Views
99
Peter_SSs
Peter_SSs
airforceone
  • Solved
Populate Rows & Columns by Comma Split
2
Replies
12
Views
357
airforceone
airforceone
L
  • Solved
Insert formulas to new lines in table
Replies
3
Views
152
Ltuk85
L
A
  • Solved
Exclude columns in listbox on userform except first two columns & last column
2
Replies
16
Views
741
abdelfattah
A
D
  • Solved
Counting formula results and text data while ignoring blank cells
2
Replies
16
Views
770
Darren_workforce
D

Forum statistics

Threads
1,223,897
Messages
6,175,269
Members
452,628
Latest member
dd2

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