Excel questions thread

[calumniate]

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

[Fuzz]

I've got it figured for you, but it's dinner time. Will post solution soonish.

[Fuzz]

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.

[RW99]

Thanks a bunch, I was thinking of a much more complicated formula. Math ftw.

[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

[sureLoss]

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.

[EldrickOnIce]

^ Thanks. Both worked perfectly

[flamesrule_kipper34]

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

[Fuzz]

Does this help?

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.

[flamesrule_kipper34]

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).

[calumniate]

Is your data safe enough to just give a power pivot workbook with slicers?

[flamesrule_kipper34]

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).

[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.

[jwslam]

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

[pseudoreality]

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.

[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))))))

[firebug]

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

[jwslam]

Originally Posted by firebug

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

[Weitz]

Originally Posted by firebug

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...

[Raekwon]

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