Excel progression tabs / wording

Copper Contributor

Hello community, I'd greatly appreciate a helping hand if possible!

 

I have an Excel file with a vast amount of data, I have created separate tabs for work progression. 

 

In the first tab I have a dropdown for 'Workable, InProg and Completed' I am looking to copy the status of 'Completed' over to the second tab and for this to then show as 'Workable', as this will enable a different group of people to work on the second tab.

 

I would like this all automated, so when person A updated the cell to 'Completed' on tab 1 it goes over to tab 2 as 'Workable'

 

If anyone has some ideas I'd be grateful.

 

Many Thanks, 

15 Replies
What if tab 1 is workable, in progress, or blank?

@Twifoo Thanks for the response!

 

We use the status to give our figures each day, so preferably I'd like them to remain 

If Work1 in Tab1 is Completed, Work1 in Tab2 is Workable. What I want to clarify are these:
1. If Work1 in Tab1 is Workable, what will Work1 in Tab2 be?
2. If Work1 in Tab1 is In Progress, what will Work1 in Tab2 be?
3. If Work1 in Tab1 is Blank, what will Work1 in Tab2 be?

@Twifoo I only need the completed in tab1 to show as workable in tab2, the other 'statuses' are not needed.

 

Work1 Tab1 Workable, would show as Blank on Tab2

Work1 Tab1 InProg, would show as Blank on Tab2

Wark1 Tab1 Completed, would show as Workable on Tab2

Your formula in Sheet2!B2 could be:
=IF(VLOOKUP(A2,Sheet1!A:B,2,0)="Completed",
"Workable",
"")

@Twifoo Thanks, I'm having issues with it returns just '0', I have amended the sheet names and adjusted some of the cells etc but I'm still have issues. 

 

I have attached some pictures for you to see, although they only show as workable on the screen grab further down there are some completed 

 

Tab2.PNGTab7.PNG

What’s your formula that returns 0?

@Twifoo 

=IF(VLOOKUP(A2,EXITED!A:B,2,0)="Completed","Workable","")
 
 
Attach your sample file so I can scrutinize it.

@Twifoo There's nothing to attach, that's all there is. 

 

The only thing I didn't add is that there's a dropdown list to select from Workable, InProg and Completed. 

 

When I enter the formula I get this message 

message excel.png

 

What’s the formula for the drop-down list? Perhaps, such formula refers to the same cell as that where the result of your VLOOKUP is located. So, the circular reference error occurs.

@Twifoo It's not a formula it's done via the data validation list option, if it makes a difference the list is kepted in STATS (sheet name) Completed (A40), InProg (A41) and Workable (A42)

Check whether the values in Column A of the EXITED sheet are referring to Column A of the sheet where your VLOOKUP is located, instead of the other way around. That might be the cause of the circular reference error.

V and H lookups become slow.

I would put a button on the forms and allow the people to use the honor system when they complete it.  The button will populate the main form back.  If you have as many tabs as I am thinking the sheet will become unworkable due to the amount of lookups when changing data.  Just give them an IM DONE button.  Heck you can even add in the start time of the tab they open and the finish time when they hit the button and if you want to get real high speed you can capture (input box) the employees id info.

 

@Loobc 

Thanks for the response. 

 

I just needed something like =INFORCE!B2 so it shows the status from the dropdown in 'Exited Payments' but I only need the completed to show. 

 

I thought the VLOOKUP needed the same data in in the left to work?