Looking for Help. Summing Cells in Different Locations Based on Multiple Variables. Bonus Easy Question, Too!

clindstrom

New Member
Joined
Sep 25, 2014
Messages
2
Hello, all! I've got two problems, one easy and the other not so much.

For starters, I just need two columns to show white if there is nothing in both cells on the row, yellow if there is a 1 in the left only, blue if there is a 1 in the right only and red if there is a number in both cells on the same row.
Example:
ABCDE<--D Yellow, E Blue
1<--Yellow pair
1<--Blue pair
<--White pair
11<--Red pair
^white^ ^white^ ^white^

<tbody>
</tbody>

Next, the harder one. This is what prompts me to post tonight:
I work with two sheets on a regular basis to track stuff that happens through the month. The first sheet is a record of events that happened including what date, a short description chosen from a dropdown list and the number of times that specific event happened. The second sheet tracks how many of each type of event happened based on the same short description and the date, totaled based on the quantity while ignoring the rest of the row. I want to automate this process and I know it is possible but it really does elude me after a few shifts of trying to crack it :(
To ensure consistency, these columns are all Data Validated with a warning about accuracy if there isn't a match.
Here is a fun stipulation: This report is shared among each shift over a database so all of the automation has to be part of the file itself.

Example of what I would like to see:

WorksheetA
DATEVALUEOTHERQUANTITY
1-SepCatasdoiu1
1-SepDogagdj1
2-SepDogasd2
2-SepFishjfsdfh1
2-SepCatasrafhb1
2-SepCatzxcva3
2-SepOtherrfyhyfd14
2-SepDogzxcv1
3-SepFishtreat1
4-SepOtherzzzdf1
4-SepOthergraez2
4-SepBirdzfgzry1

<tbody>
</tbody>
WorksheetB
DATECatDogFishBirdOther
1-Sep11000
2-Sep431014
3-Sep00100
4-Sep00013
5-Sep00000
6-Sep00000

<tbody>
</tbody>

So in short, I need the number of times each animal occurred each day from sheet A tallied onto the Grid on sheet B


Thanks for your tame!
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

azumi

Well-known Member
Joined
Jun 4, 2013
Messages
555
Maybe, put in F2 and copied down

=IF(AND(D3<>"",E3<>""),"White",IF(OR(D3<>"",E3=""),"Yellow",IF(OR(D3="",E3<>""),"Red","")))
 

clindstrom

New Member
Joined
Sep 25, 2014
Messages
2
Maybe, put in F2 and copied down

=IF(AND(D3<>"",E3<>""),"White",IF(OR(D3<>"",E3=""),"Yellow",IF(OR(D3="",E3<>""),"Red","")))

I seem to have been misunderstood. My apologizes! I want the cell color to change based on the position of the 1

I hope that makes more sense
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,249
Office Version
  1. 365
Platform
  1. Windows
I seem to have been misunderstood. My apologizes! I want the cell color to change based on the position of the 1

I hope that makes more sense
Welcome to the MrExcel board!

Try this. Select from D2:Exx and apply the Conditional Formatting formulas as shown.

Excel Workbook
DE
1
21
31
4
511
6
CF Pairs
#VALUE!
 

Watch MrExcel Video

Forum statistics

Threads
1,109,041
Messages
5,526,417
Members
409,701
Latest member
nitmani

This Week's Hot Topics

Top