Replies

Right, you have skirted my issue by not having the borders turned on

I poorly explained the issue. The issue is, if the borders are turned on, they will be an angled flag that extends to the right of the corresponding merged cell above them. What I was trying to do was have the merged cell’s border match up with the angled flags.

The hack and whack way I got around it was to add a column to the left and right to make the angled flags reside inside a larger rectangle. I then turned off the interior borders so you could not really see what I did. The enclosed picture shows what I did.

Thanks,

Andy

Thanks for getting me unwrapped from the axle

Have a good one – that worked like a champ

Andy

OK, I am trying to implement the [=10.25/(C5/60)/24] formula and it works but when I enter the data, I get values such as:

22:07
22:37
23:35

but then, the ones greater than 24, change

24:37 appears as 0:37
25:57 appears as 0:57

if I look at the formula field near the top of Excel the upper values show as 10:07:00 PM and the ones over 24 show as 01/01/1900 12:37:00 AM.

The formatting for the cells is Custom h:mm in all cases.

I can not figure out how to get that date out of the value. I want to show both the times in min:sec, along with the mph values.

Thanks and sorry to be a pain…

Andy

Geez Rory – that was kind of simple

That makes the calculation much easier. Any thoughts on the entry of the time without the preceding “0:”, so I can just enter minutes:seconds?

This group never ceases to amaze me with how simple things can be

Thanks!

Andy

Thanks! I hate it when I am off in left field

Andy

Yes it does. I could have sworn that I tried that

Obviously I had dazzled myself on that one, and I may have been messing with the Updates tab column format.

Thank you very much!

Andy

OK, here is a neutered version of the file. If you look at the dashboard page, the problem is with the last column. The equations in that column are not consistent due to me messing around trying to get them to work.

Thanks,

Andy

Thanks – I did not realize that ESC cleared the buffer I knew the answer was in front of my face

Thank you!

Andy

You could use the INDIRECT function: the formula

=INDIRECT(“Updates!B4”)

will remain the same when rows are inserted or deleted. To avoid the hassle of having to modify the formula for each row, you could use

=INDIRECT(“Updates!B”&ROW())

This can be filled down.

Alternatively, if the values in column A are unique, you could use VLOOKUP formulas, for example in B4:

=VLOOKUP($A4,Updates!$A$1:$B$60000,2,FALSE)

This formula can be filled down too.

Thanks Hans!

VLOOKUP is the ticket. The only issue is the changing range on the other sheet. My solution was to set my lower row limit much higher than the existing 100 rows of data. I made it 200.

Thanks again!

Andy

Thanks Hans!

You made my hack and whack solution way more elegant. Now if I can only remember this one this time

Thanks again!

Andy

Thanks Hans!

Thanks Hans! I added a Monthly Summary row at the bottom and used your formula to bring down the latest value. Then my summary calculations are always up to date. Works like a champ. Thanks!

Thanks Rory! Modified bit of code below:

Dim intLen As Integer
Dim intPos As Integer
intLen = Len(ActiveCell.Value)
intPos = InStr(ActiveCell.Value, ” “)
ActiveCell.Value = Right(ActiveCell.Value, intLen – intPos)
ActiveCell.Offset(1, 0).Select

So what is the equivalent to the “search” function in VBA?

I was messing around with this thread and tried to make a function that would do the following. The cells contain two columns of data that need to be separated. A cell contains “leftside rightside” with a space between the two vaules.

Dim intLen As Integer
Dim intPos As Integer
intLen = Len(ActiveCell.Value)
intPos = Search(” “, ActiveCell.Value)
ActiveCell.Value = Right(ActiveCell.Value, intLen – intPos)
ActiveCell.Offset(1, 0).Select

This yields “Sub or function not defined” for “Search”. The first question is how do I “search” in VBA? The second question is, is there a better way to take data like this and return one side or the other? I can create a cell formula and create a new column, but I wanted to create a macro that could be used to change the data in place.

Thanks,

Andy

Thanks Legare,

I forgot about the limitation of three items for conditional formatting. I actually need more than 3. Is there a way around that? I think I saw something before, but do not remember how it was done.

Thanks,

Andy