Jul 17 2017
08:47 AM
- last edited on
Jul 25 2018
09:49 AM
by
TechCommunityAP
Jul 17 2017
08:47 AM
- last edited on
Jul 25 2018
09:49 AM
by
TechCommunityAP
Hi,
I am trying to build a financial statement template that uses formulas to pull data from another worksheet.
The difficulty I am having is finding a formula that will look to a whole sheet of data and return the value based on 2 conditions:
1. Find the Project
2. Find the GL Code
If yes, enter value from spreadsheet. If no, enter $0.
I have uploaded a sample for illustration purposes. I am thinking I needed some combination of a lookup, nestedif and array formula. I would like the template to always lookto another tab for the data. My hope is to be able to export the data from bookeeping software to this tab and have the template pull the numbers.
Any help would be appreciated. Thank you.
Rob
Jul 17 2017 09:27 AM
SolutionHi Rob,
Array (Ctrl+Shift+Enter) formula INDEX/MATCH could work, for revenue
=IFERROR(INDEX(Data!$D:$D, MATCH(1,(Data!$F:$F=Template!C$2)*(Data!$C:$C=Template!$B4),0),1),0)
and for expenses
=IFERROR(INDEX(Data!$E:$E, MATCH(1,(Data!$F:$F=Template!C$2)*(Data!$C:$C=Template!$B9),0),1),0)
see attached file
Jul 17 2017 09:50 AM - edited Jul 17 2017 09:59 AM
I like to use SUMIFS for this sort of work
=SUMIFS(Data!$D:$D,Data!$B:$B,$A4,Data!$F:$F,C$2)
+SUMIFS(Data!$E:$E,Data!$B:$B,$A4,Data!$F:$F,C$2)
Also note that this relies heavily on getting all the descriptions in the right spots so worth adding cross checks.
Cell F23 on the data sheet should probably have Project 3 typed in.
Jul 17 2017 09:50 AM
Thank you very, very much!
It works :)
Rob
Jul 17 2017 10:49 AM
Thanks Wyn!
I appreciate you taking the time to solve this issue.
Rob
Jul 17 2017 09:27 AM
SolutionHi Rob,
Array (Ctrl+Shift+Enter) formula INDEX/MATCH could work, for revenue
=IFERROR(INDEX(Data!$D:$D, MATCH(1,(Data!$F:$F=Template!C$2)*(Data!$C:$C=Template!$B4),0),1),0)
and for expenses
=IFERROR(INDEX(Data!$E:$E, MATCH(1,(Data!$F:$F=Template!C$2)*(Data!$C:$C=Template!$B9),0),1),0)
see attached file