INDEX and MATCH , "Excel ran out of resources while attempting to calculate one or more formulars.."

[DIARYTODAY]

Hi All,

I am new to VBA and need some help with few formulas. The first question is the Index and Match formula, it does not work for me. I have 2 sets of data as follows:
Table 1: Employee Name(A1), Client Name that EE works this month (B1), Total Days EE works (C1)/month <-- this is my data, it changes every month
Table 2: Employee Name (D1), CLient Name (E1), Client Rate (F1)<-- this data is set (1 EE can have different rates for diff client)

I need to calculate Total Cost per EE per month. So my formula is :

G1=(INDEX(F:F,MATCH(A1&B1,D:D&E:E,0),1))*C1) CSE

It seems like it works but when I double click the corner of the cell G1 to copy it down, it copies all the way down to the bottle of the page pass the data table and it starts giving me the msg "Excel ran out of resources while attempting to calculate one or more formulas. As a result, these formulas cannot be evaluated".

Please help! I need this to work for my report. Is my formula wrong?
Thank you in advance.

 

[gaz_chops]

Try limiting the range?

=(INDEX(F2:F1000,MATCH(A1&B1,D2:D1000&E2:E1000,0),1))*C1)

 

[DIARYTODAY]

Try limiting the range?

=(INDEX(F2:F1000,MATCH(A1&B1,D2:D1000&E2:E1000,0),1))*C1)

It works gaz_chops, thank you ..but now its taking forever for Excel to calculate these..everytime I make 1 small change , its running very slow and it says "Calculating (4(processor(s)): 4% ( the % goes up sloowwwly)..do you know any thing I can do to speed this up?

By the way, I limit it to 10,000 since my report runs up to 8300 already and its not a full month, sometimes I have to run reports for 3 months or quarterly so its a lot of data to calculate..VBA might not be running properly then.

 

[gaz_chops]

Sorry VBA is not my forte, hopefully someone else will answer.

 

[DIARYTODAY]

Thank you anyways for the tips

 

[gaz_chops]

You're welcome, I asked someone else to see if they can help.

 

[DIARYTODAY]

gaz_chops, can you help me with this one?

So here is my table:
Table 1: EE Name(A1), prospect name (B1)(name is very long, i.e: AEM-xxxxxx, AEM-yyyyy, AEM-Advanced,etc..., AEP-xxxxx, AEP-zzzz,.... (AEM, AEP...are client names)
Table 2: EE Name (C1), prospect abbreviation (D1)

I need to abbreviate prospect names to calculate the rate for that client, AEM and AEP are same client (just diff entity), Advanced prospect has different rate so it has to be abbreviated as AEP- ADV, so my formula on D1 is:

=IF(ISERROR(FIND("AEM"&"ADVANCED",B1)),LEFT(B1,3),"AEP"&LEFT(B1,3)&" - ADV") It does not work

I also try
=IF(ISERROR(FIND("ADVANCED",B1)),LEFT(B1,3),LEFT(B1,3)&" - ADV")+IF(ISERROR(FIND("AEM",B1)),LEFT(B1,3),"AEP") but does not work either

thank you in advance

 

[gaz_chops]

Not sure I follow what you're doing, are you just trying to find if B1 contains either "AEM" or "Advanced"?

 

[gaz_chops]

Reading again, i think you are creating the Abbreviation, so if it finds Advanced then you want the 1st 3 characters & -ADV, if not just the 1st 3 characters?
If thats right try

=IFERROR(IF(SEARCH("*Advanced*",B1),LEFT(B1,3)&"-ADV"),LEFT(B1,3))

 

[DIARYTODAY]

Yes, I want to create Abbrev. since I have 100 client names and each client has another 50 prospects or more.

This formula works for me =IF(ISERROR(FIND("ADVANCED",B1)),LEFT(B1,3),LEFT(B1,3)&" - ADV") to find help me find prospect that has ADVANCED and gives me AEP - ADV, or PLM - ADV, which is what I want.

But then I realized client AEM is the same with AEP,so I was trying to add to the formula above to find AEM, replace with AEP instead. Make sense?