I’m looking to automate a stage process, where when a date is entered in a specific column it updates a cell with a specific number.
I’ve mocked this out, so that in the scenario, the progress column should update automatically.
It was excellently suggested by a colleague to simply add up the columns to replicate the stage, but this isn’t always going to be the case that each date is filled in.
Any suggestions welcome please!
Not sure if this is what you are after
but if you enter this in your progress column and then copy it down.
Please Login or Register to view this content.
Regards
Sean
Please add to my reputation if you think i helped (click on the star below the post)
Mark threads as "Solved" if you have your answer (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [code]
Please supply a workbook containing example Data: It makes its easier to answer your problem & saves time!
Hi
How about in B4 and copy down!
Formula:
Please Login or Register to view this content.
EDIT: if you don't want 0, if all the cells are empty, then you can wrap in a IF:
Formula:Please Login or Register to view this content.
Last edited by Kevin UK; 01-15-2013 at 02:49 PM.
Regards Kevin
Merged Cells (They are the work of the devil!!!)
This works great and does exactly what I need. However, one small issue, is that I have more than 8 Stages (I know I didn't post that on the mockup) so therefore I run out of brackets.Originally Posted by Sean Thomas
Not sure if this is what you are after
but if you enter this in your progress column and then copy it down.
Please Login or Register to view this content.
This works well, but if all dates aren't filled in (which they probably won't be as it's a sheet multiple users are looking editing) then it will unfortunately give the incorrect information. This will be have to be my go to incase anything more accurate isn't available.Hi
How about in B4 and copy down!
Formula:Please Login or Register to view this content.
EDIT: if you don't want 0, if all the cells are empty, then you can wrap in a IF:
Formula:Please Login or Register to view this content.
how many stages do you need?
i thought 2007 & 2010 can hold max 64 if statements
Please Login or Register to view this content.
It's actually 9 stages. I've got to Stage 8 successfully, when I try and enter a 9th I get
"The specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format".
EDIT: I know I put 10 and not 9, it's intentional...Please Login or Register to view this content.
does it need to be excel formula or could you use macros?
try this to find last non blank cell in a range
Please Login or Register to view this content.
Another idea, which I’m not sure how to write, but can you have one outside of the nest?
(Written in part English, part Excel)
IF(U6<>"",10,"") OTHERWISE =IF(T6<>"",8,IF(O6<>"",7,IF(N6<>"",6,IF(M6<>"",5,IF(K6<>"",4,IF(J6<>"",3,IF(I6<>"",2,IF(H6<>"",1))))))))
Hi highguyuk
I have read this thread 3 times and i still don't understand! Perhaps it's me, can you upload another sample indicating what you are after.
Ok. Here is the actual sheet, with content removed.
Column G is to update dependant on the different stages, listed in Row 3. There are 9 stages (at present) labelled 1-8 and 10. 10 is cancelled, so if ever anything is in Column U no further work will be carried out on that row and the stage should read 10.
The current formula, as suggested by Sean Thomas, works great. But has limitations on the nested elements, which is why I wondered if it's possible just to add one more function outside of the current formula which works.
Additionally, ideal but not compulsory, is that if there are no items filled out in any of the stage cells, the formula sets the Cell to "0" and not blank.
Hi
I will have a look. Nothing like your sample file in post #1 though!
Maybe not, but the solution isn't too far off! :-)
Hi
Have a look at the attached, the problem is with the way it is set out. If the data in columns L, P:S were after column U, might make things a lot simpler.
Last edited by Kevin UK; 01-17-2013 at 05:42 AM.
ignore this post
Last edited by Teethless mama; 01-16-2013 at 11:34 AM.
hmm, i agree. because you have a non contigeous range this makes things more difficult to come up with a single formula.
However, if you were to add another column before M and another before T, you could then do the following.
in the cell before stage 8 (in the current workbook S)
then in cell before stage 5 (in current workbook L)Please Login or Register to view this content.
Then in cell GPlease Login or Register to view this content.
this will then check the last 2 stages and if a date is entered it will pass the stage to coumn GPlease Login or Register to view this content.
if both are blank it will check stages 5,6,7 and enter the stage in column G
else it will check stages 1-4 and show which was the last stage.
hope this is clear?
@ Sean Thomas
Why do you want to add extra columns and extra formulas! When you can simplify things and use the INDEX & MATCH to return the header value!
The only problem is column O. Why has the OP has changed from using dates to using text! If they keep the column as dates, then a simple INDEX and MATCH can be used.
Formula:Please Login or Register to view this content.
Last edited by Kevin UK; 01-17-2013 at 05:43 AM.
Kevin
Thats exactly the reason why i suggested that method.
By having columns in between that arent dates/stages, it affects all the usual methods of returning the values.
With my method it wont matter and the additional columns can be hidden.
I cant really see any other solutions apart from using VBA.
Well I see the OP has visited at 12:04. Must mean they are happy. because they have not commented!!!!!
I wanted to fully understand the suggested solutions before commenting again, don't want to waste peoples time in asking pointless questions I can hopefully work out myself!
At first, I didn't understand the principal behind #16 after a brief first read. Understand that now though.
As raised in #17, the original version of this contained Y/N rather than dates and is a tool currently in use that I'm developing to include more functionality. In all likelihood, in the near future we'll need Column O to be a date field rather than Y/N so I'm happy to implement that now if needbe if it makes the system and solution easier.
Hi highguyuk
If you can use a date, I think it will simplify things. The choice is yours.
If you go the date way for column O,are you going to be using numerical values in columns L,P:S!
Also if you need to display the results as in the sheet I posted. you will have to change the headers in H3:K3,M3:O3,T3:U3, they are numbers and formatted as "Stage "0
Column O forthwith will be a date. I'm unsure how this make things simplier though, or does the MATCH/INDEX solution not work unless it is a date?
L,P:S unfortunately need to remain in their location within the sheet. L is a date, P:S are text.
I copied your solution into the live sheet and wondered why it was appearing "Stage 1" instead of "1". Managed to teach myself the new trick before your reply!
As long as column O is a date, Everything should be OK.
I think that closes everything out then!
Thanks very much for your help - and teaching a dog a new trick!
No problem. Time to make my way through the snow!
Unfortunately, having implemented the solution, the Stages don’t appear to be updating correctly.
I’ve attached the current situation, where you can see for example Row 13-15 and 17-22 should all be at Stage 8 on Column G whereas only 1 Row is showing correctly. Additionally, Row 57-58 should be stage 5 and not 4.
Excel Mockup 3.xls
Hi
What if you change the year in the cells of column T from 2012 to 2013!
That doesn't resolve issues on Row 57-58, where two dates are the same. So this solution doesn't work in that scenario?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks