Report

Data auto change on multiple status in one column [Solved]

Ask a question -Hta- 5Posts Thursday February 16, 2017Registration date February 21, 2017 Last seen - Last answered on Mar 3, 2017 at 06:35 AM by Mazzaropi
Hello again,

Few days ago i am looking for a command that can use to change data status in one same column. Example : 'Activated' change to 'Expired' when it due. This case is closed perfectly.
Thanks to Mazzaropi

Now I have same issue but different needs. Anyone can help me how to change data in 3 or more status in one same column?
Example: 'Perfect' > 'Good' > 'Fair' > 'Poor'

Thank You.
See more 
Helpful
+1
plus moins
-Hta-, Good morning.

The best and most professional way to solve this case is by using a separate table and using the VLOOKUP function to find the status.

I've set a little example for you.
Please check the file and tell us if this is what you wanted.

https://www.sendspace.com/file/mi7998

I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
-Hta-- Feb 28, 2017 at 01:57 AM
Good Day!
Thanks for your reply and sorry late to reply from me.
For the mention case, i think its too complicated and should leave it as we update it manually as before, but i have new case and it just like same issue. i wish i can send u the both file via email but i try to describes it here:

A1-A20 is a enrollment date
B1-B20 is a expire date (365 days after enrollment)

below is a status fomula that i did and wish to combine it in one cell:

G1-G20 : =IF(B1>TODAY(),"active","")
H1-H20 : =IF(B1>TODAY()-5,"","expired")
I1-I20 : =IF(AND(B1<=TODAY(),B1>TODAY()-6),"reminder","")

can it be done? or there is another way to make it work?

Thank You
Reply
Leave a comment
Helpful
+1
plus moins
-Hta-, Good morning.

"...For the mention case, i think its too complicated and should leave it as we update it manually as before, ..."
I'm sorry.
Maybe my example was not very clear to solve your doubt.
The issue is not complicated.
It is very simple and classic in the Excel world.
I gave you an example with a function prepared exclusively for this type of case.

You may want to resolve this using another function.
Maybe a nested IF function.

I built another example by offering both suggestions as a solution.
https://www.sendspace.com/file/1pj799

Check if this helps.

You can ask the questions you want about it.
The idea is to help you solve your problem at work.
You can count on us.

You can save your excel files to any free sites, www.sendspace.com or ge.tt or www.dropbox.com and put the link to download the files here.
This way we can help you directly in your work files.
--
Belo Horizonte, Brasil.
Marcílio Lobão
-Hta-- Mar 2, 2017 at 05:09 AM
Hi Mazzaropi, Good Day!
Thank You very much for your file, that is what i need.
If you dont mind, can u teach me the formula using vlookup in this file?
Hope you can teach me on 2nd issue :-

A1-A20 is a enrollment date
B1-B20 is a expire date (365 days after enrollment)

below is a status fomula that i did and wish to combine it in one cell whether using "if" or vlookup formula

G1-G20 : =IF(B1>TODAY(),"active","")
H1-H20 : =IF(B1>TODAY()-5,"","expired")
I1-I20 : =IF(AND(B1<=TODAY(),B1>TODAY()-6),"reminder","")
Reply
Leave a comment
Helpful
+0
plus moins
-Hta-, Good morning.

Thanks for the feedback.

You have to tell us what the rules are for each status to appear.

What are the comparison cells.

Please, without information the help for you becomes precarious.
--
Belo Horizonte, Brasil.
Marcílio Lobão
-Hta- 5Posts Thursday February 16, 2017Registration date February 21, 2017 Last seen - Feb 21, 2017 at 08:06 AM
Hello, thank you for responding my question.
I am afraid not to good to explain it because not good enough in excel but mybe you can understand after see the table below:

Status / Percentage Total
Perfect : 100
Good : 99-80
Avergare : 79-60
Fair : 59-40
Poor : 39-20
Bad :19-0

Status will be in 2G column and below,
Percentage Total will be in 2F column and below.
This table will be update every 3 hours so the status should be change time to time.

I hope you understand what i mean.

Thank You.
Reply
Leave a comment
Helpful
+0
plus moins
-Hta-, Good evening.

The key to working with the VLOOKUP function is to organize the search data from your table in the first column (left) and the indexed data in the columns on the right.

VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

If you want to find the exact value in the table, the data to the left can be placed in any order.
The function will find the data and show the corresponding data in the column that you indicate.
To do this, just put the word FALSE in the last parameter of the function.

If you want to search for a data that is in ranges of values, then the data in the left column should be sorted in ascending order.
The function will look for the data as the value searched is smaller or equal to the nearest and will show the corresponding data in the column that you indicate.
To do this, just put the word TRUE in the last parameter of the function.
This is the exact case of your doubt.

The link below is the VLOOKUP Quick Reference Card.
It's really great.
http://download.microsoft.com/download/9/b/4/9b49c8c5-d7a9-45b1-b8b6-52067e9970a8/AF101984660_en-us_xl_qrc_vlookup%20refresher.pdf

This other link is also the explanation of the function by Microsoft support.
https://support.office.com/en-us/article/VLOOKUP-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1?ui=en-US&rs=en-US&ad=US

You can also find a good explanation within the help of Excel itself.

I hope I have really helped with these tips.
--
Belo Horizonte, Brasil.
Marcílio Lobão
Leave a comment
Helpful
+0
plus moins
-Hta-,

"...Hope you can teach me on 2nd issue :- ..."

About your new question:

Try to use:

G1 --> =IF(B1<=TODAY(), "Expired", IF(AND(B1-TODAY()>0, B1-TODAY()<=6), "Reminder", "Active"))

I created a new example for you.
Simulated a lot of dates.
https://www.sendspace.com/file/395fqz

Please, verify if this formula solve your doubt.
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
-Hta-- Mar 3, 2017 at 02:50 AM
Hi Mazzaropi..
Thank you for never tired of helping me. All the cases has been solved by your helps and now i am more interesting to learn deeply in Excel.
Hopely you can help me again another day in Excel and actually I am working on Message Box, I will try figure it out myself and if I get stuck, I hope you willing to help me again.
Thanks Again
Reply
Leave a comment
Helpful
+0
plus moins
-Hta-, Good morning.

Thanks for the feedback.
Glad to have helped you in your work.

Have a nice day!
--
Belo Horizonte, Brasil.
Marcílio Lobão
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!