[Solved] Search for a name and date to return a cell value

Discuss the spreadsheet application

[Solved] Search for a name and date to return a cell value

Postby laul07 » Thu Jan 02, 2020 11:41 pm

Hi,

Im working on a spreadsheet and im looking at implementing a function to match a name and date and return a cell value on the same row.


I've attached the spreadsheet that I've been working on and after searching the web I've not been able to find a function that suits this. There are 2 sheets a Report and a Data sheet. The information needs to go on the report in the stats table (Cell B28). It needs to search through the table on the Data sheet (A3:K52)

I'm trying to find how many parts that "Staff A" has completed on "02/01/20" and show this on the report.

TIA for any help with this.
Attachments
Example.ods
(15.17 KiB) Downloaded 18 times
Last edited by laul07 on Thu Jan 16, 2020 5:43 pm, edited 2 times in total.
OpenOffice 4.1.6 on Windows Vista
laul07
 
Posts: 3
Joined: Tue Nov 26, 2019 7:56 pm
Location: UK

Re: Search for a name and date to return a cell value

Postby Villeroy » Fri Jan 03, 2020 12:02 am

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28641
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Search for a name and date to return a cell value

Postby RusselB » Fri Jan 03, 2020 12:10 am

This formula
Code: Select all   Expand viewCollapse view
=SUMPRODUCT($Data.$A$3:$A$52=$A28;$Data.$B$3:$B$52=$C$26;$Data.$G$3:$G$52)
appears to work for the specified information.
Please test it further and experiment with it.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 6157
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Search for a name and date to return a cell value

Postby Lupp » Fri Jan 03, 2020 12:24 am

(A question concerning any kind of matching should contain sufficient data to explain the situation and to allow for testing.)

You need to assure that the compound conditions are met once at most in the data rows.
See attachment.
Attachments
aoo100611compoundMatching_1.ods
(18.48 KiB) Downloaded 19 times
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2929
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Search for a name and date to return a cell value

Postby Villeroy » Fri Jan 03, 2020 12:53 am

Pivot_100611.ods
(22.76 KiB) Downloaded 14 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28641
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: fiona87, Google [Bot], Villeroy and 25 guests