Dynamic Range in Formula from text

Dear All,

I have a list of criteria with different "IF" conditions for all criteria. i.e for criteria1 "GGG" it should use If(xxxx) formula and for criteria "GGR" it should use If(yyy) formula. As the no. of criteria is large and all have unique If formula (around 64) , I need to apply each unique "IF" formula on my data.

My method:I wrote all "IF" formula for 64 criteria (text format) and used vlookup to call the formula and using "Evaluate" in Name Manager to apply the formula. But the problem is in all "IF" formula the Range is fix as written in formula. How to make this dynamic in datasheet. (Z7:AE7 as shown below)

criteria1 = IF(COUNTIF(z7:ae7,"G")>3,"R"...........) -
criteria1 = IF(COUNTIF(z7:ae7,"G")>2,"G"...............)

Thanks for your time

attachment: Pls check attachment, there will be easier way to do I guess. Based on criteria for first three time periods, (there are 64 criteria )each criteria requires counting G,B+,B- and R and accordingly call it as A/B/C/D

Will you please attach a sample Excel workbook? I suspect that there is a much more efficient way of doing what you are trying to do.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

Thanks for letting us know.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.