Data auto change on multiple status in one column
Solved/Closed
-Hta-
Posts
5
Registration date
Thursday February 16, 2017
Status
Member
Last seen
February 21, 2017
-
Feb 21, 2017 at 01:40 AM
Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 - Mar 3, 2017 at 06:35 AM
Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 - Mar 3, 2017 at 06:35 AM
Related:
- Data auto change on multiple status in one column
- Viber online status - Guide
- Grand theft auto v free download no verification for pc - Download - Action and adventure
- Facebook auto refresh - Guide
- Transfer data from one excel worksheet to another automatically - Guide
- Viber seen status - Guide
6 responses
Mazzaropi
Posts
1985
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 24, 2023
147
Feb 22, 2017 at 10:07 AM
Feb 22, 2017 at 10:07 AM
-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
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
Mazzaropi
Posts
1985
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 24, 2023
147
Feb 28, 2017 at 09:28 AM
Feb 28, 2017 at 09:28 AM
-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
"...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
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","")
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","")
Mazzaropi
Posts
1985
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 24, 2023
147
Feb 21, 2017 at 05:20 AM
Feb 21, 2017 at 05:20 AM
-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
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-
Posts
5
Registration date
Thursday February 16, 2017
Status
Member
Last seen
February 21, 2017
Updated by -Hta- on 21/02/17 at 08:07 AM
Updated by -Hta- on 21/02/17 at 08:07 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.
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.
Mazzaropi
Posts
1985
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 24, 2023
147
Mar 2, 2017 at 10:05 PM
Mar 2, 2017 at 10:05 PM
-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.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1?ui=en-us&%3brs=en-us&%3bad=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
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.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1?ui=en-us&%3brs=en-us&%3bad=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
Didn't find the answer you are looking for?
Ask a question
Mazzaropi
Posts
1985
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 24, 2023
147
Mar 2, 2017 at 10:07 PM
Mar 2, 2017 at 10:07 PM
-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
"...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
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
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
Mazzaropi
Posts
1985
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 24, 2023
147
Mar 3, 2017 at 06:35 AM
Mar 3, 2017 at 06:35 AM
-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
Thanks for the feedback.
Glad to have helped you in your work.
Have a nice day!
--
Belo Horizonte, Brasil.
Marcílio Lobão
Updated by -Hta- on 28/02/17 at 01:58 AM
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