Formula for blank cells

[datadummy]

I am trying to write a formula that references another tab and pulls that data in, but in the event the tab doesn't have data in it I would like it to leave the destination cell blank. I tried an IFERROR formula but it returned a 0 when there was no data. Please let me know if I can provide any further information.

 

[Eric W]

Sure, you'd need a formula in each of the cells where you want the data to appear. If you'd like help with that, please post a sample of the Data Entry tab and the Stop Light tab. It helps a lot when picturing your sheet and trying to create a solution.

 

[datadummy]

Here's an example of the Stop Light tab at the bottom and the Data Entry tab on top, so what I would like to attempt to do is take Column A "IA#" on the Data Entry tab and if that number appears more than once on that tab the reference column C "Date Conducted" and take the newer of the two and then populate the cells on the Stop Light tab that match the cells on the Data Entry tab.

IA#	Audit Type	Date Conducted	Internal Audit Key Process Measures	Action Plan Owner	Auditor	Audit Detail: description	Status	Risk Status	Next Audit Date
IA1D	Document Control	4/15/2017	Infection Control		AC	8/7/2017 New Policy Administrator has been trained for Infection Control/Prevention. Document control staff is working with the new and previous IC/P policy administrator to insure a smooth transition of policies and procedures. 6/09/2017 – Infection Control/Prevention has delegated an employee to the role of Policy Administrator. Training is being scheduled with Quality Management Analyst.	Open	1	9/17
IA2D	Document Control	10/15/2017	Facilities		AC	8/7/2017 Accreditation / Quality Management Data Analyst is working with facility management continuing to develop strategic methodology for data mining and reporting.	Open	2	9/17
IA3D	Document Control	10/15/2017	Asante CMS Medicare Message		AC	5/20/2017 - Work continues and there is forward movement. We are involving additional departments in the set up of this process. 3/15/2017 - Progress is great and moving swiftly. Document Control and Continual Improvement will be using the new SharePoint site that is being constructed and we will all be able to use the site for review and communication of documents. For further history please see 2016 Quarter 3 and 4 Internal Audits.	Open	3	6/17

<colgroup><col width="91" style="width: 68pt; mso-width-source: userset; mso-width-alt: 3328;" span="2"> <col width="94" style="width: 71pt; mso-width-source: userset; mso-width-alt: 3437;"> <col width="251" style="width: 188pt; mso-width-source: userset; mso-width-alt: 9179;"> <col width="100" style="width: 75pt; mso-width-source: userset; mso-width-alt: 3657;"> <col width="99" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3620;"> <col width="36" style="width: 27pt; mso-width-source: userset; mso-width-alt: 1316;" span="3"> <col width="66" style="width: 50pt; mso-width-source: userset; mso-width-alt: 2413;"> <col width="165" style="width: 124pt; mso-width-source: userset; mso-width-alt: 6034;"> <col width="57" style="width: 43pt; mso-width-source: userset; mso-width-alt: 2084;"> <col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <col width="73" style="width: 55pt; mso-width-source: userset; mso-width-alt: 2669;"> <tbody> </tbody>

IA#	Most recent Update (metric/result)	Corrective Action Owner	Date Conducted	Risk Status	Next Follow up Date	Status
IA1D	8/7/2017 New Policy Administrator has been trained for Infection Control/Prevention. Document control staff is working with the new and previous IC/P policy administrator to insure a smooth transition of policies and procedures. 6/09/2017 – Infection Control/Prevention has delegated an employee to the role of Policy Administrator. Training is being scheduled with Quality Management Analyst.		04/15/17	Red	9/17/2017	Open
IA2D	8/7/2017 Accreditation / Quality Management Data Analyst is working with facility management continuing to develop strategic methodology for data mining and reporting.		10/15/2017	Yellow	9/17/2017	Open
IA3D	5/20/2017 - Work continues and there is forward movement. We are involving additional departments in the set up of this process. 3/15/2017 - Progress is great and moving swiftly. Document Control and Continual Improvement will be using the new SharePoint site that is being constructed and we will all be able to use the site for review and communication of documents. For further history please see 2016 Quarter 3 and 4 Internal Audits.		10/15/2017	Green	6/17/2017	Open

<colgroup><col width="89" style="width: 67pt; mso-width-source: userset; mso-width-alt: 3254;"> <col width="282" style="width: 212pt; mso-width-source: userset; mso-width-alt: 10313;"> <col width="93" style="width: 70pt; mso-width-source: userset; mso-width-alt: 3401;"> <col width="84" style="width: 63pt; mso-width-source: userset; mso-width-alt: 3072;"> <col width="95" style="width: 71pt; mso-width-source: userset; mso-width-alt: 3474;"> <col width="88" style="width: 66pt; mso-width-source: userset; mso-width-alt: 3218;"> <col width="86" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3145;"> <col width="57" style="width: 43pt; mso-width-source: userset; mso-width-alt: 2084;"> <tbody> </tbody>

 

[Eric W]

OK, with Data Entry looking like this (I'm showing again so we see the column/row values):

	A	B	C	D	E	F	G	H	I	J
1	IA#	Audit Type	Date Conducted	Internal Audit Key Process Measures	Action Plan Owner	Auditor	Audit Detail: description	Status	Risk Status	Next Audit Date
2	IA1D	Document Control	4/15/2017	Infection Control		AC	8/7/2017 New Policy Administrator has been trained for Infection Control/Prevention.Document control staff is working with the new and previous IC/P policy administrator to insure a smooth transition of policies and procedures. 6/09/2017 – Infection Control/Prevention has delegated an employee to the role of Policy Administrator.Training is being scheduled with Quality Management Analyst.	Open	1	17-Sep
3	IA2D	Document Control	10/15/2017	Facilities		AC	8/7/2017 Accreditation / Quality Management Data Analyst is working with facility management continuing to develop strategic methodology for data mining and reporting.	Open	2	17-Sep
4	IA3D	Document Control	10/15/2017	Asante CMS Medicare Message		AC	5/20/2017 - Work continues and there is forward movement.We are involving additional departments in the set up of this process. 3/15/2017 - Progress is great and moving swiftly. Document Control and Continual Improvement will be using the new SharePoint site that is being constructed and we will all be able to use the site for review and communication of documents. For further history please see 2016 Quarter 3 and 4 Internal Audits.	Open	3	17-Jun

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Data Entry​

You can set up the Stop Light sheet like this:

	A	B	C	D	E	F	G	H
1	IA#	Most recent Update (metric/result)	Corrective Action Owner	Date Conducted	Risk Status	Next Follow up Date	Status	Row
2	IA1D	8/7/2017 New Policy Administrator has been trained for Infection Control/Prevention.Document control staff is working with the new and previous IC/P policy administrator to insure a smooth transition of policies and procedures. 6/09/2017 – Infection Control/Prevention has delegated an employee to the role of Policy Administrator.Training is being scheduled with Quality Management Analyst.		4/15/2017	1	9/17/2017	Open	1
3	IA2D	8/7/2017 Accreditation / Quality Management Data Analyst is working with facility management continuing to develop strategic methodology for data mining and reporting.		10/15/2017	2	9/17/2017	Open	2
4	IA3D	5/20/2017 - Work continues and there is forward movement.We are involving additional departments in the set up of this process. 3/15/2017 - Progress is great and moving swiftly. Document Control and Continual Improvement will be using the new SharePoint site that is being constructed and we will all be able to use the site for review and communication of documents. For further history please see 2016 Quarter 3 and 4 Internal Audits.		10/15/2017	3	6/17/2017	Open	3

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Stop Light​

Worksheet Formulas Cell Formula B2 =INDEX('Data Entry'!$G$2:$G$10,$H2)&"" C2 =INDEX('Data Entry'!$E$2:$E$10,$H2)&"" D2 =INDEX('Data Entry'!$C$2:$C$10,$H2) E2 =INDEX('Data Entry'!$I$2:$I$10,$H2) F2 =INDEX('Data Entry'!$J$2:$J$10,$H2) G2 =INDEX('Data Entry'!$H$2:$H$10,$H2)&"" <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula H2 {=MATCH(A2&"|"&MAX(IF('Data Entry'!$A$2:$A$10=A2,'Data Entry'!$C$2:$C$10)),'Data Entry'!$A$2:$A$10&"|"&'Data Entry'!$C$2:$C$10,0)} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

I added the Row column, which you can hide if you want. The formula in that column looks at all the rows on the Data Entry sheet that match the value in column A, then picks the one with the highest date in column C. It's best to only calculate this number once for performance reasons, and to keep the formulas in B:G as simple as possible.

Now as you can see, the formulas are simple INDEXes, using the row found in H. So create your Row column, and then just set up the other formulas - adjust the ranges as needed.

Hope this helps!