Automation of a spreadsheet

[tony0710]

Hi

I've attached a sample sheet, of my data input - this is then analysed using pivot tables.

There are a few automation processes that I would like to happen in the sheet - basically I want to input the least amount of information and allow excel to do the rest.

Although the sample sheet doesn't show it I've already got Col A (QTR), Col C (DAY), Col D (MONTH), Col E (WK No) automated on input of date in Col B - thanks to daddylonglegs and vba Noob.

For the rest I need help, so here goes:

1. Col F (INC/EXP) - this will be manually input.
2. Col G (Dept) - manually input
3. Col H (Amt) - manually input
4. Col I (category) - enter manually
5. Col J (Type) - automatic by excel based on entry in Col I
6. Col K (vat) - automatic by excel based on entry in Col J
7. Col L (ref) - automatic by excel as the next sequential number (once the data in autofiltered by ascending date) i.e. PCV273 will be inputted manually first, then when the next cash exp in Col J appears (after the data is auto filtered by ascending date - Col B - excel will place PCV274 in the corresponding cell. Same goes for Chq numbers.

So can someone have a look at this and see what they can come up with - I don't even know what functions to use!!

Any help will be much appreciated.

Regards

Tony

[royUK]

There's no example attached.

[tony0710]

Ok Roy

I've attached it again - hopefully it will be there now.

Regards

Tony

[tony0710]

Dear All Gurus

I was just reading my original posting and thought that there might be some ambiguity.

I wrote in point 7:
Col L (ref) - automatic by excel as the next sequential number (once the data in autofiltered by ascending date) i.e. PCV273 will be inputted manually first, then when the next cash exp in Col J appears (after the data is auto filtered by ascending date - Col B - excel will place PCV274 in the corresponding cell. Same goes for Chq numbers.

PCV273 is the first Petty Cash Voucher for the year, so basically I will input the first cheque number and PCV number of the year and then expect excel to start the sequence from there on.

Just thought that I'd clarify it.

Regards

Tony

[tony0710]

Dear All

After lots of research, I've managed to sort out a lot of the issues as mentioned in my original posting.

Unable to attach a sample file, as all are over the 100kb.

There is a an outstanding issue and a new issue that I need your expertise on:

Outstanding issue: automatic sequential numbering in col D (Ref) of the input sheet, after having autofiltered for ascending date - to apply to Cash Exp (Petty Cash Voucher - PCVxxx) and cheque exp - cheque number.

New Issue: have offset/match formulae to the VLT sheet - one of the formula is

=OFFSET(VLT!$B$1,MATCH(B2,VLT!$B$2:$B$57,0),-1)

The problem I have is that VLT sheet has a dynamic range - fixed columns, but dynamic amount of rows - so how should the above formula, especially the figures in red be altered to cater for the dynamic number of rows?

Thanks in advance to everyone that contributes to this.

Tony

[Bjornar]

If you replace:
VLT!$B$2:$B$57
with:
OFFSET(VLT!$B$2,0,0,COUNTA(VLT!B:B),1)
you will get a dynamic expanding range.

But I think in your case you could just use MATCH on the whole column like this:

=OFFSET(VLT!$B$1,MATCH(B2,VLT!$B:$B,0),-1)

[tony0710]

Hi Bjornar

Just to inform you that I tried both method that you suggested but both returned a #N/A error.

Regards

Tony

[Bjornar]

#N/A is what the MATCH function return when no match is found.

I see that you should add 1 to the MATCH result in my suggested formula, because your original lookup started in B2, and my formula started in B1 (the whole B column).

=OFFSET(VLT!$B$1,MATCH(B2,VLT!$B:$B,0)+1,-1)

But I don't think this is the source of your problem. Are you sure the value in B2 has a match in column B in sheet VLT ?

One tip when you get an error in a nested formula like this is to step thru the formula calculation using Tools -> Formula Audition -> Evalute formula. The you will se in what part of the formula the error occurs.