Hello everybody,
Glenn helpt me to make a workform and it works fine.
Now i want to checked with conditional formatting to color the cells green or red when treu or false.
But i can't succed to make the formula if 2 cells controlled by another cell.
I have to attechments attached.
sheet changecolor is the way i want to work and sheet testalles is the whole sheet.
Can someone help me with the right formula?
Greets Roland
Maybe this:
Green:
=OR(AND($P3=1,COUNTA($Q3:$R3)=1),AND($P3=2,Q3<>""))
Red:
=OR(AND($P3=1,COUNTA($Q3:$R3)<>1),AND($P3=2,Q3=""))
I have applied this to the changecolour file, as there are no column labelled amount and containing 1 or 2 in the other file....
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
pff you're quick. Iam taking a good look tonight i am busy on the farm at this moment. At least thanlk you for the formula. Iam gonna implant it in the right form and come back if its working or not. Thx
Wonderfull i imp[lanted the formulas and change the cells. It works fineagain thank you. It's difficult to get the formula in the right order.
The other question if it is possible to check how many from a name in is very hard i think? Names can be changed and new names can be added or old names can be deleted?
Or is then the best way to set a couple of names and make a formula with aantalarg to count? When the name isn't in I:I en J:J keep empty "".
It would be nice but not really necessary. I made in the form P25 and Q25. If there is a possibility to count by name it would be nice.
Is this what you mean? IN P26:
=IFERROR(1/(1/COUNTIF($I$2:$J$14,Q26)),"")
Yes i think that is the easy way. I put the names in. I use lot of the same clients. That are not 100 people.
But i found a mistake if i fill J2 with a customer and I2 stays empty it works. But if i set a name in I2 and leave J2 empty the cells are blank and not green or red i renamed the file the early changes with name and number are there as well
I changed the cells in I@ and J2 so you can see it.
greet Roland
Youhad made a mistake transposing the CF formula:
=OR(AND($H2=1,COUNTA($I2:$J2)=1),AND($H2=2,I2<>""))
Your file.
Would you like a list of customers, that have been used, to automatically populate P24 downwards, instead of doing it manually?
Like this...
it's a small letter. LOl
You mean that if i fill in a new name in colum I or J, it will automaticly set in P24 downwards?
I like it how more automatic the less mistakes you can make.Iám not using a lot of excel, but like it to make a form for use in my own company. How more automatic the less mistakes you make.
I like your comment and tips so i cn learn a lot.
I see your in ireland, so i poor two tullamore dew in and we'll drink on your help.
Thx
Yes. It's now done in the last file i attached.
Pour some John Jameson's instead... I prefer it! Normally, though, I prefer beer (home made ale, prepared only from barley, hops, water & yeast).
Hhaha thats a long formula. I'll will take later a good look how it works, But i see something like macro and your using a lot of codes to let it happen. When i was learning this a little bit it was excell 2010. LOL.
Another questin then if youre so good at this.
I fill A2 with a number and then till H1 with a lot of data. I and J arejust for who is doing the job in holland or foreign country.
But if i fill A3 with the same number can it automatic fill in the rest till H1?
Then by example A4 with new data, then A5, A6 and A7 with the same number as A4. And so on.
Jameson is also good. And the beers yes as less as possible in it, just the necessary.
Yes... and No.
You would need (on another sheet?? ) to keep a table of project numbers and the constant columns. I'll do it and let you look. Easier than explaining it...
Yes... and No.
You would need (on another sheet?? ) to keep a table of project numbers and the constant columns. I'll do it and let you look. Easier than explaining it...
Is it correct that the date and Time (columns B & F) will NOT be constant, every time
So. Add project details in Project List sheet. I created a Named Range (CTRL-F3) to view/edit called "Projects", using this formula:
='Project List'!$A$2:INDEX('Project List'!$A:$A,MATCH(10^300,'Project List'!$A:$A))
as soon as you add a new project number in column A, the number is automatically picked up by that formula. It is used in the light grey cells in column A, Blad1. I used Data Validation to resrict the entries in that column (down to row 100) only to the projects in "Projects".
I then used this formula in the dark grey cells (C to E, G & H):
=IFERROR(VLOOKUP($A2,'Project List'!$A:$F,MATCH(Blad1!C$1,'Project List'!$A$1:$F$1,0),FALSE),"")
to pick up the remaining "fixed" details for each project. You still have to add date & time, as I guess they will NOT be the same every time.
No that is not what i mean. I now what youre trying to explain.
This list is for justone customer. The only thing is that de customer has a lot of transport in one day. I'll am the pilot. If this job is done i'll erase the form and start a new form with new orders.
In this form it goes about 13 transports with different orders from the customer. I need to make invoices by numbers of the customer.
So in this case project 5154 needs 2 pilots. 5155 3 pilots.
The simple thing is if i made row A2:H2. If i need a second one and fill A3=A2 then it automatic fills B3:H3. If A3 is a new order i fillin the rest. If A4=A5=A6. I think its the formula
I made an example in H15 till H18
Morning Glenn,
I succeeded with what i wanted in the form. Thanks to you its working for me.
imade a mistake in the area S21:V44, but i found it, en works again.
The formula of the area Q23:R42 is very special. It's to hard for me.
I also made some extra checks, as you cab see they become red in de sheet.
Thank you again Glenn. You made me very happy.
I attached our form another time.
Mayby youve got tips or tricks.
Greet Roland
Hmm i just discovered a mistake. When i delete the data from A2:I100 al the formulas are gone. So mayby your idea is better. I just hope that its possible the way i did. Of course yuo're coming up with a formula with 6 lines. LOL. Hope you understand what i mean. While i make everytime a new sheet with data. It isn't also for keeping. It's just for counting and make the invoice
Greet Roland
Sorry woke up and changed 2 rows. I and J needs to be next to each other.
I think your idea is better, but why wasn't date and time in your example?
I tried to use your formula, but don't understand it.
So i Attached a new sheet with 2 forms already in it.
I was very busy yesterday to tidy things up.
It takes a weekend, but i succeeded to complete my form. Thanks to Glenn who helped me again.
Hello Glenn,
Can you help me again.You helped with a wonderful form, but now some conditions are changed.
I made anpother sheet for the new counting and that works fine.But you made in sheet Hours! in colum L:M if K had a number, some conditional formating.
Now i wanted to do the same in sheet NL! in colum H:I but now it isn't working.
If D<>"" then cel H1 and I1 needs to be filled, and colored green. If one of the 2 is empty then empty red and filled green.
If D="" then one of the cells need to be filled, If one of the cells is filled both need to become green.
If the are both empty, both cells need to be colored red
What did i do wrong? I thought i copy the formula and change the parameters in sheet NL!
Greet Roland
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks