Copy data from conditional formatted cells to new tab based on the conditional formatting color in Excel
up vote
0
down vote
favorite
Okay so I'm new to all this- please help me if there's a better way to do this... currently trying to make changes to an existing spreadsheet and I can't figure out how to accomplish what I want...
Tab 1 accounts for different numbers and their assignments, Tab 2 rearranges that data for a printable page, and Tab 3 has a list of all the numbers we have to account for, and currently uses conditional formatting to do so. (Green if the number is accounted for on Tab 1, Red if not)
What I am trying to accomplish is create a list of all the numbers NOT accounted for (Red conditionally formatted cells on tab 3) to show up in any form on Tab 2. I started messing around with the =mycolor and IF functions, but I found that only works with explicit formatting of the cell, not the conditional formatting I am using to account for the numbers.
Do I need to change how my spreadsheet is accounting for the numbers from Tab 1 instead of conditional formatting? If so what would be the best way to go about this? If there's a way to do this with conditional formatting, how would I go about copying data based on the conditional formatting of certain cells?
Also, ideally I want this to be done without having to play a macro. I'm trying to make this as stupid simple for the end user as possible... but if there's no way to do it without recording a macro then that's what I'll end up doing.
Thanks a bunch in advance...
excel vba conditional-formatting
asked Nov 4 at 9:29
David Weibel
1
New contributor
David Weibel is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
up vote
0
down vote
favorite
Okay so I'm new to all this- please help me if there's a better way to do this... currently trying to make changes to an existing spreadsheet and I can't figure out how to accomplish what I want...
Tab 1 accounts for different numbers and their assignments, Tab 2 rearranges that data for a printable page, and Tab 3 has a list of all the numbers we have to account for, and currently uses conditional formatting to do so. (Green if the number is accounted for on Tab 1, Red if not)
What I am trying to accomplish is create a list of all the numbers NOT accounted for (Red conditionally formatted cells on tab 3) to show up in any form on Tab 2. I started messing around with the =mycolor and IF functions, but I found that only works with explicit formatting of the cell, not the conditional formatting I am using to account for the numbers.
Do I need to change how my spreadsheet is accounting for the numbers from Tab 1 instead of conditional formatting? If so what would be the best way to go about this? If there's a way to do this with conditional formatting, how would I go about copying data based on the conditional formatting of certain cells?
Also, ideally I want this to be done without having to play a macro. I'm trying to make this as stupid simple for the end user as possible... but if there's no way to do it without recording a macro then that's what I'll end up doing.
Thanks a bunch in advance...
excel vba conditional-formatting
asked Nov 4 at 9:29
David Weibel
1
New contributor
David Weibel is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
Okay so I'm new to all this- please help me if there's a better way to do this... currently trying to make changes to an existing spreadsheet and I can't figure out how to accomplish what I want...
Tab 1 accounts for different numbers and their assignments, Tab 2 rearranges that data for a printable page, and Tab 3 has a list of all the numbers we have to account for, and currently uses conditional formatting to do so. (Green if the number is accounted for on Tab 1, Red if not)
What I am trying to accomplish is create a list of all the numbers NOT accounted for (Red conditionally formatted cells on tab 3) to show up in any form on Tab 2. I started messing around with the =mycolor and IF functions, but I found that only works with explicit formatting of the cell, not the conditional formatting I am using to account for the numbers.
Do I need to change how my spreadsheet is accounting for the numbers from Tab 1 instead of conditional formatting? If so what would be the best way to go about this? If there's a way to do this with conditional formatting, how would I go about copying data based on the conditional formatting of certain cells?
Also, ideally I want this to be done without having to play a macro. I'm trying to make this as stupid simple for the end user as possible... but if there's no way to do it without recording a macro then that's what I'll end up doing.
Thanks a bunch in advance...
excel vba conditional-formatting
asked Nov 4 at 9:29
David Weibel
1
New contributor
David Weibel is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Okay so I'm new to all this- please help me if there's a better way to do this... currently trying to make changes to an existing spreadsheet and I can't figure out how to accomplish what I want...
Tab 1 accounts for different numbers and their assignments, Tab 2 rearranges that data for a printable page, and Tab 3 has a list of all the numbers we have to account for, and currently uses conditional formatting to do so. (Green if the number is accounted for on Tab 1, Red if not)
What I am trying to accomplish is create a list of all the numbers NOT accounted for (Red conditionally formatted cells on tab 3) to show up in any form on Tab 2. I started messing around with the =mycolor and IF functions, but I found that only works with explicit formatting of the cell, not the conditional formatting I am using to account for the numbers.
Do I need to change how my spreadsheet is accounting for the numbers from Tab 1 instead of conditional formatting? If so what would be the best way to go about this? If there's a way to do this with conditional formatting, how would I go about copying data based on the conditional formatting of certain cells?
Also, ideally I want this to be done without having to play a macro. I'm trying to make this as stupid simple for the end user as possible... but if there's no way to do it without recording a macro then that's what I'll end up doing.
Thanks a bunch in advance...
excel vba conditional-formatting
excel vba conditional-formatting
asked Nov 4 at 9:29
David Weibel
1
New contributor
David Weibel is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked Nov 4 at 9:29
David Weibel
1
New contributor
David Weibel is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked Nov 4 at 9:29
David Weibel
1
New contributor
David Weibel is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked Nov 4 at 9:29
David Weibel
1
asked Nov 4 at 9:29
David Weibel
1
1
New contributor
David Weibel is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
David Weibel is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
David Weibel is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
What I would do is add a column to tab three that is a missing/not missing column (=column C in my mock up below). Missing = 1 accounted for =0. (That can either be achieved by the same formula as used to give the red/green OR a vlookup() function). Therefore for each red row there will be a 1 alongside.
Extending that, as per my comment... index the missing values (col A=):
=IF(C3>0,SUM(C$1:C3),"")
Now use a new pair of columns to grab the missing values 
And use a lookup function to line up the missing values (col G)... I only went up to 5 (col f)
=IFERROR(VLOOKUP(F3,A$3:B$6,2,FALSE),"")
answered Nov 4 at 9:55
RichardBJ
563
I like the idea of using a vlookup function but I haven't been able to make it work for this in particular- I want to avoid the end user having to sort missing/accounted for numbers at the end, which is actually what they're using now (sort by color) Thanks for the input
– David Weibel
Nov 4 at 10:29
OK so the other thing I’ve done is to make sure all the hits are indexed, by counting all the hit above? This “index” then goes in the first column (insert a column). Now the red values are indexed. Then you set up another sheet which has col a as incrementing numbrs from 1 to x. Then you use vlookup again to look up that index in your previous sheet and retuns the associated red number. It requires you know the max possible number of red numbers. If the index is not found it will return an error, so you can embed the lookup in an iF error function?
– RichardBJ
Nov 4 at 11:12
Hi again David, I just amended my previous answer to show that. It is difficult to convey this in Excel compared to a scripting language! Regards, R.
– RichardBJ
Nov 4 at 11:53
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
What I would do is add a column to tab three that is a missing/not missing column (=column C in my mock up below). Missing = 1 accounted for =0. (That can either be achieved by the same formula as used to give the red/green OR a vlookup() function). Therefore for each red row there will be a 1 alongside.
Extending that, as per my comment... index the missing values (col A=):
=IF(C3>0,SUM(C$1:C3),"")
Now use a new pair of columns to grab the missing values
And use a lookup function to line up the missing values (col G)... I only went up to 5 (col f)
=IFERROR(VLOOKUP(F3,A$3:B$6,2,FALSE),"")
answered Nov 4 at 9:55
RichardBJ
563
I like the idea of using a vlookup function but I haven't been able to make it work for this in particular- I want to avoid the end user having to sort missing/accounted for numbers at the end, which is actually what they're using now (sort by color) Thanks for the input
– David Weibel
Nov 4 at 10:29
OK so the other thing I’ve done is to make sure all the hits are indexed, by counting all the hit above? This “index” then goes in the first column (insert a column). Now the red values are indexed. Then you set up another sheet which has col a as incrementing numbrs from 1 to x. Then you use vlookup again to look up that index in your previous sheet and retuns the associated red number. It requires you know the max possible number of red numbers. If the index is not found it will return an error, so you can embed the lookup in an iF error function?
– RichardBJ
Nov 4 at 11:12
Hi again David, I just amended my previous answer to show that. It is difficult to convey this in Excel compared to a scripting language! Regards, R.
– RichardBJ
Nov 4 at 11:53
add a comment |
up vote
0
down vote
What I would do is add a column to tab three that is a missing/not missing column (=column C in my mock up below). Missing = 1 accounted for =0. (That can either be achieved by the same formula as used to give the red/green OR a vlookup() function). Therefore for each red row there will be a 1 alongside.
Extending that, as per my comment... index the missing values (col A=):
=IF(C3>0,SUM(C$1:C3),"")
Now use a new pair of columns to grab the missing values
And use a lookup function to line up the missing values (col G)... I only went up to 5 (col f)
=IFERROR(VLOOKUP(F3,A$3:B$6,2,FALSE),"")
answered Nov 4 at 9:55
RichardBJ
563
I like the idea of using a vlookup function but I haven't been able to make it work for this in particular- I want to avoid the end user having to sort missing/accounted for numbers at the end, which is actually what they're using now (sort by color) Thanks for the input
– David Weibel
Nov 4 at 10:29
OK so the other thing I’ve done is to make sure all the hits are indexed, by counting all the hit above? This “index” then goes in the first column (insert a column). Now the red values are indexed. Then you set up another sheet which has col a as incrementing numbrs from 1 to x. Then you use vlookup again to look up that index in your previous sheet and retuns the associated red number. It requires you know the max possible number of red numbers. If the index is not found it will return an error, so you can embed the lookup in an iF error function?
– RichardBJ
Nov 4 at 11:12
Hi again David, I just amended my previous answer to show that. It is difficult to convey this in Excel compared to a scripting language! Regards, R.
– RichardBJ
Nov 4 at 11:53
add a comment |
up vote
0
down vote
up vote
0
down vote
What I would do is add a column to tab three that is a missing/not missing column (=column C in my mock up below). Missing = 1 accounted for =0. (That can either be achieved by the same formula as used to give the red/green OR a vlookup() function). Therefore for each red row there will be a 1 alongside.
Extending that, as per my comment... index the missing values (col A=):
=IF(C3>0,SUM(C$1:C3),"")
Now use a new pair of columns to grab the missing values
And use a lookup function to line up the missing values (col G)... I only went up to 5 (col f)
=IFERROR(VLOOKUP(F3,A$3:B$6,2,FALSE),"")
answered Nov 4 at 9:55
RichardBJ
563
What I would do is add a column to tab three that is a missing/not missing column (=column C in my mock up below). Missing = 1 accounted for =0. (That can either be achieved by the same formula as used to give the red/green OR a vlookup() function). Therefore for each red row there will be a 1 alongside.
Extending that, as per my comment... index the missing values (col A=):
=IF(C3>0,SUM(C$1:C3),"")
Now use a new pair of columns to grab the missing values
And use a lookup function to line up the missing values (col G)... I only went up to 5 (col f)
=IFERROR(VLOOKUP(F3,A$3:B$6,2,FALSE),"")
answered Nov 4 at 9:55
RichardBJ
563
edited Nov 4 at 12:03
answered Nov 4 at 9:55
RichardBJ
563
answered Nov 4 at 9:55
RichardBJ
563
answered Nov 4 at 9:55
RichardBJ
563
563
I like the idea of using a vlookup function but I haven't been able to make it work for this in particular- I want to avoid the end user having to sort missing/accounted for numbers at the end, which is actually what they're using now (sort by color) Thanks for the input
– David Weibel
Nov 4 at 10:29
OK so the other thing I’ve done is to make sure all the hits are indexed, by counting all the hit above? This “index” then goes in the first column (insert a column). Now the red values are indexed. Then you set up another sheet which has col a as incrementing numbrs from 1 to x. Then you use vlookup again to look up that index in your previous sheet and retuns the associated red number. It requires you know the max possible number of red numbers. If the index is not found it will return an error, so you can embed the lookup in an iF error function?
– RichardBJ
Nov 4 at 11:12
Hi again David, I just amended my previous answer to show that. It is difficult to convey this in Excel compared to a scripting language! Regards, R.
– RichardBJ
Nov 4 at 11:53
add a comment |
I like the idea of using a vlookup function but I haven't been able to make it work for this in particular- I want to avoid the end user having to sort missing/accounted for numbers at the end, which is actually what they're using now (sort by color) Thanks for the input
– David Weibel
Nov 4 at 10:29
OK so the other thing I’ve done is to make sure all the hits are indexed, by counting all the hit above? This “index” then goes in the first column (insert a column). Now the red values are indexed. Then you set up another sheet which has col a as incrementing numbrs from 1 to x. Then you use vlookup again to look up that index in your previous sheet and retuns the associated red number. It requires you know the max possible number of red numbers. If the index is not found it will return an error, so you can embed the lookup in an iF error function?
– RichardBJ
Nov 4 at 11:12
Hi again David, I just amended my previous answer to show that. It is difficult to convey this in Excel compared to a scripting language! Regards, R.
– RichardBJ
Nov 4 at 11:53
I like the idea of using a vlookup function but I haven't been able to make it work for this in particular- I want to avoid the end user having to sort missing/accounted for numbers at the end, which is actually what they're using now (sort by color) Thanks for the input
– David Weibel
Nov 4 at 10:29
I like the idea of using a vlookup function but I haven't been able to make it work for this in particular- I want to avoid the end user having to sort missing/accounted for numbers at the end, which is actually what they're using now (sort by color) Thanks for the input
– David Weibel
Nov 4 at 10:29
OK so the other thing I’ve done is to make sure all the hits are indexed, by counting all the hit above? This “index” then goes in the first column (insert a column). Now the red values are indexed. Then you set up another sheet which has col a as incrementing numbrs from 1 to x. Then you use vlookup again to look up that index in your previous sheet and retuns the associated red number. It requires you know the max possible number of red numbers. If the index is not found it will return an error, so you can embed the lookup in an iF error function?
– RichardBJ
Nov 4 at 11:12
OK so the other thing I’ve done is to make sure all the hits are indexed, by counting all the hit above? This “index” then goes in the first column (insert a column). Now the red values are indexed. Then you set up another sheet which has col a as incrementing numbrs from 1 to x. Then you use vlookup again to look up that index in your previous sheet and retuns the associated red number. It requires you know the max possible number of red numbers. If the index is not found it will return an error, so you can embed the lookup in an iF error function?
– RichardBJ
Nov 4 at 11:12
Hi again David, I just amended my previous answer to show that. It is difficult to convey this in Excel compared to a scripting language! Regards, R.
– RichardBJ
Nov 4 at 11:53
Hi again David, I just amended my previous answer to show that. It is difficult to convey this in Excel compared to a scripting language! Regards, R.
– RichardBJ
Nov 4 at 11:53
add a comment |
David Weibel is a new contributor. Be nice, and check out our Code of Conduct.
David Weibel is a new contributor. Be nice, and check out our Code of Conduct.
David Weibel is a new contributor. Be nice, and check out our Code of Conduct.
David Weibel is a new contributor. Be nice, and check out our Code of Conduct.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53139370%2fcopy-data-from-conditional-formatted-cells-to-new-tab-based-on-the-conditional-f%23new-answer', 'question_page');
}
);
Post as a guest
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
