11-23-2016, 05:22 PM
|
#21
|
Franchise Player
Join Date: Feb 2007
Location: A small painted room
|
Quote:
Originally Posted by RW99
So I'm back with another question for the CP braintrust, any help would be appreciated. I have this spreadsheet:
The green section is fairly straight forward, I have to distribute files every day to a group of six staff. Today I am distributing 1000 files (D2). Of these six staff, Shannon and Greg are slackers, so they only get 10.6% of the files, everyone else is even at 19.7% (A5:A10).
The yellow section is where it gets tricky. Some staff are here a full day and some are half days and some call in sick to play Battlefield 1 (come on Terry...). So the question is what would my formula look like in the red section (H5:H10) so that regardless of who shows up to work all 1000 files get assigned?
I think I'm asking for a lot here, I'm assuming the formula might be fairly long
|
Since Terri is off your percentage dist has to be refactored , and compared against the sum of available workload days? On phone so didn't check your file, but 10%x0 work days equals 0
|
|
|
The Following User Says Thank You to calumniate For This Useful Post:
|
|
11-23-2016, 05:28 PM
|
#22
|
Franchise Player
|
I've got it figured for you, but it's dinner time. Will post solution soonish.
|
|
|
The Following User Says Thank You to Fuzz For This Useful Post:
|
|
11-23-2016, 06:11 PM
|
#23
|
Franchise Player
|
I'm not sure you can do it all in one cell, as you need to sum a new column first.
So make H=B5*G5 and fill down
Then new column I
I=H5/SUM(H$5:H$10)*D$2
If you have more rows, just change the H$10 in this formula to whatever the last row is.
|
|
|
The Following 2 Users Say Thank You to Fuzz For This Useful Post:
|
|
11-23-2016, 07:09 PM
|
#24
|
First Line Centre
Join Date: Oct 2005
Location: 103 104END 106 109 111 117 122 202 203 207 208 216 217 219 221 222 224 225 313 317 HC G
|
Thanks a bunch, I was thinking of a much more complicated formula. Math ftw.
|
|
|
05-14-2017, 09:37 PM
|
#25
|
Franchise Player
Join Date: Jul 2002
Location: Chicago
|
Wow I am no good at excel. I want to do something pretty simple i think, but maybe not and regardless, I have no idea how to do it.
I have several tasks that will regularly take place: task1, task2, task3,...
These tasks will alternate in the same 'activity' column (column B)
Each of these tasks will take a varying amount of 'time', and that be recorded next to the activity in column C.
Can I have excel automatically calculate the total hours spent on each specific activity?
thanks
|
|
|
05-14-2017, 11:40 PM
|
#26
|
Some kinda newsbreaker!
Join Date: May 2004
Location: Learning Phaneufs skating style
|
Quote:
Originally Posted by EldrickOnIce
Wow I am no good at excel. I want to do something pretty simple i think, but maybe not and regardless, I have no idea how to do it.
I have several tasks that will regularly take place: task1, task2, task3,...
These tasks will alternate in the same 'activity' column (column B)
Each of these tasks will take a varying amount of 'time', and that be recorded next to the activity in column C.
Can I have excel automatically calculate the total hours spent on each specific activity?
thanks
|
If I understand your problem correctly, sounds like the SUMIFS function.
https://support.office.com/en-us/art...6-611cebce642b
=SUMIFS(C1:C100, B1:B100, "=task1")
for example would add up all the values in column C where column B is "task1"
You can also alternatively use pivot tables as a quick solution too.
Last edited by sureLoss; 05-14-2017 at 11:42 PM.
|
|
|
The Following User Says Thank You to sureLoss For This Useful Post:
|
|
05-15-2017, 07:41 PM
|
#27
|
Franchise Player
Join Date: Jul 2002
Location: Chicago
|
^ Thanks. Both worked perfectly
|
|
|
06-25-2017, 01:51 PM
|
#28
|
Franchise Player
Join Date: Aug 2008
Location: Calgary, AB
|
I'm having a heck of a time trying to figure this one out so thought maybe someone could help me.
Basically I'm trying to have a Pivot Table Report filter update based off a user selection in a drop-down data validation cell (vs. having to update it in the pivot table menu itself).
I've tried various VBA codes and none have come even close to working, there has got to be a really simple way to do this, any suggestion?
Here's the skinny:
My Sheet4 Cell B2 has the drop-down data validation list of entries.
The Pivot Table is called PivotTable1
The Pivot Table field name for the filter I'm trying to change is called HFM Management Reporting Entity
Literally nothing happens when I make a change to the drop-down in cell B2, please advise.
The code I've tried using is:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As String
If Intersect(Target, Range("B2")) Is Nothing Then Exit Sub
Set pt = Worksheets("Sheet4").PivotTables("PivotTable1")
Set Field = pt.PivotFields("HFM Management Reporting Entity")
NewCat = Worksheets("Sheet4").Range("B2").Value
With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable
End With
End Sub
|
|
|
06-25-2017, 08:46 PM
|
#29
|
Franchise Player
|
Does this help?
Quote:
Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets(“SheetName”).PivotTables(“PivotTableNam e”).PivotCache.Refresh
End Sub
|
http://www.eonesolutions.com/blog-po...hable-reports/ You could put it in your vba.
|
|
|
06-26-2017, 08:44 PM
|
#30
|
Franchise Player
Join Date: Aug 2008
Location: Calgary, AB
|
That didn't work, however, when I when back and updated the "Sheet4" to the actual SheetName I am getting it to run (albeit unsuccessfully).
|
|
|
06-26-2017, 08:54 PM
|
#31
|
Franchise Player
Join Date: Feb 2007
Location: A small painted room
|
Is your data safe enough to just give a power pivot workbook with slicers?
|
|
|
06-27-2017, 09:04 AM
|
#32
|
Franchise Player
Join Date: Aug 2008
Location: Calgary, AB
|
Quote:
Originally Posted by calumniate
Is your data safe enough to just give a power pivot workbook with slicers?
|
This is the approach I was going down originally, however, the data-set itself is supposed to change based off of the user selection from the drop-down.
So basically, there is a vlookup that is occuring, that based off of the drop-down menu selection a vlookup formula changes the dataset range that the pivot table reads.
I think a data slicer would only work for a static data range, so I think that's complicating things (including the original attempts at this macro).
|
|
|
08-03-2017, 12:18 PM
|
#33
|
Franchise Player
|
I have a question:
I have a list of items that I need to assign a value to. They are labeled 1-6 with each number having a different value.
Is there a formula to use some sort of "If equal to 1 (or 2 or 3 etc) than show the corresponding value for 1 or 2 or 3 etc"? I can't seem to figure it out.
|
|
|
08-03-2017, 12:26 PM
|
#34
|
Scoring Winger
|
Quote:
Originally Posted by Weitz
I have a question:
I have a list of items that I need to assign a value to. They are labeled 1-6 with each number having a different value.
Is there a formula to use some sort of "If equal to 1 (or 2 or 3 etc) than show the corresponding value for 1 or 2 or 3 etc"? I can't seem to figure it out.
|
Make another list somewhere. Use VLOOKUP
Example, I have A1:B6
1 AAA
2 BBB
3 CCC
4 DDD
5 EEE
6 FFF
My data is from column D onwards, where D is 1-6 in random assortments.
So if I want the corresponding values to show up, I have in box C1:
=VLOOKUP(D1,$A$1:$B$6,2,FALSE)
What it's doing: Take the value in D1, find it in column 1 of A1:B6, return the value in column 2 of that array (column B). False means exact match only. The $ allows you to drag it down all of column C
https://support.office.com/en-us/art...8-93a18ad188a1
|
|
|
08-03-2017, 12:29 PM
|
#35
|
Powerplay Quarterback
|
Quote:
Originally Posted by Weitz
I have a question:
I have a list of items that I need to assign a value to. They are labeled 1-6 with each number having a different value.
Is there a formula to use some sort of "If equal to 1 (or 2 or 3 etc) than show the corresponding value for 1 or 2 or 3 etc"? I can't seem to figure it out.
|
You could use either a look-up table or a nested boolean argument. Six items is a little high for nested if statements, but it will work. i.e if(cell=1,1 value, if(cell =2,2 value,if(cell=3,3 value,etc.
|
|
|
08-03-2017, 01:13 PM
|
#36
|
Franchise Player
|
Thanks for the help guys!
I am trying the nested way there, and I can't seem to get it to work.
this is my formula:
=IF(1,$O$117,IF(2,$O$118,IF(3,$O$119,IF(4,$O$120,I F(5,$O$121,IF(6,$O$122,0))))))
All it returns is the value for 1.
Sorry I am awful with excel.
Wait I just realized it doesn't make sense ha.
Cool Got it to work with this:
=IF(N4=1,$O$117,IF(N4=2,$O$118,IF(N4=3,$O$119,IF(N 4=4,$O$120,IF(N4=5,$O$121,IF(N4=6,$O$122,0))))))
Last edited by Weitz; 08-03-2017 at 01:19 PM.
|
|
|
08-04-2017, 09:20 AM
|
#37
|
Powerplay Quarterback
Join Date: Aug 2002
Location: Mayor of McKenzie Towne
|
Quote:
Originally Posted by Weitz
Thanks for the help guys!
I am trying the nested way there, and I can't seem to get it to work.
this is my formula:
=IF(1,$O$117,IF(2,$O$118,IF(3,$O$119,IF(4,$O$120,I F(5,$O$121,IF(6,$O$122,0))))))
All it returns is the value for 1.
Sorry I am awful with excel.
Wait I just realized it doesn't make sense ha.
Cool Got it to work with this:
=IF(N4=1,$O$117,IF(N4=2,$O$118,IF(N4=3,$O$119,IF(N 4=4,$O$120,IF(N4=5,$O$121,IF(N4=6,$O$122,0))))))
|
You'll kick yourself when you realize how simple vlookups are.
http://excelcentral.com/excel2013/ex...-in-excel.html
__________________
"Teach a man to reason, and he'll think for a lifetime"
~P^2
|
|
|
The Following User Says Thank You to firebug For This Useful Post:
|
|
08-05-2017, 11:29 AM
|
#38
|
Scoring Winger
|
Quote:
Originally Posted by firebug
|
and if you're short on column space there's HLOOKUP
|
|
|
08-11-2017, 09:08 AM
|
#39
|
Franchise Player
|
Quote:
Originally Posted by firebug
|
Thanks for this. I'll take a look at this and try and learn for the next time I need to do something like this! So much of excel that I do not know...
|
|
|
02-23-2018, 10:54 AM
|
#40
|
First Line Centre
Join Date: Nov 2007
Location: Airdrie, Alberta
|
Maybe someone can help me out.
We have a spreadsheet imported from our phone system, yesterday users could highlight cells and autosum average and get average numbers. Excel updated and today no matter what I try on those cells I get #DIV/0! but if I type anywhere else on the same spreadsheet and try it works fine making me think Excel isn't seeing the numbers correctly. I tried a copy/paste multiply by 1 to force numbers to change and also copy paste to another blank section and back and still nothing. Any ideas?
Edit: Also if I just =sum them it always totals 0 which would cause the above error so Excel thinks all the values are 0 for some reason
Last edited by Raekwon; 02-23-2018 at 10:57 AM.
|
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
All times are GMT -6. The time now is 03:35 PM.
|
|