Trying to create a formula or macro to pull multiple cells to a new sheet

jimbrown21

New Member
Joined
Apr 10, 2021
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
I have a macro built which pulls employee information onto sheet 1 from a website so it can be current.
Column B= Employee ID
Column D= Employee Name
Column G= Manager Name
I would like to build a formula or macro onto sheet 2 which pulls info from sheet 1. I would like to put into column A&B on sheet2 the Employee ID and Employee name IF their manager has one of the two names which I typed into cells V1 and V2 on sheet2.

Anyone know how I would go about doing this function?
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,829
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Welcome to MrExcel Message Board.

Example Data at Sheet1
Book1.xlsx
ABCDEFG
1Employee IDEmployee NameManager Name
211001Peter1Linch
311002Peter2Frank
411003Peter3Jeny
511004Peter4Heidi
611005Harold1Alexander
711006Harold2Linch
811007Harold3Frank
911008John1Jeny
1011009John2Heidi
1111010John3Alexander
1211011John4Linch
1311012John5Frank
1411013Kathrin1Jeny
1511014Kathrin2Heidi
1611015Sara1Alexander
1711016Sara2Linch
1811017Sara3Frank
1911018Sara4Jeny
2011019Jessica1Heidi
2111020Jessica2Alexander
2211021Jessica3Linch
2311022Morris1Frank
2411023Morris2Jeny
25
Sheet1


AND Result at Sheet2 (Column A formula should Press CTRL+SHIFT+ENTER
Cell Formulas
RangeFormula
A2:A13A2=IFERROR(INDEX(Sheet1!$B$2:$B$24,SMALL(IF((Sheet1!$F$2:$F$24=$V$1)+(Sheet1!$F$2:$F$24=$V$2),ROW(Sheet1!$B$2:$B$24)-ROW(Sheet1!$B$2)+1),ROWS($A$2:A2))),"")
B2:C13B2=IFNA(VLOOKUP($A2,Sheet1!$B$2:$F$24,COLUMNS($A$1:B1)*2-1,FALSE),"")
Press CTRL+SHIFT+ENTER to enter array formulas.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,047
Messages
5,639,766
Members
417,109
Latest member
996

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
Top