Conditional formating a report

[justcallmekyle]

I am working on making our lives a little easier around my work. They currently use a different excel sheet for each months report and each crew. I am trying to make them all one for every month and have the report auto fill. I need help with that Auto fill portion. The link provided is to a drop box that has the excel in it i am currently working on. Please help me get each cell of the report to auto fill from the different crews. Thank you

https://www.dropbox.com/s/kfmkzu5skpdnj3n/crew log.xlsx?dl=0

 

[justcallmekyle]

no luck so far! Anyone have any ideas that would help me with this?

 

[FDibbins]

I am not permitted to access external links

I agree with you that is it almost always better to put all data in 1 sheet, then reference that for repots etc. It sounds to me though that you have a few options for this

1. use a helper column that will add a unique ID to each row of data pertaining to the month you want, then base the extract on INDEX/MATCH based on that ID
something like =countif($A$2:A2,$A$1)
assuming your dates are in column A, starting in row 2, and you enter the criteria date in A1

2. Use an ARRAY formula...

	A​	B​	C​	D​	E​	F​	G​
1​	Date	Blah1	Blah2		1/8/2017​	Blah1	Blah2
2​	1/1/2017​	10​	100​	427361		50​	500​
3​	1/1/2017​	20​	200​	427362		60​	600​
4​	1/1/2017​	30​	300​	427363		70​	700​
5​	1/1/2017​	40​	400​	427434		80​	800​
6​	1/8/2017​	50​	500​	427431
7​	1/8/2017​	60​	600​	427432
8​	1/8/2017​	70​	700​	427433
9​	1/8/2017​	80​	800​	427504

F2=IFERROR(INDEX(B:B,SMALL(IF($A$2:$A$15=$E$1,ROW($A$2:$A$15)),ROWS($A$1:A1))),"")
entered using CTRL SHIFT ENTER, not just ENTER
copy across as needed, then copy down

 

[justcallmekyle]

try this. https://www.dropbox.com/s/kfmkzu5skpdnj3n/crew log.xlsx?dl=0

the report is jan report, and the conditions for the manhole part would be the ward number 0-5 and if it is a disconnect, repair, or tap. so if the ward is zero then it has blank number of taps, etc.

 

[FDibbins]

try this. https://www.dropbox.com/s/kfmkzu5skpdnj3n/crew log.xlsx?dl=0

the report is jan report, and the conditions for the manhole part would be the ward number 0-5 and if it is a disconnect, repair, or tap. so if the ward is zero then it has blank number of taps, etc.

Im pretty sure I just told you I am not permitted to access external links?

 

[justcallmekyle]

Im pretty sure I just told you I am not permitted to access external links?

So are you saying it is the link not working or your permissions on the system you are on? If it is the system you are on then let me know how you would like me to get it to you to view. Clearly, there must have been a misunderstanding.

 

[Joe4]

Ford is telling you he is unable to view those links and download those files. He is not the only one. Many people are unable or unwilling to download files off of the internet for security reasons (my workplace actually forbids it). So uploading files and providing links to them will decrease your pool of potential helpers.

There are other alternatives. While you cannot upload files to this site. But there are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html. Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

 

[justcallmekyle]

Thank you for helping to clear that up. I will get some screen shots together.

 

[FDibbins]

Joe, thanks for the assist - yes, my workplace severely restricts access to certain web site types