Any Excel Geeks around?

Post by **andy a** » Thu Jul 05, 2012 7:14 pm

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.

Post by **Joe** » Thu Jul 05, 2012 7:17 pm

Wrong forum surely?

Post by **The Ginge Reaper** » Thu Jul 05, 2012 7:17 pm

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.

Post by **andy a** » Thu Jul 05, 2012 7:20 pm

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")))

Post by **The Ginge Reaper** » Thu Jul 05, 2012 7:21 pm

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

Post by **andy a** » Thu Jul 05, 2012 7:24 pm

Cheers - much appreciated

Post by **The Ginge Reaper** » Thu Jul 05, 2012 7:26 pm

I'm not promising anything though

Post by **M H** » Thu Jul 05, 2012 7:27 pm

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............

Post by **D & B** » Thu Jul 05, 2012 7:39 pm

Think I'll sit this one out.

Post by **andy a** » Thu Jul 05, 2012 7:58 pm

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.

Post by **gazman** » Thu Jul 05, 2012 8:00 pm

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

Me too mate. Can't stand excel.

Post by **andy a** » Thu Jul 05, 2012 8:02 pm

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.

Post by **andy a** » Sat Jul 07, 2012 6:50 am

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")))

Post by **M H** » Sat Jul 07, 2012 6:51 am

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

Post by **andy a** » Sat Jul 07, 2012 7:01 am

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!