Help with a forumla

tbcomputerguy

New Member
Joined
Mar 27, 2019
Messages
5
I am trying to get a formula to work. How do i reference a change in cells. As you can see i am copying the range on the left. when there is a change in the headings (A1,A3, A5) etc i would like to to show in N2, N4, N6 ETC. The blank rows on the right are rows i have deleted. I hope i have explained this well.

 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,449
Office Version
  1. 2010
Platform
  1. Windows
if the times are real excel times you could test the A column for text using this code;
Code:
=IF(ISTEXT(A2),A2,"")
If the times are entered as text as well, then you could test the first character for being numeric using this code:
Code:
=IF(AND((CODE(LEFT(A3,1)))>47,(CODE(LEFT(A3,1)))<58),"",A3)
 

tbcomputerguy

New Member
Joined
Mar 27, 2019
Messages
5
The first formula is wrong as it picks up the time not A1 - the heading. It works if I am using it on one line. But if for instance on N6-N9, the result calculates to the time. they should all calculate to what is in A5. Likewise N13-14 should result in what is on A12. Follow me?

DAve
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,449
Office Version
  1. 2010
Platform
  1. Windows
No, I don't follow you, what I have tried to solve for you is how to detect the change. Your picture of your data isn't clear because it looks like the header is in a merged cell, so I don't actually know what is in which column. You appear to have changed your requirement between your first post and your last.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,614
Messages
5,523,906
Members
409,542
Latest member
Shezz01

This Week's Hot Topics

Top