IF/Then statement for worksheet.

[spaces999]

Hoping someone can help me with this problem I have. I need to have an excel sheet search for a set of numbers in a specific tab. Then if it finds that number, then place the word used or highlight it in some way. So there are tag numbers that are put in a sheet. There are 12 sheets named from January to December. The tags are put in these sheets but only specific ones need to be monitored. I would like to either highlight them in some way or, even better, next to the tag numbers that are being monitored, put what sheet they are on. Cell information would be helpful as well but I am trying to keep this simple. I don’t think VBA works here so I have been trying to figure out a formula to get this to work. I can't post the excel sheet to show but here is what the tables look like:


[TABLE="width: 506"]
<tbody>[TR]
[TD]Used?[/TD]
[TD][/TD]
[TD]Tech Assets[/TD]
[TD]Location[/TD]
[TD]Cell[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]123455[/TD]
[TD]#NAME?[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]12222[/TD]
[TD]12222[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]12333[/TD]
[TD]12333[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Yes[/TD]
[TD]33333[/TD]
[TD]33333[/TD]
[TD]APRIL - S0 & Asset[/TD]
[TD]D3[/TD]
[/TR]
[TR]
[TD]Yes[/TD]
[TD]23456[/TD]
[TD]23456[/TD]
[TD]JANUARY - S0 & Asset[/TD]
[TD]D4[/TD]
[/TR]
</tbody>[/TABLE]

Sheet Name: TECH ASSET TAGS

All other sheets are named: JANUARY - S0 & Asset all the way through DECEMBER

Each sheet looks like this:

[TABLE="width: 1402"]
<tbody>[TR]
[TD]Total[/TD]
[TD]Assets:[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S0[/TD]
[TD]Number[/TD]
[TD]Asset Tag[/TD]
[TD]Date[/TD]
[TD]Description[/TD]
[TD]Location[/TD]
[TD]Serial[/TD]
[TD]Tracking Number[/TD]
[/TR]
[TR]
[TD]S0[/TD]
[TD]3423423[/TD]
[TD]12558[/TD]
[TD]1/5/2017[/TD]
[TD]OPTIPLEX SMALL 7040[/TD]
[TD]building 7[/TD]
[TD]4jkl4kj4kj4kj[/TD]
[TD]z123456789[/TD]
[/TR]
[TR]
[TD]S0[/TD]
[TD]3423423[/TD]
[TD]23456[/TD]
[TD]1/5/2017[/TD]
[TD]OPTIPLEX SMALL 7040[/TD]
[TD]building 2[/TD]
[TD]kjlwekjndfsnk[/TD]
[TD]z123456789[/TD]
[/TR]
</tbody>[/TABLE]

 

[SQUIDD]

This may not be exactly what you are after but I would think conditional formatting would be handy for you to look up.

You can do things like make the cell red if the number 200 is its value for example.

Also I'm sure vba would easily handle doing what you want.

Dave

 

[spaces999]

Yeah I am sure VBA will be the only way to go but not sure how that works. This company seems to block a lot of functions and I have never gotten a vba to actually work for some reason. Thanks.

 

[myemail1248]

Why not record a macro using the "search" function and then edit it to your specific requirements?

 

[SQUIDD]

so are you saying code is not an option then?

I still think conditional formatting will fix most of your issues

dave

 

[myemail1248]

Am I saying code is not an option? No, not at all. What I am saying is if he does not understand VBA code, recording a macro will write most of the code for him. It is the easiest way to dig in and learn about code.

 

[spaces999]

SQUIDD: I have only had luck with formulas. I know how to do search things on simple tasks like finding a particular word but in this case there are several numbers. So I need it to search several pages and go through all the cells to find specific numbers from a list. So there could be say 100 numbers it has to compare to. I am not sure how to formulate that.

myemail12248: I am not sure how to record macros. I am trying to watch youtube videos on these things but it is taking some time to get through them.

Best I have come up with is =IF('SEPTEMBER:[AUGUST - S0 & Asset:AUGUST - S0 & Asset'!D3:D1004=B2:B1000,"") but it will not list the number yet so I am going to try and keep adding onto it. I am thinking if I can get it to at least search and compare the numbers, I can maybe put other formulas in place to make them show up elsewhere. If that makes any sense at all.

 

[spaces999]

Oops. The formula changed on me. =IF('SEPTEMBER:AUGUST - S0 & Asset'!D3:D1004=B2:B1000,"")

 

[SQUIDD]

Am I saying code is not an option? No, not at all. What I am saying is if he does not understand VBA code, recording a macro will write most of the code for him. It is the easiest way to dig in and learn about code.

myemail1248, i was replying to spaces999 about code not being an option as he said his compmany blocks them.
sorry for the confusion.

dave

 

[SQUIDD]

SQUIDD: I have only had luck with formulas. I know how to do search things on simple tasks like finding a particular word but in this case there are several numbers. So I need it to search several pages and go through all the cells to find specific numbers from a list. So there could be say 100 numbers it has to compare to. I am not sure how to formulate that.

myemail12248: I am not sure how to record macros. I am trying to watch youtube videos on these things but it is taking some time to get through them.

Best I have come up with is =IF('SEPTEMBER:[AUGUST - S0 & Asset:AUGUST - S0 & Asset'!D3:D1004=B2:B1000,"") but it will not list the number yet so I am going to try and keep adding onto it. I am thinking if I can get it to at least search and compare the numbers, I can maybe put other formulas in place to make them show up elsewhere. If that makes any sense at all.

i think code is the way forward if you can.

can you tell us exacly what you would like the code to do, what column to put the data.

it looks like you want to write a list in sheet 1, have the code search for them and tell you all cell adresses they were found on including sheet name(month)

can you give an example of how sheet 1 would need to look.

see what can be done for you.

dave