How to add highlighted values

[pardegt]

I need a spreadsheet that will have several ranges of numbers on it that correlate number of staff needed to build per a required quanitity (i.e, 1ea widgetA = 2 staff, 5ea widgetA = 8 staff, 4ea widgetB = 3 staff, etc.)

I will go through and highlight the correct number of staff required per the current demand for each product. There are a large variety of widgets, so I want a cell that will add all the highlighted cells to give the total staff count required. How would I go about programming a cell to do this?

Thanks.

[FDibbins]

Hi and welcome to the forum

There are no regulat formulas that will count colors. This could be done with VBA, or, if you have specific rules that determine what gets colored and when, then we may be able to use Conditional Formatting to automate the coloring foer you, and maybe use those same rules to do the count/sum for you as well.

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[playaller]

If you color the cells using conditional formatting then you can also use conditional formatting to count the colors.

If you color the cells manually then you will need VBA and a formula and will have to use 1 highlight color if summing them all.

1.Highlight a cell that will always remain highlighted to use as a reference.
2. Create a new Module in VBA and paste this code

Please Login or Register  to view this content.

3. Paste this formula in the cell that will display the sum

Please Login or Register  to view this content.

4. Adjust $J$1 to the cell that will always be highlighted with the color to look for.
5. Adjust $A$1:$F$14 to represent your range to sum.


If any additional cells highlighted after the formula is updated you will have to Press Ctrl+Alt+F9 (or press enter within the formula window) to update the total.

[pardegt]

Thank-you for your replies. I followed your instruction playaller and think I did what you asked but it is not quite working. When I go to save, I get a warning that I am saving to a macro free workbook. Do I need to create a macro? If so, can someone walk me thru it...is has been too long since I created one.

I attached a file representing one page of what I am trying to accomplish.

Thanks again.

[playaller]

Hello pardegt,

Simply save the workbook as .xlsm file or another macro enabled format and you're good to go.

Here is your file: Production Staffing Calc.xlsm

[pardegt]

Thank-you!

[pardegt]

I have another small favor to ask. Management likes where I am going with this and would like to add demand to the sheet. As you can see from the new attachment, I am now also highlighting current demand in green. My question is, since I want to identify demand based on number of units but pull the number of staff per that demand, I need to be able to tell it to pull the number that is below each green highlighted cell. I know I need to add another VBA module behind the green highlighted cell but am not sure what I need to change in the programing to tell it capture the value of the cell below the highlight. Hopefully, this makes sense.

I really appreciate your help.

[playaller]

Hello pardegt,

No worries. I have rewritten the code and have included a 2nd sub. Your updated workbook is attached. Remember Ctrl+Alt+F9 to update total if values are changed or if the values are changed manually while the code in place then the totals should update in real time.

Attached Update: Production Staffing Calc 2 (Updated).xlsm

[pardegt]

Wow, thanks! I had a thought but I am not sure how difficult it would be. I was thinking of coloring the cells with names (i.e. widget A, etc) by applying these rules (green: staff => demand ; yellow: staff -1 of demand ; red -2or> of demand)

This would allow a quick glimpse of the sheet to see how the overall area is performing and those that are below target.

[FDibbins]

You could do this with regular Conditional Formatting

[pardegt]

Ok, but I am not sure how to do this with the variation going on in the changing demand and avail staff. It would need to compare the number below the green highlighted cells and the corresponding yellow highlighted cells. These highlighted cells will frequently change.

[pardegt]

Just a bump hoping for some direction on the conditional coloring. THanks!

[playaller]

Are we talking about each widget counting the colors and cells under colors from within their own section to determine the demand?

[pardegt]

Yes, each widget would be evaluated based on its demand/required staff and the current staff. I was then thinking the cells that contain the names (widget A, etc) would each be colored based on their evaluation.

Sorry, I didn't make my request clear. Hopefully, this helps.

[playaller]

So lets create a scenerio.

Each Widget has a Green and Yellow Box to the right of it, Green on top of Yellow to align to the right end of the 2 Rows per Widget.

Color formatting for widget title area as follows:

Green: staff => demand
Yellow: staff -1 of demand
Red =>-2 of demand

Is that what you're needing?

[playaller]

Please See the attached.

Note the Yellow and Greens are different than the ones counted.
Finished: Production Staffing Calc 2 (Playaller_Updated).xlsm

Up can update the conditional formatting color in widget A and copy and paste the formatting only to all other widgets if needed.

[pardegt]

Thanks playballer...I didn't think of having the values of the cells from each row added seperately to the sheet and then apply the conditional formatting from them. I take it then that it is not possible to do it directly from the rows?

Thanks...I really appreciate your help.

[playaller]

It would require much more code within the conditional formatting which would make it a pain if any adjustments are needed. the 2 end cells keep the job easy for the conditional format calculating the results of two cells outcome versus the multiple criteria of the range. If you don't want to see those boxes I would suggest Moving everything over a few rows and hiding the rows they are in. This will allow you to keep the look you want while the end cells work hidden in the background.