Calgarypuck Forums - The Unofficial Calgary Flames Fan Community
Old 11-23-2016, 05:22 PM   #21
calumniate
Franchise Player
 
calumniate's Avatar
 
Join Date: Feb 2007
Location: A small painted room
Exp:
Default

Quote:
Originally Posted by RW99 View Post
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
calumniate is offline   Reply With Quote
The Following User Says Thank You to calumniate For This Useful Post:
Old 11-23-2016, 05:28 PM   #22
Fuzz
Franchise Player
 
Fuzz's Avatar
 
Join Date: Mar 2015
Exp:
Default

I've got it figured for you, but it's dinner time. Will post solution soonish.
Fuzz is online now   Reply With Quote
The Following User Says Thank You to Fuzz For This Useful Post:
Old 11-23-2016, 06:11 PM   #23
Fuzz
Franchise Player
 
Fuzz's Avatar
 
Join Date: Mar 2015
Exp:
Default

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.
Fuzz is online now   Reply With Quote
The Following 2 Users Say Thank You to Fuzz For This Useful Post:
Old 11-23-2016, 07:09 PM   #24
RW99
First Line Centre
 
RW99's Avatar
 
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
Exp:
Default

Thanks a bunch, I was thinking of a much more complicated formula. Math ftw.
RW99 is offline   Reply With Quote
Old 05-14-2017, 09:37 PM   #25
EldrickOnIce
Franchise Player
 
EldrickOnIce's Avatar
 
Join Date: Jul 2002
Location: Chicago
Exp:
Default

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
EldrickOnIce is offline   Reply With Quote
Old 05-14-2017, 11:40 PM   #26
sureLoss
Some kinda newsbreaker!
 
sureLoss's Avatar
 
Join Date: May 2004
Location: Learning Phaneufs skating style
Exp:
Default

Quote:
Originally Posted by EldrickOnIce View Post
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.
sureLoss is offline   Reply With Quote
The Following User Says Thank You to sureLoss For This Useful Post:
Old 05-15-2017, 07:41 PM   #27
EldrickOnIce
Franchise Player
 
EldrickOnIce's Avatar
 
Join Date: Jul 2002
Location: Chicago
Exp:
Default

^ Thanks. Both worked perfectly
EldrickOnIce is offline   Reply With Quote
Old 06-25-2017, 01:51 PM   #28
flamesrule_kipper34
Franchise Player
 
flamesrule_kipper34's Avatar
 
Join Date: Aug 2008
Location: Calgary, AB
Exp:
Default

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
flamesrule_kipper34 is offline   Reply With Quote
Old 06-25-2017, 08:46 PM   #29
Fuzz
Franchise Player
 
Fuzz's Avatar
 
Join Date: Mar 2015
Exp:
Default

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.
Fuzz is online now   Reply With Quote
Old 06-26-2017, 08:44 PM   #30
flamesrule_kipper34
Franchise Player
 
flamesrule_kipper34's Avatar
 
Join Date: Aug 2008
Location: Calgary, AB
Exp:
Default

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).
flamesrule_kipper34 is offline   Reply With Quote
Old 06-26-2017, 08:54 PM   #31
calumniate
Franchise Player
 
calumniate's Avatar
 
Join Date: Feb 2007
Location: A small painted room
Exp:
Default

Is your data safe enough to just give a power pivot workbook with slicers?
calumniate is offline   Reply With Quote
Old 06-27-2017, 09:04 AM   #32
flamesrule_kipper34
Franchise Player
 
flamesrule_kipper34's Avatar
 
Join Date: Aug 2008
Location: Calgary, AB
Exp:
Default

Quote:
Originally Posted by calumniate View Post
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).
flamesrule_kipper34 is offline   Reply With Quote
Old 08-03-2017, 12:18 PM   #33
Weitz
Franchise Player
 
Join Date: Mar 2013
Exp:
Default

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.
Weitz is online now   Reply With Quote
Old 08-03-2017, 12:26 PM   #34
jwslam
Scoring Winger
 
Join Date: Jul 2014
Exp:
Default

Quote:
Originally Posted by Weitz View Post
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
jwslam is offline   Reply With Quote
Old 08-03-2017, 12:29 PM   #35
pseudoreality
Powerplay Quarterback
 
pseudoreality's Avatar
 
Join Date: Jul 2012
Exp:
Default

Quote:
Originally Posted by Weitz View Post
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.
pseudoreality is offline   Reply With Quote
Old 08-03-2017, 01:13 PM   #36
Weitz
Franchise Player
 
Join Date: Mar 2013
Exp:
Default

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.
Weitz is online now   Reply With Quote
Old 08-04-2017, 09:20 AM   #37
firebug
Powerplay Quarterback
 
firebug's Avatar
 
Join Date: Aug 2002
Location: Mayor of McKenzie Towne
Exp:
Default

Quote:
Originally Posted by Weitz View Post
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
firebug is online now   Reply With Quote
The Following User Says Thank You to firebug For This Useful Post:
Old 08-05-2017, 11:29 AM   #38
jwslam
Scoring Winger
 
Join Date: Jul 2014
Exp:
Default

Quote:
Originally Posted by firebug View Post
You'll kick yourself when you realize how simple vlookups are.

http://excelcentral.com/excel2013/ex...-in-excel.html
and if you're short on column space there's HLOOKUP
jwslam is offline   Reply With Quote
Old 08-11-2017, 09:08 AM   #39
Weitz
Franchise Player
 
Join Date: Mar 2013
Exp:
Default

Quote:
Originally Posted by firebug View Post
You'll kick yourself when you realize how simple vlookups are.

http://excelcentral.com/excel2013/ex...-in-excel.html
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...
Weitz is online now   Reply With Quote
Old 02-23-2018, 10:54 AM   #40
Raekwon
First Line Centre
 
Raekwon's Avatar
 
Join Date: Nov 2007
Location: Airdrie, Alberta
Exp:
Default

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.
Raekwon is offline   Reply With Quote
Reply

Tags
excel

Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -6. The time now is 08:29 AM.

Calgary Flames
2023-24




Powered by vBulletin® Version 3.8.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright Calgarypuck 2021