The Darts Forum

Hi,

I'm trying to help someone out at work and I'm stuck.

Basically they have a spreadsheet with product codes in and need a formula telling them which account to invoice it to based on the product code.

if it contains 'NFUCOM' and doesn't end in '/M' then account is 'NFU133'
if it contains 'NFUEMP' then account is 'NFU263'
if it contains 'NFUCFE' then account is 'NFU257'
otherwise it's account "NFU001"

I thought I'd done it but failed miserably. The worksheet is attached if anyone's feeling creative it's got my formula in which only works for the 1st rule.

Wrong forum surely?

Do those 6 character strings appear at the same place in each full product code?

If so there is a way of doing it using a MID and then an IF function but it's kinda messy and wouldn't be able to do it until tomorrow.

I think they could be anywhere in the product code. I tried using the find formula ....

=IF(AND(FIND("NFUCOM",G4,1)>0,(RIGHT(G4,2)<>"/M"))=TRUE,"NFU133",IF(FIND("NFUEMP",G4,1)>0,"NFU263",IF(FIND(G4,"NFUCFE",1)>1,"NFU257","NFU001")))

Can't get my head round that on my phone, I'll have a look tomorrow.

Cheers - much appreciated

I'm not promising anything though

Why the fuck are you using spreadsheets for invoicing when there's far easier options out there?

I happen to know someone who can convert excel into CSV and import it into accounting systems............

Think I'll sit this one out.

M H wrote:Why the fuck are you using spreadsheets for invoicing when there's far easier options out there?

I happen to know someone who can convert excel into CSV and import it into accounting systems............

It's not for invoicing it's for product that's been paid for but the customer needs a report showing which cost centre has had it's products called off.

D & B wrote:Think I'll sit this one out.

Me too mate. Can't stand excel.

gazman wrote:

D & B wrote:Think I'll sit this one out.

Me too mate. Can't stand excel.

I'm none to fond of it myself. Unfortunatley most of the people I work with are less adept than me so I get this sort of stuff to try and sort out.

Cracked it!

=IF(AND(IFERROR(FIND("NFUCOM",G12,1)>0,FALSE),(RIGHT(G12,2)<>"/M"))=TRUE,"NFU133",IF(IFERROR(FIND("NFUEMP",G12,1)>0,FALSE),"NFU263",IF(IFERROR(FIND(G12,"NFUCFE",1)>1,FALSE),"NFU257","NFU001")))

At 6.50 on a Saturday morning, that's sad mate!

It's pretty sad any time of any day in my opinion. Don't like being beaten by technology though and it struck me whilst I was making a coffee. Fucking computers!