Calgarypuck Forums - The Unofficial Calgary Flames Fan Community
Old 11-14-2018, 10:19 AM   #61
Wormius
Franchise Player
 
Wormius's Avatar
 
Join Date: Feb 2011
Location: Somewhere down the crazy river.
Exp:
Default

You want the same date in all of those cells, or incremented by some amount?
Wormius is offline   Reply With Quote
Old 11-14-2018, 10:22 AM   #62
Wormius
Franchise Player
 
Wormius's Avatar
 
Join Date: Feb 2011
Location: Somewhere down the crazy river.
Exp:
Default

You should just click on the first instance, and double-click on the lower right corner where it becomes a plus-sign. That will auto-fill everything until the end of what Excel thinks is the end of the column.
Wormius is offline   Reply With Quote
Old 11-14-2018, 01:10 PM   #63
Huntingwhale
Franchise Player
 
Huntingwhale's Avatar
 
Join Date: Sep 2008
Exp:
Default

I think I figured it out...just converted the dates/times to total seconds and it makes it a lot easier to manipulate.

Still sucks though.
Huntingwhale is offline   Reply With Quote
Old 11-16-2018, 03:53 PM   #64
Superfraggle
First Line Centre
 
Join Date: Nov 2003
Location: Calgary
Exp:
Default

Probably too later, and not totally sure I understand what you are trying to do, but my guess is you just want to advance by one hour every row going down?

If so, in cell B2, enter both the date and the time (8/1/2018 1:22:52 PM). Format it so that it only shows the time. In A2, just refer to B2 (=B2) and format that one to show the date.

In cell B3, just enter the formula (=B2+(1/24)). That will advance it by one hour. Then just copy the formulas in the two columns down until you get where you want to be.
Superfraggle is online now   Reply With Quote
Old 12-04-2018, 11:12 AM   #65
PsYcNeT
Franchise Player
 
PsYcNeT's Avatar
 
Join Date: May 2004
Location: Marseilles Of The Prairies
Exp:
Default

Alright here's a fun one.

Lets say I want to create a WFM log for employees, so I create a "Roster" sheet on the first tab. I use the Name Manager to create a Name called "Roster", that refers to column A in the "Roster" sheet. Then, if I use the formula "=ROSTER" in the first column pf every subsequent Sheet, it will show a live update when a new employee is added to the ROSTER tab.

However, if I have formulae (using INDEX+MATCH or otherwise) or data in each row on the other sheets, when a new employee is added to the ROSTER sheet, it bumps the rest down in column A, but all the other columns stay in place.

Ultimately, how do I make it so when an employee is added to the Roster (and ergo added to the =ROSTER reference), it also effectively inserts a new Row in every associated sheet and keeps my formulae intact?
__________________

Quote:
Originally Posted by MrMastodonFarm View Post
Settle down there, Temple Grandin.
PsYcNeT is offline   Reply With Quote
Old 12-04-2018, 04:36 PM   #66
PsYcNeT
Franchise Player
 
PsYcNeT's Avatar
 
Join Date: May 2004
Location: Marseilles Of The Prairies
Exp:
Default

Figured it out using a combo of VBA, Index+Match function and workbook group edits.

Got rid of the Names altogether.
__________________

Quote:
Originally Posted by MrMastodonFarm View Post
Settle down there, Temple Grandin.
PsYcNeT is offline   Reply With Quote
Old 01-17-2019, 05:28 PM   #67
mrkajz44
First Line Centre
 
mrkajz44's Avatar
 
Join Date: Oct 2010
Location: Deep South
Exp:
Default

I want to set up a check-box at the end of a proof in a spreadsheet that I use for multiple legal entities. The check-box must be clicked to show the user did a final check of the proof. However, is there a way to force Excel to un-check this box if something in the proof changes? That way you'll know if the user did the final check and confirmed everything was okay and nothing was changed afterwards. Any ideas?
__________________
Much like a sports ticker, you may feel obligated to read this
mrkajz44 is offline   Reply With Quote
Old 01-18-2019, 09:22 AM   #68
mrkajz44
First Line Centre
 
mrkajz44's Avatar
 
Join Date: Oct 2010
Location: Deep South
Exp:
Default

Never mind on my question above, I've found a different solution. I'm going to add to the control page a threshold that is acceptable for the proof to be out. Sometime the proof will have to be zero, and sometimes within 1,000 is okay. By putting the threshold in the control panel page, I can update the threshold and then have an automatic check if the proof is reasonable.
__________________
Much like a sports ticker, you may feel obligated to read this
mrkajz44 is offline   Reply With Quote
Old 09-17-2019, 03:04 PM   #69
ken0042
Playboy Mansion Poolboy
 
ken0042's Avatar
 
Join Date: Apr 2004
Location: Close enough to make a beer run during a TV timeout
Exp:
Default

I have a question that I hope somebody might be able to help with. We are looking to make cells in Excel fillable with a date picker; similar to what you can do in Google Sheets.

First you set the cell properties



Then when you double click on the cell you get a date picker:



We are using O365 installed on the desktop. We see a 3rd party app that claims to do this, but installing it comes with security issues of the 3rd party getting some level of access to the sheet.

Also tried saving the Google sheet as .xlsx and it didn't work.

Any suggestions would be appreciated!
ken0042 is offline   Reply With Quote
Old 09-18-2019, 08:54 AM   #70
jwslam
Scoring Winger
 
Join Date: Jul 2014
Exp:
Default

Google took me here
https://www.youtube.com/watch?v=5pdcLTwkn5s
jwslam is offline   Reply With Quote
Old 09-18-2019, 09:12 AM   #71
ken0042
Playboy Mansion Poolboy
 
ken0042's Avatar
 
Join Date: Apr 2004
Location: Close enough to make a beer run during a TV timeout
Exp:
Default

Yeah, unfortunately that's the 3rd party app that also gets access to the Excel sheet that you are working on. Thanks though.
ken0042 is offline   Reply With Quote
Old 11-21-2019, 01:31 PM   #72
fundmark19
#1 Goaltender
 
Join Date: May 2009
Exp:
Default

Hey guys I am having trouble with an excel formula. I am trying to to compile totals on a summary page over 12 sheets (months of year). I know sumif with indirect should be able to do this but I can't wrap my head on the formula.

on Summary Tab I want the sum total in j20 (this is the name searching in b5:35)

on each month tab I need to search for a specific name in b5:b35 and then add the corresponding totals between d5:d35

Each month is named Jan,Feb,Mar etc..
fundmark19 is offline   Reply With Quote
Old 11-21-2019, 03:06 PM   #73
Hanni
First Line Centre
 
Hanni's Avatar
 
Join Date: Apr 2009
Exp:
Default

Do you even need to worry about indirect?

I just did 3 months and a random bunch of names in B5:B35

Code:
=SUMIF(Jan!B5:B9, "John", Jan!D5:D9)+SUMIF(Feb!B5:B9, "John", Feb!D5:D9)+SUMIF(Mar!B5:B9, "John", Mar!D5:D9)
Instead of the name in quotations you could point it to another cell that has the name you want to add to the total

Code:
 =SUMIF(Jan!B5:B9, Summary!J1, Jan!D5:D9)

Last edited by Hanni; 11-21-2019 at 03:08 PM.
Hanni is offline   Reply With Quote
The Following User Says Thank You to Hanni For This Useful Post:
Old 11-21-2019, 04:09 PM   #74
fundmark19
#1 Goaltender
 
Join Date: May 2009
Exp:
Default

Thanks that works fine. I was hoping to save time typing by using the fancier formula but I guess why break something that works
fundmark19 is offline   Reply With Quote
Old 11-22-2019, 02:05 PM   #75
jwslam
Scoring Winger
 
Join Date: Jul 2014
Exp:
Default

Is there a reason why it's all on different sheets? Probably easier to put it all together and pivot table it. Less chance for error that way too.

edit: If you don't know what a pivot table is or how it works, the tutorial built into excel is super useful. You can find it in "File, New" in Office365

Last edited by jwslam; 11-22-2019 at 02:15 PM.
jwslam is offline   Reply With Quote
Old 11-22-2019, 03:56 PM   #76
fundmark19
#1 Goaltender
 
Join Date: May 2009
Exp:
Default

Quote:
Originally Posted by jwslam View Post
Is there a reason why it's all on different sheets? Probably easier to put it all together and pivot table it. Less chance for error that way too.

edit: If you don't know what a pivot table is or how it works, the tutorial built into excel is super useful. You can find it in "File, New" in Office365
I haven't messed around with pivot tables but it may be of use if I can dumb it down enough for other team members. We have the different sheets to track activities by the month and doing month by month plus yearly analysis which could probably be accomplished by a pivot table as well
fundmark19 is offline   Reply With Quote
Old 11-23-2019, 09:30 AM   #77
jwslam
Scoring Winger
 
Join Date: Jul 2014
Exp:
Default

Quote:
Originally Posted by fundmark19 View Post
I haven't messed around with pivot tables but it may be of use if I can dumb it down enough for other team members. We have the different sheets to track activities by the month and doing month by month plus yearly analysis which could probably be accomplished by a pivot table as well
Once you get the data set up in a pivot table, it would just be a matter of refreshing the table for new entries and then turning the filters to get what you want
The filtering is pretty straight forward, just dropdowns.

Depending on the size of your business you could even go as far as setting up an analytics interface with PowerBI or more costly, Tableau
(Apparently Power BI is included in all Office 365 subscriptions if your work already has this)
jwslam is offline   Reply With Quote
Old 04-08-2020, 01:19 PM   #78
fundmark19
#1 Goaltender
 
Join Date: May 2009
Exp:
Default

ok guys I am back. I have downtime so started messing with pivot tables and ran into a new issue. In one of my pivot tables I have 4 columns that are Text and not numbers I would like manipulate.

I have learnt about Measure's and have one in place however when I try to add more it gives me an error. Can you have multiple measures in one pivot? This is measure I am using

=CONCATENATEX(Values(Table1[Referral Source]), Table1[Referral Source], ", ")

Then I thought I could just mirror the data set and hide columns I don't need for this sheet. When I go to mirror I can't locate the file since it isn't locally stored and on our onedrive.

Any thoughts?
fundmark19 is offline   Reply With Quote
Old 05-08-2020, 09:45 AM   #79
jwslam
Scoring Winger
 
Join Date: Jul 2014
Exp:
Default

Is there a way to name dynamic formulas?

For example


So in
C1 =A1*B1
C2 =A2*B2

Is there something that I can use for example that I can just type
=TIMES

where TIMES knows to take the left-2 and left-1 columns to multiply them?
So C3 knows it's A3*B3
or even as far as H7 knows it's F7*G7

Thanks!
jwslam is offline   Reply With Quote
Old 05-08-2020, 10:39 AM   #80
calumniate
Franchise Player
 
calumniate's Avatar
 
Join Date: Feb 2007
Location: A small painted room
Exp:
Default

Quote:
Originally Posted by fundmark19 View Post
ok guys I am back. I have downtime so started messing with pivot tables and ran into a new issue. In one of my pivot tables I have 4 columns that are Text and not numbers I would like manipulate.

I have learnt about Measure's and have one in place however when I try to add more it gives me an error. Can you have multiple measures in one pivot? This is measure I am using

=CONCATENATEX(Values(Table1[Referral Source]), Table1[Referral Source], ", ")

Then I thought I could just mirror the data set and hide columns I don't need for this sheet. When I go to mirror I can't locate the file since it isn't locally stored and on our onedrive.

Any thoughts?
My thought is that isn't really a measure, but more like a calculated column. You can have multiple measures in powerpivot for sure, but measures need expression elements in them.

Not sure if this helps.. You could reference your new calculated column with a measure. If you're using regular pivot tables (not powerpivot), you're screwed

Last edited by calumniate; 05-08-2020 at 10:43 AM.
calumniate is online now   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 09:35 AM.

Calgary Flames
2023-24




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