IF/THEN ? Should be fairly easy to answer - just can't figure out!

rachabra

New Member
Joined
Jan 2, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
I have a pretty basic understanding of Excel but I'm sure there must be a way to do this: I have a worksheet that is a template for an invoice. And I have a worksheet that has data. What I want to do is have the template populate based on one Customer. So, this is what I'm trying to figure out:

Worksheet A: Template Invoice

Customer: Judy Smith (D2 and D4 only on Worksheet B) WHAT FORMULA WOULD I USE HERE TO ONLY GRAB DATA RELATING TO Judy Smith????

Date: Description: Hours:
='Data'!A2 ='Data'!B2 ='Data'!D2
='Data'!A4 ='Data'!B4 ='Data'!D4


Worksheet B: Data
Colm: A B C D
R1 Date: Description: Hours: Customer:
R2 11/20/20 Housecleaning 3 Judy Smith
R3 11/22/20 Gardening 4 Bob Miller
R4 11/25/20 Weeding 2 Judy Smith

Thanks so much for your help!!
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,878
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Why not simply use Autofilter ?
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,913
Office Version
  1. 2016
Platform
  1. Windows
Hi Rachabra,

If this is your data sheet B

Rachabra.xlsx
ABCD
1DateDescriptionHoursCustomer
220-Nov-20Housecleaning3Judy Smith
322-Nov-20Gardening4Bob Miller
425-Nov-20Weeding2Judy Smith
5
B


Then this should pull in the data for the specified Customer

Rachabra.xlsx
ABCDE
1Rachabra
2
3Customer:Judy Smith
4
5DateDescriptionHoursCustomer
620-Nov-20Housecleaning3Judy Smith
725-Nov-20Weeding2Judy Smith
8    
9    
A
Cell Formulas
RangeFormula
B6:E9B6=IFERROR(INDEX(B!A$2:A$9999,AGGREGATE(15,6,ROW(B!$B$2:$B$9999)-ROW(B!$B$1)/(B!$D$2:$D$9999=$C$3),ROW()-ROW($B$5))),"")
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,840
Office Version
  1. 365
Platform
  1. Windows
If your version of 365 has the FILTER function then something like this:
Sheet1

Book1
ABC
1
2Judy Smith
3
4DateDescriptionHours
511/20/2020Housecleaning3
611/25/2020Weeding2
Sheet1
Cell Formulas
RangeFormula
A5:C6A5=FILTER(Sheet2!$A$2:$C$4,Sheet2!$D$2:$D$4=$A$2,"")
Dynamic array formulas.


Sheet2
Book1
ABCD
1DateDescriptionHoursCustomer
211/20/2020Housecleaning3Judy Smith
311/22/2020Gardening4Bob Miller
411/25/2020Weeding2Judy Smith
Sheet2
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,878
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Or a Pivot Table !!
 

Forum statistics

Threads
1,141,051
Messages
5,703,960
Members
421,321
Latest member
blusky4

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