Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Excel 2003 Programming help...
Dear Friends,
I know VB programming & also knew that excel macro includes VB codes. But I don't know how to select cells ad how to check a data in a particular cell and inputing a data to another cell w.r.f to the checking cell. I'm using excel 2003. Example: A B C D E F G H I J K L M N O 1 - - - - - - - CA NS N - - - CANS 2 - - - - - - - CA WIP N - - - CAWIP 3 - - - - - - - CA C N - - - CACMPD 4 - - - - - - - CA C Y - - - CRRCVD 5 - - - - - - - RO NS N - - - RONS 6 - - - - - - - RO C N - - - ROCMPD 7 - - - - - - - RO C Y - - - RORDY We are doing telecom cell site project. Let me explain about the sheet snap i mentioned above. H column states the acceptance status of the site. This will have 3 status viz. C - Cleared, CA - Conditionally Accepted, RO - Reoffered. I column states the Pending jobs status. This will contain 3 status NS - Not Started, WIP - Work In Progress, C - Completed. J column states Ready for Reoffer or Compliance Report received.This will 2 status Yes or No. Now the scenario is that if the site has been Cleared, then no issues. If the site is CA, then the pending job has to be compelted and a Complaince Report to be submitted stating that the pending job are done. If the site is RO, then pending jobs to be completed and it has to be reoffered for another visit to client. If H = C, then no chekcking, direclty putting N = Cleared. If H = CA and I = NS, then O = CANS, that means site is Conditionally Accepted and pending job clearance Not Started yet. If H = CA and I = WIP, then O = CAWIP, means pending jobs clearance Work In Progress. If H = CA and I = C, then O=CACMPD, means site is CA and pending jobs Completd. If H = CA and I = C and J = Y then o = CRRCVD, means Compliance Report received. Same case for H = RO,except the last one if H = RO and I = C and J = Y, then O = RORDY. I have done this with nested IF formula but it supports till 7 if conditions only. So, kindly anybody help me out by giving some tips how to select the cells and it s values in a particular sheet or somethign like this. If you are unable to understand what I want to say then feel free to contact me on my emil id . I'll email you the worksheet itself. Thanks in advance Sriram |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Excel 2003 Programming help...
Addressing the 7 nested IF statement limit, there is a work-around that will
give virtually limitless nested IFs. Warning: trying to follow the logic in these MegaFormulas may lead to insanity. Consider the formula that Ardus offered you: =IF(OR(I1="NS",I1="WIP"),H1&I1,IF(J1="Y",IF(H1="CA ","CRRCVD","RORDY"),H1&"CMPD")) There are only 3 nested IFs here, but as you know, you could have up to 4 more. Now you name the formula as you would name a range of cells. Insert/Name/Define. Put your formula in the Refers To: box, give it a name at the top like Formula1 and click ADD. Do this for other formulas as well so you have formula names refering to multi IF statements. In the worksheet you use the formula names in other formulas. The Name is considered as only one argument in a cell formula, even though it refers to many arguments. ie = IF(formula1, formula2, formula3). If each named formula had 7 nested IFs, then this workbook formula would be evaluating 21 IF statements. Now re-read the warning at the beginning :) Mike F "Sriram" wrote in message ... Dear Friends, I know VB programming & also knew that excel macro includes VB codes. But I don't know how to select cells ad how to check a data in a particular cell and inputing a data to another cell w.r.f to the checking cell. I'm using excel 2003. Example: A B C D E F G H I J K L M N O 1 - - - - - - - CA NS N - - - CANS 2 - - - - - - - CA WIP N - - - CAWIP 3 - - - - - - - CA C N - - - CACMPD 4 - - - - - - - CA C Y - - - CRRCVD 5 - - - - - - - RO NS N - - - RONS 6 - - - - - - - RO C N - - - ROCMPD 7 - - - - - - - RO C Y - - - RORDY We are doing telecom cell site project. Let me explain about the sheet snap i mentioned above. H column states the acceptance status of the site. This will have 3 status viz. C - Cleared, CA - Conditionally Accepted, RO - Reoffered. I column states the Pending jobs status. This will contain 3 status NS - Not Started, WIP - Work In Progress, C - Completed. J column states Ready for Reoffer or Compliance Report received.This will 2 status Yes or No. Now the scenario is that if the site has been Cleared, then no issues. If the site is CA, then the pending job has to be compelted and a Complaince Report to be submitted stating that the pending job are done. If the site is RO, then pending jobs to be completed and it has to be reoffered for another visit to client. If H = C, then no chekcking, direclty putting N = Cleared. If H = CA and I = NS, then O = CANS, that means site is Conditionally Accepted and pending job clearance Not Started yet. If H = CA and I = WIP, then O = CAWIP, means pending jobs clearance Work In Progress. If H = CA and I = C, then O=CACMPD, means site is CA and pending jobs Completd. If H = CA and I = C and J = Y then o = CRRCVD, means Compliance Report received. Same case for H = RO,except the last one if H = RO and I = C and J = Y, then O = RORDY. I have done this with nested IF formula but it supports till 7 if conditions only. So, kindly anybody help me out by giving some tips how to select the cells and it s values in a particular sheet or somethign like this. If you are unable to understand what I want to say then feel free to contact me on my emil id . I'll email you the worksheet itself. Thanks in advance Sriram |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Excel 2003 Programming help...
Dear Mike,
As you suggested me, I've created separate formula for each of my results and all have come Good without error. But as you told, I've created Define Names with the mentioned formula and substitute them as you told like =IF(Formula1, Formula2, Formula3...), but it is not working. In L column I've the status like NR, ND, WH, D, WIP, ENP and C. My first formulae is IF($L4="NR", "NA") My second formula is IF(OR($L4="ND",$L4="WH",$L4="D",$L4="WIP"),"TNC") In 4th Row L Column I mentioned "NR" In 6th Row L Column I mentioend "ND" As you said after creating these two formula as Define Names with the names Forma1 and Forma2 respectively and entered the same in a blank column say in Z column of 4th row as below, =IF(FORMA1,FORMA1,FORMA2) But it is showing error as #VALUE. If the same formulae is entered in 6th row Z column, it is oming Correct. Because 6th row L column value is ND & giving result as TNC. This is correct. And 4th row L column value is NR, and it should show NA, insted it is showing #VALUE. Kindly help me out, if I've done any wrong in this. Thanks in Advance. Sriram "Mike Fogleman" wrote: Addressing the 7 nested IF statement limit, there is a work-around that will give virtually limitless nested IFs. Warning: trying to follow the logic in these MegaFormulas may lead to insanity. Consider the formula that Ardus offered you: =IF(OR(I1="NS",I1="WIP"),H1&I1,IF(J1="Y",IF(H1="CA ","CRRCVD","RORDY"),H1&"CMPD")) There are only 3 nested IFs here, but as you know, you could have up to 4 more. Now you name the formula as you would name a range of cells. Insert/Name/Define. Put your formula in the Refers To: box, give it a name at the top like Formula1 and click ADD. Do this for other formulas as well so you have formula names refering to multi IF statements. In the worksheet you use the formula names in other formulas. The Name is considered as only one argument in a cell formula, even though it refers to many arguments. ie = IF(formula1, formula2, formula3). If each named formula had 7 nested IFs, then this workbook formula would be evaluating 21 IF statements. Now re-read the warning at the beginning :) Mike F |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Excel 2003 Programming help...
You have done good so far, but you still need to qualify the IF statement in
the cell. The first argument always requires some kind of comparison like =, <, <, , etc. So just add a comparison to your first argument. In your case it would be: =IF(Forma1="NA",Forma1,Forma2) Mike F "Sriram" wrote in message ... Dear Mike, As you suggested me, I've created separate formula for each of my results and all have come Good without error. But as you told, I've created Define Names with the mentioned formula and substitute them as you told like =IF(Formula1, Formula2, Formula3...), but it is not working. In L column I've the status like NR, ND, WH, D, WIP, ENP and C. My first formulae is IF($L4="NR", "NA") My second formula is IF(OR($L4="ND",$L4="WH",$L4="D",$L4="WIP"),"TNC") In 4th Row L Column I mentioned "NR" In 6th Row L Column I mentioend "ND" As you said after creating these two formula as Define Names with the names Forma1 and Forma2 respectively and entered the same in a blank column say in Z column of 4th row as below, =IF(FORMA1,FORMA1,FORMA2) But it is showing error as #VALUE. If the same formulae is entered in 6th row Z column, it is oming Correct. Because 6th row L column value is ND & giving result as TNC. This is correct. And 4th row L column value is NR, and it should show NA, insted it is showing #VALUE. Kindly help me out, if I've done any wrong in this. Thanks in Advance. Sriram "Mike Fogleman" wrote: Addressing the 7 nested IF statement limit, there is a work-around that will give virtually limitless nested IFs. Warning: trying to follow the logic in these MegaFormulas may lead to insanity. Consider the formula that Ardus offered you: =IF(OR(I1="NS",I1="WIP"),H1&I1,IF(J1="Y",IF(H1="CA ","CRRCVD","RORDY"),H1&"CMPD")) There are only 3 nested IFs here, but as you know, you could have up to 4 more. Now you name the formula as you would name a range of cells. Insert/Name/Define. Put your formula in the Refers To: box, give it a name at the top like Formula1 and click ADD. Do this for other formulas as well so you have formula names refering to multi IF statements. In the worksheet you use the formula names in other formulas. The Name is considered as only one argument in a cell formula, even though it refers to many arguments. ie = IF(formula1, formula2, formula3). If each named formula had 7 nested IFs, then this workbook formula would be evaluating 21 IF statements. Now re-read the warning at the beginning :) Mike F |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Excel 2003 Programming help...
Dear Mike,
Thanks for your below help for my earlier problem. Now I've come up with another problem, can you please solve it for me. My self and 3 of my other colleagues are working in a Project Management Team with different roles. We use to track our work flow in the manner of Trackers separately. But all of our 4 data will contain around 50% of same details. One of our 4 members is the ultimate MIS updater and we other 3 use to collect the relevant common data once or twice a week and update with our records. It reflects a little time waste. So I've planned to create a Master file, so that our MIS after updating his file daily, the common data will be reflected in our rest 3 memeber's files. I did this. Even though I've planned to give some Password protection to the respective team member's sheets. For example let our Master file will be containing 10 sheets. 1st sheet will be containing our 4 names. 2nd is of the ultimate MIS executive's. 3rd and 4th will be of mine. 5-7 will be of the next one. 8-10 is for the last member. Now my question is that, the Master file will be available in our server and at any time anybody can open this. While opened only the First sheet should be Visible rest all should be Hidden/Invisible. Whenever the respective person clicks his name, a password should be asked and after entering his password, then his respective sheets should be visible and he can make his update. After updating he can normally close the file. The same case for rest also. Kindly tell me option how to accomplish this. My quetion is to Show / Make Visible one or more sheets by giving password and that too by clicking the respective names/links. Thanks and Regards, Sriram Subramaniyan "Mike Fogleman" wrote: You have done good so far, but you still need to qualify the IF statement in the cell. The first argument always requires some kind of comparison like =, <, <, , etc. So just add a comparison to your first argument. In your case it would be: =IF(Forma1="NA",Forma1,Forma2) Mike F |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 Programming | Excel Discussion (Misc queries) | |||
Excel 2003 Power Programming With VBA | Excel Programming | |||
Excel 2003 & add-in programming | Excel Programming | |||
programming excel 97 from .net 2003 | Excel Programming | |||
Programming in Excel 2003! | Excel Programming |