Posts by boombox

Re: Formula for a bank statement running balance calculation

Hi Segu,

I'm wondering if I've just asked the world's stupidest question, because I've just tried this and it works fine...

Let me come back to you when I've tried it on my real (slightly more complicated but not by much) data.

Thanks

Hi

I can download statements from my bank (HSBC, for future Googlers) in the following format. Does anyone know what formula I can use to show a running balance in column D (i.e. to the right of 'amount')?
I've tried various things and the only way I've had success is to filter the credits and debits into seperate columns due to the way the formula interprets the '-' symbols...

Thank you!
[TABLE="class: grid, align: center"]

[TD="width: 64, bgcolor: transparent"]date[/TD]
[TD="width: 64, bgcolor: transparent"]description[/TD]
[TD="width: 64, bgcolor: transparent"]amount[/TD]

[TD="bgcolor: transparent"]01.12.2011[/TD]
[TD="bgcolor: transparent"]supermarket[/TD]
[TD="bgcolor: transparent, align: right"]-10[/TD]

[TD="bgcolor: transparent"]02.12.2012[/TD]
[TD="bgcolor: transparent"]wages[/TD]
[TD="bgcolor: transparent, align: right"]1000[/TD]

[TD="bgcolor: transparent"]04.12.2011[/TD]
[TD="bgcolor: transparent"]butcher[/TD]
[TD="bgcolor: transparent, align: right"]-5[/TD]

[TD="bgcolor: transparent"]06.12.2011[/TD]
[TD="bgcolor: transparent"]child benefit[/TD]
[TD="bgcolor: transparent, align: right"]80[/TD]

[/TABLE]

Re: Vlookup / networkdays required

Ooops - I got an infraction for a dodgy thread title! Ho hum.

Thanks very much for your suggestion, krish, but I get #N/A when I try that?

Re: Help with Interesting vlookup / networkdays required - please.

Thank you! I have produced the following example.

Hi,

I have what I think is a slightly unusualy use for a Vlookup.

I have a spreadsheet with the following 3 columns (simplified): PO, Mfr Part Code & Due date.
I then have another spreadsheet with the following: PO, Mrf Part Code & Working Days.

Spreadsheet 1 is a big sheet with lots of entries. One PO can contain lots of different Mfr part codes and the Mfr Part codes can also be found on other POs. It could even come to pass that a Mfr Part code might appear twice for one PO (if a qty is increased a new line is added to the sheet).

What I want to do is do a vlookup to find the instances where the PO and Mfr part code match, then copy over the due date to spreadsheet 2 and then calculate how many working days (on spreadsheet 2) between today's date and the imported due date.

If this is the kind of thing which is explained better with uploads - I am happy to provide. I have tried Googling to no avail.

THANKS in advance.

Re: Drawing a border around unique cells

Fantastic! Thanks!

Re: Drawing a border around unique cells

Have I broken protocol somehow? If so please let me know.

Hi,

I've got some code that I've modified to draw a box around unique cells in a column. But what I'd like it to do is draw a box around the rows which hhare the unique cells.

Sub BorderMe()
Dim rngStart As Range
Dim rngCell As Range
Set rngStart = Range("I4")
' set the defined range to 1 row more than your actual data
For Each rngCell In Range("I4", Range("I" & Rows.Count).End(xlUp).Offset(1, 0))
If rngCell.Value <> rngStart.Value Then
With Range(rngStart, rngCell.Offset(-1, 0))
.BorderAround xlContinuous, xlMedium, xlColorIndexAutomatic
Set rngStart = Range(rngCell.Address)
End With
End If
Next
End Sub

Any help much appreciated.