What's new
Fantasy Football - Footballguys Forums

Welcome to Our Forums. Once you've registered and logged in, you're primed to talk football, among other topics, with the sharpest and most experienced fantasy players on the internet.

***Official*** Excel Help Corner (2 Viewers)

Does she have any filters applied to the pivot table?

Also, if the values were added after the pivot table was created, just making sure - she is refreshing the pivot table, correct?

 
Fellow geeks, in updating links, when I hit change source and click the file to update my links to, excel sometimes makes me hit changes source several times in order for all cells to update almost as if it's updating one cell at a time. Any thoughts on how to fix this?

ETA: whenever I change the source I open the file I am changing to, and sometimes even open the old file in an attempt to fix the issue.

ETA 2: it seems to be more of a problem in cells where I have 2 links in one cell, for example cell W15 is linking to 2 different files.

 
Last edited by a moderator:
I have a sheet that totals a small list, and I would like to be able to remove a cell from the total when I make the cell 'strike-thru.' I have the calculation part set up, but it doesn't automatically update. How do I set up a button to recalculate?

TIA!

 
TheWalkmen said:
Fellow geeks, in updating links, when I hit change source and click the file to update my links to, excel sometimes makes me hit changes source several times in order for all cells to update almost as if it's updating one cell at a time. Any thoughts on how to fix this?

ETA: whenever I change the source I open the file I am changing to, and sometimes even open the old file in an attempt to fix the issue.

ETA 2: it seems to be more of a problem in cells where I have 2 links in one cell, for example cell W15 is linking to 2 different files.
Click to expand...
Hmmmm I’ve never run into that (I hate linking files), but I’m wondering if you need to turn off auto calc first before you start changing. 

 
Mr. Ected said:
I have a sheet that totals a small list, and I would like to be able to remove a cell from the total when I make the cell 'strike-thru.' I have the calculation part set up, but it doesn't automatically update. How do I set up a button to recalculate?

TIA!
Click to expand...
Ironically enough - so you have auto calc turned on?

 
Ned said:
Mr. Ected said:
I have a sheet that totals a small list, and I would like to be able to remove a cell from the total when I make the cell 'strike-thru.' I have the calculation part set up, but it doesn't automatically update. How do I set up a button to recalculate?

TIA!
Click to expand...
Ironically enough - so you have auto calc turned on?
Click to expand...
Yes, the issue is when I change the format of a cell to 'strike-thru' it doesn't qualify as something that will auto calc.

 
ragincajun said:
How do I remove a leading space from a cell with a date.  I tried TRIM to no avail.
Click to expand...
Try this (where A1 is the cell containing the space + date):

=RIGHT(A1,LEN(A1)-1)

 
not an expert, but can get a few things done...

How do I do this?

Property A
Property B
Property C 
A
C
B
C
A
B
C
B
A
B
C

Three VRBO properties above listed by move in date in COL A.
COL B has the rent collected from each guest visit.
How do I automatically add up just Property A rents?  B?  C? 
TIA  

 
Getzlaf15 said:
not an expert, but can get a few things done...

How do I do this?

Property A
Property B
Property C 
A
C
B
C
A
B
C
B
A
B
C

Three VRBO properties above listed by move in date in COL A.
COL B has the rent collected from each guest visit.
How do I automatically add up just Property A rents?  B?  C? 
TIA  
Click to expand...
If you put the name of the property in COL C, then you can do SUMIF(<range of col c>, <name of property a>, <range of col b>) to get the rents of property a. Change the second value for properties b and c. 

 
Getzlaf15 said:
not an expert, but can get a few things done...

How do I do this?

Property A
Property B
Property C 
A
C
B
C
A
B
C
B
A
B
C

Three VRBO properties above listed by move in date in COL A.
COL B has the rent collected from each guest visit.
How do I automatically add up just Property A rents?  B?  C? 
TIA  
Click to expand...
Depending on how the rest of your data is laid out, a pivot table is the quickest way. 

 
Getzlaf15 said:
not an expert, but can get a few things done...

How do I do this?

Property A
Property B
Property C 
A
C
B
C
A
B
C
B
A
B
C

Three VRBO properties above listed by move in date in COL A.
COL B has the rent collected from each guest visit.
How do I automatically add up just Property A rents?  B?  C? 
TIA  
Click to expand...
Assuming your data is laid out as you have listed it here, with the Property Name in Column A and the rents in column B, the easiest way would be a pivot table.

Just select your data (Columns A and B - even easier if you format the data to be a table, so you only have to reference the table name and your data source automatically expands as you add more data), then in the pivot table layout drag column A to be the Rows, and column B to be summed in the data section. Voila, instant sums of the rent. And as you add more data to the table, simply right-click your pivot table and select refresh for updated totals.

If you also have a column with the dates of the rent, you can also add that into your pivot table if you want to filter a certain date range, ie a particular year or month.

 
I'm using excel 2010 on windows 7.

with most of the .xlsx worksheets I try to open (simple sums within, no graphics), I get the following error at opening:

- Excel found unreadable conent in '*filename.xlsx'. Do you want to recover the contents of this workbook? If you trust the source, click Yes.

I click Yes and get this:

- Excel was bale to open the file by repairing or removing the unreadable content.

  -Removed Feature: Worksheet properties from /xl/worksheets/sheet1.xml part

Here's the recovery log:

<?xml version="1.0" encoding="UTF-8" standalone="true"?>

-<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">

<logFileName>error104160_03.xml</logFileName>

<summary>Errors were detected in file 'K:\Name\Docs\06- Bid\Bid Comparison.xlsx'</summary>

-<removedFeatures summary="Following is a list of removed features:">

<removedFeature>Removed Feature: Worksheet properties from /xl/worksheets/sheet1.xml part</removedFeature>

</removedFeatures>

</recoveryLog>
Click to expand...


After this, it opens- but slightly reformatted. every. single. time. I then have to save as back to the original file name- won't let me just save it.

Any help would be much appreciated. 

eta: I tried the google, but surprisingly not much help... or at least any in spoken in a way I could understand.

 
Last edited by a moderator:
I suck at all things excel. I'm trying to build something like this  and can't even get past the top part where I have the larger text in two different title rows  and the left column where the sheets rows are grouped into different sections. 

Can anyone throw me life preserver?

thank you

 
simsarge said:
I suck at all things excel. I'm trying to build something like this  and can't even get past the top part where I have the larger text in two different title rows  and the left column where the sheets rows are grouped into different sections. 

Can anyone throw me life preserver?

thank you
Click to expand...
'Merge cells' or 'center across selection' is going to do what you want for that stuff.

Hard to say what else without knowing specifics.

 
Ned said:
'Merge cells' or 'center across selection' is going to do what you want for that stuff.

Hard to say what else without knowing specifics.
Click to expand...
I absolutely HATE merged cells.  It makes it so you can't copy/paste values from an entire sheet from one file into another file because the "formatting isn't the same" or whatever the error is.

 
bryhamm said:
I absolutely HATE merged cells.  It makes it so you can't copy/paste values from an entire sheet from one file into another file because the "formatting isn't the same" or whatever the error is.
Click to expand...
That's why "Center Across Selection" is your friend. Essentially the same display without the formatting issues from merging.

 
acarey50 said:
That's why "Center Across Selection" is your friend. Essentially the same display without the formatting issues from merging.
Click to expand...
Yep.  I use that, or I turn on the wrap text within a cell.  Both have useful purposes and don't have that copy/paste issue.

 
Ned said:
'Merge cells' or 'center across selection' is going to do what you want for that stuff.

Hard to say what else without knowing specifics.
Click to expand...
Merge worked perfectly! I was pretty much starting from scratch text wise, so copy/paste wasn't an issue.

Thanks tons

 
bryhamm said:
I absolutely HATE merged cells.  It makes it so you can't copy/paste values from an entire sheet from one file into another file because the "formatting isn't the same" or whatever the error is.
Click to expand...
I'm going to change our warehousing provider, which does more than a million bucks a year from us.

One of the driving reasons is their reports all contain merged cells at the top and the bottom of every file that need to be deleted out before I start filtering and extracting data.

 
Hello,

I am trying to do a simple formula:

If Column A OR Column B = YES, then Column C should output FAIL ...else PASS

How do i write this?

Example:
Column A = YES
Column B = YES
Column C = FAIL

Column A = YES
Column B = NO
Column C = FAIL

Column A = NO
Column B = YES
Column C = FAIL

Column A = NO
Column B = NO
Column C = PASS

 
Help!... I have a report that I can export from square into Excel that shows every transaction we have done.  Our current list last year is about 10K transactions.  How can i have Excel count all the transactions on 1/1/2018, 1/2/2018 etc etc so we can then take that info into a daily summary sheet we have that compares dollars per day.  I now want to track transaction per day as well.  I do not care for all the other data that is given, just a count of each days transactions.  

Here is a sample from the Excel sheet:

12/31/2018 12:59:00

12/31/2018 12:57:49

12/31/2018 12:55:19

12/31/2018 12:51:04

12/31/2018 12:35:22

12/30/2018 18:17:29

12/30/2018 18:16:24

12/30/2018 18:07:05

12/30/2018 18:03:14

12/30/2018 17:56:35

12/30/2018 17:43:58

i want to have an output that shows:

12/31/2018 5 transactions

12/30/2018 6 transactions

etc etc for each day of the year

thanks

Inhiding

 
Last edited by a moderator:
Inhiding said:
Help!... I have a report that I can export from square into Excel that shows every transaction we have done.  Our current list last year is about 10K transactions.  How can i have Excel count all the transactions on 1/1/2018, 1/2/2018 etc etc so we can then take that info into a daily summary sheet we have that compares dollars per day.  I now want to track transaction per day as well.  I do not care for all the other data that is given, just a count of each days transactions.  

Here is a sample from the Excel sheet:

12/31/2018 12:59:00

12/30/2018 17:43:58

i want to have an output that shows:

12/31/2018 5 transactions

thanks

Inhiding
Click to expand...
New column with =INT(cell reference with date/time) to convert timestamps to just the date.  Then do =COUNTIF() on that created column to count number of transactions per day. 

 
  • Smile
Reactions: Ned
Inhiding said:
Help!... I have a report that I can export from square into Excel that shows every transaction we have done.  Our current list last year is about 10K transactions.  How can i have Excel count all the transactions on 1/1/2018, 1/2/2018 etc etc so we can then take that info into a daily summary sheet we have that compares dollars per day.  I now want to track transaction per day as well.  I do not care for all the other data that is given, just a count of each days transactions.  

Here is a sample from the Excel sheet:

12/31/2018 12:59:00

12/31/2018 12:57:49

12/31/2018 12:55:19

12/31/2018 12:51:04

12/31/2018 12:35:22

12/30/2018 18:17:29
Click to expand...


Brony said:
New column with =INT(cell reference with date/time) to convert timestamps to just the date.  Then do =COUNTIF() on that created column to count number of transactions per day. 
Click to expand...
You should also be able to just do a PivotTable and group the date/time field by day. Then you can easily count the transactions by day (or month, or year, etc., whatever date based grouping you want) and also sum the transaction amounts to get the total dollars by day (or month, or year)

 
  • Smile
Reactions: Ned
been a long time, but wife has a new question;

her company has a fiscal year that goes from 10/1-9/30

in a spreadsheet, lets say she has column A with dates of events  she'd like column B to auto populate the quarter and column C to auto populate the FY that comes from column A.

any ideas?   i.e. 4/30/2019 in column A would be Q3 2019 and FY 2019 in B & C.

Thx

 
Chemical X said:
been a long time, but wife has a new question;

her company has a fiscal year that goes from 10/1-9/30

in a spreadsheet, lets say she has column A with dates of events  she'd like column B to auto populate the quarter and column C to auto populate the FY that comes from column A.

any ideas?   i.e. 4/30/2019 in column A would be Q3 2019 and FY 2019 in B & C.

Thx
Click to expand...
Formula in Column B would be:

="Q"&CHOOSE(MONTH(A2),2,2,2,3,3,3,4,4,4,1,1,1)&" "&ROUNDUP(YEAR(A2+92),0)

Formula in Column C would be:

="FY "&ROUNDUP(YEAR(A2+92),0)

 
Ignoramus said:
Formula in Column B would be:

="Q"&CHOOSE(MONTH(A2),2,2,2,3,3,3,4,4,4,1,1,1)&" "&ROUNDUP(YEAR(A2+92),0)

Formula in Column C would be:

="FY "&ROUNDUP(YEAR(A2+92),0)
Click to expand...
appreciate the help.....she made a slight tweak.

she would like the results in one column.  so, take the info from A and have column B reflect both the quarter and FY.  Thanks

 
I need to create a workbook that will allow me to copy the rows of data onto sheets within the workbook based on the value of certain cells.  

We have projects that are assigned to project managers (column C) and workshops (column J). If Sheet 1 is where all the data goes, how do I make Sheet 2 bring in all the data in a row for projects "Jeff" is working on, Sheet 3 "Bill", etc?  I will also want a sheet that copies the same data over for workshop "Shop1" and another for "Shop2".  
 

I know how to do individual cells but don't know how to move entire rows of data. 

Thanks nerds!

 
Chemical X said:
Any tips or tutorials for automating a PowerPoint presentation using Excel or engage Excel add in?

Thx
Click to expand...
Depends what you are trying to automate, but if it's just getting charts or tables to automatically update based on a change in Excel, you can build the object in Excel then paste it as a link to Powerpoint. Now the Powerpoint object is linked to the one in Excel, so changes in Excel will be reflected in Powerpoint. If this is something you will be distributing, you'll probably want to change the links to manually update so that users do not get the "Can't find linked file" popup when opening the file, or you'll want to convert the Powerpoint to PDF after updating.

If it's something more than that, would need to know the specifics.

 
Nick Vermeil said:
I know how to do individual cells but don't know how to move entire rows of data. 
Click to expand...
A row of data is just a collection of cells, so if you can do it for cells you should be able to do it for rows, right?  Could you explain why the way you do it for cells wouldn't extend to the whole row?

 
Ignoratio Elenchi said:
Could you explain why the way you do it for cells wouldn't extend to the whole row?
Click to expand...
I guess no? I'm able to move individual cells from one sheet to the other just by typing = and then clicking on the correct cell withing the workbook. What I'm trying to do is above my pay grade. 

In what I'm trying to do, the only common denominator of the rows i want to move is the data in column C. I want to copy every row on the Sheet where C=Jeff.  

 
Walking Boot said:
I have a bunch of cells with borders. Some only have borders on 2 or 3 sides. Some have the border on all 4 sides.

It might take me another 10, 20 or maybe even 40 years, but, at some point, some day, I will figure out why, when I copy/paste, sometimes it takes the borders and sometimes it doesn't. 

One of these days....
Click to expand...
I would recommend using Paste Special. You can then choose formula, format, value, etc.

 
Walking Boot said:
I just wish I could predict when it would copy borders, and when it won't. It's always a mystery what will happen. I swear it just seems random. 
Click to expand...
The downside of using MS products on Mac, I guess.  Weird issue (it'd drive me nuts too).

 
It depends on what you draw the borders around I believe.

Say you have a table from A1:C3, with borders drawn around the whole box, around the top row for headers, around the bottom row for totals, around A1:A3 and around C1:C3.

You'd think you have the whole thing bordered, and copy paste would take borders no matter the cell you picked. I think, in practice, if you tried to copy and paste B2 then it would be without any borders.

 
Similarly, if you tried to copy A2:C3, there wouldn't be a top border, A1:B3 wouldn't have a right border, etc.

 
Nick Vermeil said:
I guess no? I'm able to move individual cells from one sheet to the other just by typing = and then clicking on the correct cell withing the workbook. What I'm trying to do is above my pay grade. 

In what I'm trying to do, the only common denominator of the rows i want to move is the data in column C. I want to copy every row on the Sheet where C=Jeff.  
Click to expand...
If each manager only has one project, you can use VLOOKUP to find that data.

If managers have multiple projects, and thus multiple rows of data, the solution depends on if you need totals or to bring each project's set of data independently.

 
Last edited by a moderator:
hagmania said:
If each manager only has one project, you can use VLOOKUP to find that data.

If managers have multiple projects, and thus multiple rows of data, the solution depends on if you need totals or to bring each project's set of data independently.
Click to expand...
Each project's data independently.  All managers have multiple projects.  

Basically I want a master list of all projects on sheet one where we input the data, then I want that information replicated on separate sheets for each manager and each workshop.  

 
Last edited by a moderator:
Nick Vermeil said:
Each project's data independently.  All managers have multiple projects.  

Basically I want a master list of all projects on sheet one where we input the data, then I want that information replicated on separate sheets for each manager and each workshop.  
Click to expand...
Do you explicitly need different sheets or does a tool that changes information based upon the manager/workshop work?

 
Nick Vermeil said:
Like a drop down?  That would work.
Click to expand...
I would suggest a PivotTable, using the filters (slicer, bit more advanced but looks nice) to control manager/workshop.

The idea would be to select all of the table data, Insert >> PivotTable, default options and OK. From there drag and drop workshop and manager in the FILTERS section and all your data in the ROWS section in the order you'd like it presented.

At this point it will look like a wild and crazy mess so click anywhere in the PivotTable, Design >> Report Layout >> Show in Tabular Form. Design >> Report Layout >> Repeat All Item Labels might be needed. Finally, Design >> Subtotals >> Do Not Show Subtotals

 
You must log in or register to reply here.

Similar threads

ragincajun
Excel AI!!!!
Replies
26
Views
985
culdeus
culdeus
L
Work Efficiency & Organization
Replies
15
Views
552
MAC_32
MAC_32
GordonGekko
What Are Harsh Truths About Real Life That You Would Prefer To Just Ignore ? (8/30)
Replies
5
Views
441
bigbottom
bigbottom

Users who are viewing this thread

Total: 3 (members: 0, guests: 3)
Top