Good morning everyone. Does anyone know if there is a way to fill in a range of cells based on info from another range of cells but have them fill in so that there are no blank cells in the starting range? Pardon the confusion, but I have a formula that reads as follows:
=IF(AND(OR('Data Sheet'!M37=TRUE),(OR('Project Data Sheet'!H21={"Small - Adult/PRE-NOTICE","Small - Adult/English","Small - Adult/Spanish","Large - Adult/Eng-Spa","Small - Adult/Custom","Large - Adult/Custom"}))),'Project Data Sheet'!B21," ") (B22 corresponds to 9138001, B23 corresponds to 9138002, etc.)
and shows the following result placing project numbers in their corresponding cells:
[TABLE="width: 665"]
<tbody>[TR]
[TD="colspan: 5"]Sample Number(s):[/TD]
[TD="colspan: 4"]9138000[/TD]
[TD="colspan: 4"]9138001[/TD]
[TD="colspan: 3"][/TD]
[TD="colspan: 3"]9138003[/TD]
[TD="colspan: 3"]9138004[/TD]
[TD="colspan: 2"]9138005[/TD]
[/TR]
[TR]
[TD="colspan: 5"][/TD]
[TD="colspan: 4"]9138006[/TD]
[TD="colspan: 4"][/TD]
[TD="colspan: 3"][/TD]
[TD="colspan: 3"]9138009[/TD]
[TD="colspan: 3"][/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"][/TD]
[TD="colspan: 4"][/TD]
[TD="colspan: 3"]9138014[/TD]
[TD="colspan: 3"][/TD]
[TD="colspan: 3"][/TD]
[TD="colspan: 2"][/TD]
[/TR]
</tbody>[/TABLE]
For the cells that are empty, i.e., where 9138002, 9138007 and 9138008, etc. would have been placed had they met the criteria, is there any way to close up these gaps so that I don't have gaping holes in my project number list and so that the next number that does meet that criteria would display in the next available cell instead of being locked into one particular? I hope that makes sense.
Any assistance would be greatly appreciated.
Thank you so much!
=IF(AND(OR('Data Sheet'!M37=TRUE),(OR('Project Data Sheet'!H21={"Small - Adult/PRE-NOTICE","Small - Adult/English","Small - Adult/Spanish","Large - Adult/Eng-Spa","Small - Adult/Custom","Large - Adult/Custom"}))),'Project Data Sheet'!B21," ") (B22 corresponds to 9138001, B23 corresponds to 9138002, etc.)
and shows the following result placing project numbers in their corresponding cells:
[TABLE="width: 665"]
<tbody>[TR]
[TD="colspan: 5"]Sample Number(s):[/TD]
[TD="colspan: 4"]9138000[/TD]
[TD="colspan: 4"]9138001[/TD]
[TD="colspan: 3"][/TD]
[TD="colspan: 3"]9138003[/TD]
[TD="colspan: 3"]9138004[/TD]
[TD="colspan: 2"]9138005[/TD]
[/TR]
[TR]
[TD="colspan: 5"][/TD]
[TD="colspan: 4"]9138006[/TD]
[TD="colspan: 4"][/TD]
[TD="colspan: 3"][/TD]
[TD="colspan: 3"]9138009[/TD]
[TD="colspan: 3"][/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"][/TD]
[TD="colspan: 4"][/TD]
[TD="colspan: 3"]9138014[/TD]
[TD="colspan: 3"][/TD]
[TD="colspan: 3"][/TD]
[TD="colspan: 2"][/TD]
[/TR]
</tbody>[/TABLE]
For the cells that are empty, i.e., where 9138002, 9138007 and 9138008, etc. would have been placed had they met the criteria, is there any way to close up these gaps so that I don't have gaping holes in my project number list and so that the next number that does meet that criteria would display in the next available cell instead of being locked into one particular? I hope that makes sense.
Any assistance would be greatly appreciated.
Thank you so much!