Autofill based on values from another sheet

gls9879872

New Member
Joined
Apr 7, 2017
Messages
4
I was trying to find a way not to do redundant work. Same spreadsheet, Sheet1 and Sheet2. I would like Sheet2 Column D to autofill with the data from Sheet1 Colum D. But based in the Input of Sheet1 Column E & F. Or the opposite would be good also. Have Sheet1 Column E & F autofill with data from Sheet2 Column B and Column C based on the input in Sheet2 Colunm D. Thanks for any help. Or any direction on what way this can be achieved.

Sheet1:
[TABLE="width: 920"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[/TR]
[TR]
[TD]AV001
[/TD]
[TD]EX3240 Projector - V11H719020 - White
[/TD]
[TD]Projector
[/TD]
[TD]Projector-AV-001
[/TD]
[TD][/TD]
[TD][/TD]
[TD]xxxxxxx
[/TD]
[TD]3/1/2019
[/TD]
[/TR]
[TR]
[TD]AV002
[/TD]
[TD]EX3240 Projector - V11H719020 - White
[/TD]
[TD]Projector
[/TD]
[TD]Projector-AV-002
[/TD]
[TD][/TD]
[TD][/TD]
[TD]xxxxxxx
[/TD]
[TD]3/1/2019
[/TD]
[/TR]
[TR]
[TD]AV003
[/TD]
[TD]EX3240 Projector - V11H719020 - White
[/TD]
[TD]Projector
[/TD]
[TD]Projector-AV-003
[/TD]
[TD][/TD]
[TD][/TD]
[TD]xxxxxxx
[/TD]
[TD]3/1/2019
[/TD]
[/TR]
[TR]
[TD]AV004
[/TD]
[TD]EX3240 Projector - V11H719020 - White
[/TD]
[TD]Projector
[/TD]
[TD]Projector-AV-004
[/TD]
[TD][/TD]
[TD][/TD]
[TD]xxxxxxx
[/TD]
[TD]3/1/2019
[/TD]
[/TR]
[TR]
[TD]AV005
[/TD]
[TD]EX3240 Projector - V11H719020 - White
[/TD]
[TD]Projector
[/TD]
[TD]Projector-AV-005
[/TD]
[TD][/TD]
[TD][/TD]
[TD]xxxxxxx
[/TD]
[TD]3/1/2019
[/TD]
[/TR]
[TR]
[TD]AV006
[/TD]
[TD]HC 1040 Projector - V11H772020 - Black
[/TD]
[TD]Projector
[/TD]
[TD]Projector-AV-006
[/TD]
[TD][/TD]
[TD][/TD]
[TD]xxxxxxx
[/TD]
[TD]4/4/2020
[/TD]
[/TR]
[TR]
[TD]AV007
[/TD]
[TD]HC 1040 Projector - V11H772020 - Black
[/TD]
[TD]Projector
[/TD]
[TD]Projector-AV-007
[/TD]
[TD]2
[/TD]
[TD]8
[/TD]
[TD]xxxxxxx
[/TD]
[TD]4/4/2020
[/TD]
[/TR]
[TR]
[TD]AV008
[/TD]
[TD]HC 1040 Projector - V11H772020 - Black
[/TD]
[TD]Projector
[/TD]
[TD]Projector-AV-008
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]xxxxxxx
[/TD]
[TD]4/4/2020
[/TD]
[/TR]
[TR]
[TD]AV009
[/TD]
[TD]HC 1040 Projector - V11H772020 - Black
[/TD]
[TD]Projector
[/TD]
[TD]Projector-AV-009
[/TD]
[TD]1
[/TD]
[TD]4
[/TD]
[TD]xxxxxxx
[/TD]
[TD]4/4/2020
[/TD]
[/TR]
</tbody>[/TABLE]




Sheet2:
[TABLE="width: 1040"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]Building 1, 1st Floor, Room 101
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]TV-AV-012
[/TD]
[/TR]
[TR]
[TD]Building 1, 1st Floor, Room 102
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Building 1, 1st Floor, Room 103
[/TD]
[TD]1
[/TD]
[TD]3
[/TD]
[TD]TV-AV-011
[/TD]
[/TR]
[TR]
[TD]Building 1, 1st Floor, Room 104
[/TD]
[TD]1
[/TD]
[TD]4
[/TD]
[TD]Projector-AV-009
[/TD]
[/TR]
[TR]
[TD]Description/Location
[/TD]
[TD]Building#
[/TD]
[TD]Room#
[/TD]
[TD]Device Label
[/TD]
[/TR]
[TR]
[TD]Building 2, 1st Floor, Room 201
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]Projector-AV-008
[/TD]
[/TR]
[TR]
[TD]Building 2, 1st Floor, Room 202
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Building 2, 1st Floor, Room 203
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Building 2, 1st Floor, Room 204
[/TD]
[TD]2
[/TD]
[TD]4
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Building 2, 1st Floor, Room 205
[/TD]
[TD]2
[/TD]
[TD]5
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Building 2, 2nd Floor, Room 206
[/TD]
[TD]2
[/TD]
[TD]6
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Building 2, 2nd Floor, Room 207
[/TD]
[TD]2
[/TD]
[TD]7
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Building 2, 2nd Floor, Room 208
[/TD]
[TD]2
[/TD]
[TD]8
[/TD]
[TD]Projector-AV-007
[/TD]
[/TR]
[TR]
[TD]Building 2, 2nd Floor, Room 209
[/TD]
[TD]2
[/TD]
[TD]9
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Building 2, 2nd Floor, Room 210
[/TD]
[TD]2
[/TD]
[TD]10
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Description/Location
[/TD]
[TD]Building#
[/TD]
[TD]Room#
[/TD]
[TD]Device Label
[/TD]
[/TR]
[TR]
[TD]Building 3, 1st Floor, Rm 301
[/TD]
[TD]3
[/TD]
[TD]1
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
I got it to work by moving column D on sheet 2 to the left (or making it column "B") and making your column "B" and "C" then new "C" and "D"... Only because a vlookup can't look to the left. So on sheet 2 you'd now have A = Description/Location B = Device Label C = Building # and D = Room # ........ Then on Sheet 1 I entered a vlookup... So lets say in cell E2 I entered "=VLOOKUP($D$2,Sheet2!$B$1:$E$18,2,TRUE)" this way it would look at the device you are looking for the reference it on sheet 2 and display the building " where the device is... in column F I entered "=VLOOKUP($D$2,Sheet2!$B$1:$E$18,3,TRUE)" to get the Room #.... Hope this helps.
 
Upvote 0
Ok, so when I paste, Projector-AV-001, into column B on Sheet2 it works perfectly. I can't get it to work on other cells. Even changing the "=VLOOKUP($D$2,Sheet2 to the next row increment. I will look into it later. My weekend is starting. But you have made a huge leap for me. I knew it could be done, just didn't know where to start looking first.
 
Upvote 0
After looking into the VLOOKUP I came up with this and it works perfectly. I moved the Sheet2 Column D to Column B. With Sheet2 Column B as my input column. And input these two commands on Sheet1 Column E and Column F respectively. The #N/A that is returned on equipment not distributed is fine for my sheet.

=VLOOKUP( D2, 'Sheet2'!B:D, 2, FALSE ) in the Building column. Then =VLOOKUP( D2, sheet2!B:D, 3, FALSE ) in the Room Column. It works like a charm


Mojoriffic thanks for the help.
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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