Invoicing Lookup Formula

[warpsys]

Hi,

I have and excel spradsheet that contains invoicing data and I need t
create a lookup formula as follows

Column A contains the names of all projects that I maage. The example
of the project names are

EFF
BDD
JHH

The invoice numbers run in sequence dependant on the project name, ege

EFF1
EFF2
EFF3
BDD1
BDD2

Each time an invoice is created it takes the next number in sequenc
for that project.

The spreadsheet is quite large now and the projects are not listed i
any order (and there are blank rows within the data) so what I a
trying to create is a lookup formual that will find the last invoic
number for a paticular project and either, tell me what it is or, eve
better, insert the next number in the relevant cell.

Any pointers as to how to do this would be great

Many thanks

Pau

 

[Ken Wright]

Ok, so your project names are in Col A, where are your Invoice numbers? Are
they horizontally across the page, or vertically in another column. I'm assuming
they all start with a 1, eg EFF1, and how far up do they go? Would you have an
EFF12 say. Are all project names 3 characters long? What is 'the next
number'? - is this the invoice amount - where it is located in relation to the
invoice number - Above/Below/Right/Left etc Give us a bit more detail about the
structure of your data.

 

[warpsys]

Thanks for the reply

Projects are in Cloumn A
Invoice No's in Column B

The numbering is in the sequence you said ->

EFF12
EFF13
.....EFF122 etc

At the moment most projects are three alpha characters, thoough no
all.

The remaining columns are informational data and not really associate
with the formula. That is, the spreadsheet has a list of informationa
detail about the projects but the invoices themeslves are created i
Word. The bis problem that I have is that, when I want to create th
new invoice in Word, it's very difficult to know what the next invoic
number should be - hence the lookup request.

Hope this will help

Thanks

Pau

 

[Ken Wright]

OK, assuming that you do not skip ANY numbers when creating the invoices, and
further assuming that your projects start in A3 with values starting in B3,
then:-

with the project being looked up in A1, in B1 put the following:-

=A1&COUNTIF(A3:A1000,"*"&A1&"*")

If you want to return the value associated with that invoice, then use:-

=VLOOKUP(B1,A3:B1000,2,0)

CAVEAT:-

If you have names such as EPP and EPPA for two different projects this will
fail, and I need to rethink it. Reason being that if there 10 EPPs and 15
EPPAs, it would return EPP25 for EPP as thsi would be found in the EPPAs as
well.

 

[warpsys]

Ken,

Thanks - that works great. There was one small thing that I omitted t
say ! On some occasions, the invoice number will be used in more tha
one row, as per below. Is it possible to allow for this ?

Project Invoice No.

EHH EHH - 108
EHH EHH - 4
LSL LSL - 27
SWL SWL - 28
SWL SWL - 27
EHH EHH - 77
EHH EHH - 92
EHH EHH - 106
EHH EHH - 111
LSL LSL - 31
LSL LSL - 32
LSL LSL - 35
EHH EHH - 113
EHH EHH - 114
EHH EHH - 115
EHH EHH - 115
EHH EHH - 116
EHH EHH - 116
EHH EHH - 117
EHH EHH - 117
EHH EHH - 118
EHH EHH - 119
EHH EHH - 120

Many thanks

Pau