Formula to remove empty cells

[Stevetron]

Hi,
I would like some help. I have a daily record that requires 5 data entries per day. I record the date one, then the 5 entries of data. Then repeat daily.

the problem I have is, in column A the first date is in A3, then the next date is in A8, A13 and so on.

i want a second sheet, let’s call it sheet2, to show the dates without the 4 empty cells.

for example I want sheet2A3 to show sheet1A3, sheet2A4 to show sheet1A8.

I hope this makes sense and somebody can help me. Thanks in advance

 

[pjmorris]

HI Steve,

Many thanks for providing a more detailed example. The simply answer is you haven't entered the formula correctly - I'm guessing you've forgotten the brackets around the (ROW()-3). The best way to post your spreadsheet is to use the XL2BB add in (Linked above) s that can include the actual formula's as well, without it its difficult to say exactly what has gone wrong.

Having seen your source sheet I would offer the following suggestions (the first two being from mikerickson at post #4)

1. Copy your dates down alongside all data entries.
2. Highlight the data column from cell A4 down, enter a Conditional Format (Home Ribbon), 'New Formatting Rule', 'Use a formula to determine which cells to format', enter the formula =A3=A4 and set the formatting so that the font colour is white. This will hide the 2nd-5th copies of the date.
3. On Sheet2 in cell A3 =OFFSET(Sheet1!$A$3,(ROW()-3)*5,0) and copy down.
4. On Sheet 2 in cell B3 =OFFSET(Sheet1!$M$3,(ROW()-3)*5,0) and copy down.

There are other more effective solutions, but they are quite a bit more complicated to implement - though when you have the time they're worth learning (Powerquery and PowerPivot).

HTH

 

[Stevetron]

This is sheet 2

NEW RESIN ratio record trial for graph.xlsx
	A	B
1
2	Date	Daily average
3	01-Jun
4
5
6
7
8	00-Jan
9
10
11
12
13	00-Jan
14
15
16
17
18	00-Jan
Sheet9

Cell Formulas
Range	Formula
A3:A18	A3	=IF(ISBLANK('July 2020'!A3),"",OFFSET('July 2020'!$A$3,(ROW()-3)*5,0))

this is sheet 1

NEW RESIN ratio record trial for graph.xlsx
	A	B	C	D	E	F	G	H	I	J	K		M	N	O
1	Date	Cup A	Gross A	Net A	Cup B	Gross B	Net B	Ratio	Target	Upper Limit	Lower Limit		Temperature ©	Daily Average	Notes
2
3	01-Jun	0.000			0.000				8.500	8.755	8.245			0.000
4		0.000			0.000				8.500	8.755	8.245
5		0.000			0.000				8.500	8.755	8.245
6		0.000			0.000				8.500	8.755	8.245
7		0.000			0.000				8.500	8.755	8.245
8	02-Jun	0.000			0.000				8.500	8.755	8.245			0.000
9		0.000			0.000				8.500	8.755	8.245
10		0.000			0.000				8.500	8.755	8.245
11		0.000			0.000				8.500	8.755	8.245
12		0.000			0.000				8.500	8.755	8.245
13	03-Jun	0.000			0.000				8.500	8.755	8.245			0.000
14		0.000			0.000				8.500	8.755	8.245
15		0.000			0.000				8.500	8.755	8.245
16		0.000			0.000				8.500	8.755	8.245
17		0.000			0.000				8.500	8.755	8.245
18	04-Jun	0.000			0.000				8.500	8.755	8.245			0.000
19		0.000			0.000				8.500	8.755	8.245
20		0.000			0.000				8.500	8.755	8.245
21		0.000			0.000				8.500	8.755	8.245
22		0.000			0.000				8.500	8.755	8.245
23	05-Jun	0.000			0.000				8.500	8.755	8.245			0.000
24		0.000			0.000				8.500	8.755	8.245
25		0.000			0.000				8.500	8.755	8.245
26		0.000			0.000				8.500	8.755	8.245
27		0.000			0.000				8.500	8.755	8.245
July 2020

Cell Formulas
Range	Formula
D2:D27,G2:G27	D2	=IF(C2<0.01,"",C2-B2)
H2:H27	H2	=IF(C2<0.01,"",IF(F2<0.01,"",D2/G2))
E3:E27,B3:B27	E3	=F2
N3,N23,N18,N13,N8	N3	=SUM(H3:H7)/5

This is how i would like sheet2 to look like

NEW RESIN ratio record trial for graph.xlsx
	A	B
2	Date	Daily average
3	01-Jun	xxx
4	02-Jun	xxx
5	03-Jun	xxx
6	04-Jun	xxx
7	05-Jun	xxx
Sheet1

Hope this helps. I have added ISBLANK to your formula, it is a habit.

 

[Stevetron]

I should have added sheet1 is named July 2020, sheet2 is named sheet9

 

[pjmorris]

Hi Steve,

I think you can guess why it doesn't work, and apologies for sounding annoyed, but where did I say add 'IF(ISBLANK...'? To discover why it doesn't work firstly simply use the code I offered and prove that that does work, then add back your ISBLANK and use 'Evaluate Formula' from the Formula Ribbon to discover exactly why the addition is causing the problem.

Based on the screen shot above (for which, thank you) you'll need to change the M to an N in the formula for column B.

HTH

 

[Stevetron]

Hi Peter,

Thank you so very much, and i apologize, I should not have added anything to your code. Your help and responses have been excellent.

I have now input your code and both columns are working perfectly. I tried re-adding the ISBLANK and used the evaluate formula, but to no avail. I believe my knowledge is lacking too much to understand.

 

[pjmorris]

OK, firstly, glad that its now working!

Lets assume you're looking at Cell A4 on Sheet2. The ISBLANK will test Cell A4 on Sheet1, which is blank, so it returns a blank and ignores the OFFSET formula, Only when it reaches cell A8 on Sheet2 does the ISBLANK return false and then try the OFFSET Formula, but that then points past your data range.

HTH

 

[Stevetron]

Hi Peter,

Thank you for all your help. I understand now why the ISBLANK will not work as I input it, but undersdtanding how to get it to work is beyond me. Thank you for all your patience and assistance with this. It is very much appreciated.

I am going to start a new thread, as i would like to know about a graph that auto updates as new data is input.

Thanks again.