• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Validate cells which are allready having formula.

A

atulrajratna

New Member
Hi team,

I have a work allocation sheet, which we use to allocate the work across the team. I have used =INDEX formula to distribute the work in available agents. Now some procedure has changed.

suppose column A is for file numbers & B is for agent names. B1 has agent name "John" (which is came by INDEX formula).

but John wants to reallocate only that file to "Mona".

but we cant change the name from "John" to "Mona" as B1 has =INDEX formula.


Before we used to allocate the files by validating the cell range (Agent names). now by using INDEX formula work has became so easy, but to reallocate the file, we are facing such problems.

do we have any formula or any option to make it possible?

or can we use validation and formula in a cell at a time?


Please help me,

Thank you!
 
Hi, Atul Rajratna!

Can you post the structure of the sheets and cells involved, indicating input and formulas or desired output? Or even better a sample file.

For what I understood, there's missing the input for the index formula, the decision to whom allocate a file to, what cells are for user input, and so.

Regards!
 
Hi,


I have uploaded a sample allocation sheet. please check with the following link.


https://skydrive.live.com/redir.aspx?cid=92bd629a339d0180&resid=92BD629A339D0180!107&parid=root


(Please read the info given in callout shape in "Agents" tab.)


Thank you!
 
Hi Atul ,


My personal opinion is that a formula should not use relative functions such as ROW() or COLUMN() ... unless there is an absolute need for them , in that form.


In your case , you need to retrieve an agent's name from a pre-defined list of agents ; an easy way would be to use an in-cell drop-down list , which allows you to modify the agent's name at any point of time.


By using a formula , and tying it to ROW() , unless you change the row in which the formula is used , or you modify the formula itself by changing the +1 to some other number , there is no other easy way to change the agent's name ; even if you want that a formula should be used to retrieve the agent's name , you have to specify the rules which the formula should implement.


To answer your original question , of whether you can have a formula and implement a drop-down list in a cell together , the answer is No. When you implement the drop-down list Data Validation feature in a cell along with the formula , both co-exist ; the moment you change the cell contents by using the drop-down list and selecting an option , the formula is overwritten with the selected value.


Narayan
 
Hi, Atul Rajratna!


As Jack (the ripper) said, let's go by parts. (Using it twice in a day...)


As far as I can see, I'd like point out a few things, and please correct me if I'm wrong:

1) the input of applications in column B of sheet Allocation is made on an incoming basis and that sheet is never reordered

2) the allocation of an agent to an application in column E of same sheet is made on a strictly criteria of balancing the applications per agent, and in sequential order: no preferences, no sequence changes, nothing alters the order

3) from b) I infer that any agent can get assigned any application and no agent can refuse an application, because of the assignation order

4) now that you have automatized the E column filling you are facing the issue of couldn't assign a specific agent to a specific application


If I'm right, then you can try doing this:

a) first of all, define a named range for agents, so you can easily reference it: AgentsList (note the formula change in Agents!D2

b) in column A of sheet Dynamic Chart you were retrieving the agent name with an index formula that varied from row to row, even you're referecing the same number of cells but with relative reference and selecting row 1 column 1: change to AgentList and a number for the rows (here you do yes have to use a ROW() function within a formula, and I agree with previous NARAYANK991's concept)

c) define a named range for Applications as $A$8:$I$800 as AllocationsList(apply proper format from row 457 in advance if needed, or reduce range scope) and another for ApplicationsNumber as $B$8:$B$800

d) column E of sheet Allocation: first copy E8:E100 on itself pasting values (to maintain previous allocations), set data validation on AgentsList to range E8:457 (no more formulas down),... et voila!, then you can define allocations as needed... but... nothing's free (see next point)

e) until now you didn't have to use any criteria for allocations since they were done automatically by row (agent) order, but now you have to define a new default criteria: personally I'll add a new column between actuals D and E, and there I'll write the previous E formula, so as to suggest what agent would have corresponded (that helps with balance but not at all, if you make many manual allocations, ... the goal would be finding a formula to put in new E column to suggest the less assigned agent... let's see.


I conditional formatted column E to hightlight manual changes, I let to you the creation of the optimal balanced formula (clues: check lesser values in Dynamic Chart per agent, and combine it with the E formula).


Here's the modified file, check it and see if works for you.

https://skydrive.live.com/redir.aspx?cid=3a8bdc8cdf4d772c&resid=3A8BDC8CDF4D772C!271&parid=3A8BDC8CDF4D772C!172&authkey=!ALkfF5YzRRwf10M


Regards!
 
Narayan & SirJB,

Thanks for replying.


SirJB, the sheet u have modified, is working and I'll try with it. Thanks a lot for your precious help.
 
Hi, Atul Rajratna!

I'm using 2010 version, and can get access to a computer with 2007. I am not sure about this case in particular on version 2003, but if I don't remember bad I've read in this forums that there was a workaround. If I find it, I'll let you know.

Regards!

PA: I've got an idea about how to automatically balance the allocations... give me a couple of days and I'll send you the modifications. I'm half travelling half working.
 
Hi, Atul Rajratna!


You're welcome.


And here's a little rude/rough workaround, but it all depends on how many conditions you may want to test simultaneously.

Supose you to COUNT column A data, when column B = X, column C = Y, column D = Z,... you may:

- use an auxiliar/helper column

- create there a value for testing with COUNTIF


For the example given:

[pre]
Code: 
asdf	X	Y	Z	2	XYZ	2
sd	C	Y	S		CYS
sd	D	Y	Z		DYZ
d	X	D	Z		XDZ
d12	C	Y	Z		CYZ
SDF	X	Y	Z		XYZ
SD	S	D	A		SDA
sd	S	D	A		SDA
d1	X	D	A		XDA
[/pre]
(2007/2010 version):

In E1 you have: =CONTAR.SI.CONJUNTO(B1:B9;"X";C1:C9;"Y";D1:D9;"Z") -----> in english: =COUNTIFS(B1:B9,"X",C1:C9,"Y",D1:D9,"Z")


(2003 version):

In F1:F9 you have: =CONCATENAR(B1;C1;D1) -----> in english: =CONCATENATE(B1,C1,D1)

In G1 you have: =CONTAR.SI(F1:F9;"XYZ") -----> in english: =COUNTIF(F1:F9;"XYZ")


This are simple cases, just an idea of how to implement it. If you have trouble building the helper column please advise.


Regards!
 
You must log in or register to reply here.
Back
Top