Jump to content
Fantasy Football - Footballguys Forums

***Official*** Excel Help Corner


Recommended Posts

  • 5 weeks later...
  • Replies 927
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

Popular Posts

Also sometimes where that doesn't turn out well, I've pasted into Notepad then copy and pasted that to Excel and it's done better.

I'm an Excel nerd to the hilt and enjoy helping others when it comes to Excel problems, so I figured I'd start a thread where y'all can ask questions on how to do certain things in Excel. I'm game fo

Hello,

I have a question with a look-up file that I am hoping for help on.

Look-up Data Tab Name: Errors
Data: Column B, Row 2+

Example of Data:
12345
11111
22222

in my main workbook, in Column CG, I want to output 2 possible values if data in Column B matches the data in "Errors" look-up tab.
If the data in Column B matches the data in "Errors", output a '1'.
If the data in Column B does not match the data in "Errors", output a '0'.

Link to post
Share on other sites

Need to know what column the data you're looking up against on the error tab to complete this(bold)...I wrote it on my iPhone, so I can't guarantee it's error free.  You should get the drift though...

=IF(ISERROR(MATCH(B1,Errors!D:D,0)),0,1)

Link to post
Share on other sites
45 minutes ago, tjnc09 said:

@Ned

If I copy website data into Excel, it's pasting all of these small pictures that are used on the site.  Is there anyway to delete them all at once?  They don't delete when you delete the column/row as they "sit" on top of the column/row.

Try paste without formatting or paste as plain text.

Edited by hagmania
  • Like 1
Link to post
Share on other sites
1 hour ago, tjnc09 said:

@Ned

If I copy website data into Excel, it's pasting all of these small pictures that are used on the site.  Is there anyway to delete them all at once?  They don't delete when you delete the column/row as they "sit" on top of the column/row.

I recommend ASAP Utilities, which has a function called "Remove all objects" that will get rid of those.

http://www.asap-utilities.com/

 

  • Like 3
Link to post
Share on other sites
1 hour ago, hagmania said:
2 hours ago, tjnc09 said:

@Ned

If I copy website data into Excel, it's pasting all of these small pictures that are used on the site.  Is there anyway to delete them all at once?  They don't delete when you delete the column/row as they "sit" on top of the column/row.

Try paste without formatting or paste as plain text.

Also sometimes where that doesn't turn out well, I've pasted into Notepad then copy and pasted that to Excel and it's done better.

Edited by GregR
  • Like 4
Link to post
Share on other sites

Thanks all.  It's a site I copy from daily.  I copied the text/paste value to another sheet, relinked my formulas and deleted my old sheet w all the pictures.  That works but not something I want to do daily.  Cut my size from 5.5MB to 2.5 MB (eight days of pastes) so it's definitely a problem.

 

Will check out ASAP.   Appreciate it :thumbup:

Link to post
Share on other sites
11 minutes ago, GregR said:

Also sometimes where that doesn't turn out well, I've pasted into Notepad then copy and pasted that to Excel and it's done better.

Yeah, I have some fairly complex formulas to format the data I pull from the site already so I was trying not to have to redo them.  I'm sure if I paste as text or from another source I would have to redo the formulas.

Link to post
Share on other sites
22 minutes ago, tjnc09 said:

Thanks all.  It's a site I copy from daily.  I copied the text/paste value to another sheet, relinked my formulas and deleted my old sheet w all the pictures.  That works but not something I want to do daily.  Cut my size from 5.5MB to 2.5 MB (eight days of pastes) so it's definitely a problem.

 

Will check out ASAP.   Appreciate it :thumbup:

Depending on the formatting of the website you're pulling from, you can make it auto download from the site. You can use websites as an external data source - see the Data ribbon.

 

It's pretty easy to set up. Once you set that up, the data connection will save with your workbook. Then all you have to do is hit refresh any time you want to update the data. 

Link to post
Share on other sites
12 minutes ago, Ned said:

Depending on the formatting of the website you're pulling from, you can make it auto download from the site. You can use websites as an external data source - see the Data ribbon.

 

It's pretty easy to set up. Once you set that up, the data connection will save with your workbook. Then all you have to do is hit refresh any time you want to update the data. 

Yeah, I had previously tried that using Data->Get External Data->From Web.  The data I want to copy in never actual shows up when I go to link it, unfortunately.

Link to post
Share on other sites

Here's another question.

In a new tab, I want to aggregate the data from a tab called 'Data', using Column A (Associate) to sum up the scores that an associate has in Column B (Score) to create 2 new values:
Data Tab:
Column A: Associate
Column B: Score

Create these new values in a new tab:
# of Orders:
Total Score:

However, as an added twist, I want to exclude (drop) any scores LT 40 below. 

So:
Brian 4 orders and an average of 97.5 (390/4)
Julie 3 orders and an average of 116.6

Associate: Score:
Brian 40
Julie 30
Steve 30
Bob 20
Joe 10

Brian 100
Julie 100
Steve 100
Bob 100
Joe 100
Brian 200
Julie 200
Steve 200
Bob 200
Joe 200
Brian 10
Julie 10
Steve 10
Bob 10
Joe 10

Brian 50
Julie 50
Steve 50
Bob 50
Joe 50

Link to post
Share on other sites
1 hour ago, cubd8 said:

Here's another question.

In a new tab, I want to aggregate the data from a tab called 'Data', using Column A (Associate) to sum up the scores that an associate has in Column B (Score) to create 2 new values:
Data Tab:
Column A: Associate
Column B: Score

Create these new values in a new tab:
# of Orders:
Total Score:

However, as an added twist, I want to exclude (drop) any scores LT 40 below. 

So:
Brian 4 orders and an average of 97.5 (390/4)
Julie 3 orders and an average of 116.6

Associate: Score:
Brian 40
Julie 30
Steve 30
Bob 20
Joe 10

Brian 100
Julie 100
Steve 100
Bob 100
Joe 100
Brian 200
Julie 200
Steve 200
Bob 200
Joe 200
Brian 10
Julie 10
Steve 10
Bob 10
Joe 10

Brian 50
Julie 50
Steve 50
Bob 50
Joe 50

Use a =SUMIF > 39

Link to post
Share on other sites

got a hard to describe question.

 

wife is building an excel with multiple tabs.  on page 1 she is summarizing info like so:

 

open stores - 15

closed stores - 10

relocate stores - 5

 

on one of the other tabs all the info she is using on the summary tab is contained.  she wants to make the make words "open stores" become clickable and take her to that info tab showing only open stores.  then make "closed stores" clickable to do the same.

 

so far, all I can figure out is how to make them clickable, but not not remove the unwanted items.

 

any help would be great!

Link to post
Share on other sites
3 minutes ago, Chemical X said:

got a hard to describe question.

 

wife is building an excel with multiple tabs.  on page 1 she is summarizing info like so:

 

open stores - 15

closed stores - 10

relocate stores - 5

 

on one of the other tabs all the info she is using on the summary tab is contained.  she wants to make the make words "open stores" become clickable and take her to that info tab showing only open stores.  then make "closed stores" clickable to do the same.

 

so far, all I can figure out is how to make them clickable, but not not remove the unwanted items.

 

any help would be great!

So you want each cell be a hyperlink to different tabs named "open stores", "closed stores", etc?

Link to post
Share on other sites
1 minute ago, Ned said:

So you want each cell be a hyperlink to different tabs named "open stores", "closed stores", etc?

well, lets say there is a 99 column sheet that says, open, closed, relocate throughout. 

on that main page, she wants to click the word closed and have it bring up the closed stores only from the 99 columns, leaving out the other 66.

Link to post
Share on other sites
12 minutes ago, Chemical X said:

so, the filtering is great, but I want to click the name on tab 1 and have it bring up that info from tab 2, filtering out the unwanted info.

That would require a macro. I could write something but would need the workbook. 

Link to post
Share on other sites
  • 2 weeks later...

need a formula.....

have a column of dates, let's say column A.

I would like a formula that can separate these dates into specific fiscal years.  i.e. - company's fiscal year is 10/1-9/30.

 

so a formula that breaks out column A dates for FYE 2016, 2017, 2018, 2019, 2020, etc.

 

any ideas?

Link to post
Share on other sites
4 minutes ago, Chemical X said:

need a formula.....

have a column of dates, let's say column A.

I would like a formula that can separate these dates into specific fiscal years.  i.e. - company's fiscal year is 10/1-9/30.

 

so a formula that breaks out column A dates for FYE 2016, 2017, 2018, 2019, 2020, etc.

 

any ideas?

=IF(MONTH(A1)<10,YEAR(A1),YEAR(A1)+1)

 

?

Link to post
Share on other sites
15 minutes ago, Donkey Derp said:

=IF(MONTH(A1)<10,YEAR(A1),YEAR(A1)+1)

 

?

 @Donkey Derp , thanks, but seems like it may be missing something, no?

let's say column A has 8/31/2016, 8/31/2018, and 8/31/2021....................need to have each of these separated into a separate pot for its' respective FYE.

Link to post
Share on other sites
15 minutes ago, Chemical X said:

 @Donkey Derp , thanks, but seems like it may be missing something, no?

let's say column A has 8/31/2016, 8/31/2018, and 8/31/2021....................need to have each of these separated into a separate pot for its' respective FYE.

What do you think is missing?

9/1/15 will return 2015

10/1/15 will return 2016

9/1/16 will return 2016

10/1/16 will return 2017

  • Like 1
Link to post
Share on other sites
18 minutes ago, Donkey Derp said:

What do you think is missing?

9/1/15 will return 2015

10/1/15 will return 2016

9/1/16 will return 2016

10/1/16 will return 2017

wife had column formatted as date, not numerical............

 

worked perfectly, I owe you a coke.  (drinkable version)

  • Like 2
Link to post
Share on other sites

Are you trying to add something from column A for each fiscal year?  You can use =SUMIF based on those formulas in column B.  If you don't want an additional column of formulas in column B, a  =SUMIFS (extra S) formula would most likely work.  The latter would take me a second to think about and write.

Clarify exactly what you need if the formula I typed isn't working as you expect.

Edited by Donkey Derp
can ignore
  • Like 2
Link to post
Share on other sites

asking for wife......

 

she has a vlookup that ties back to a column in a spreadsheet.  if she moves the column, the vlookup goes blank.

anyway to not have the vlookup go blank regardless of where the column lies?

 

thx

Link to post
Share on other sites
14 minutes ago, Chemical X said:

asking for wife......

 

she has a vlookup that ties back to a column in a spreadsheet.  if she moves the column, the vlookup goes blank.

anyway to not have the vlookup go blank regardless of where the column lies?

 

thx

I'm guessing your VLOOKUP hs the cell references locked with $. Take the $ out and it will adjust as you move your lookup column around. 

  • Like 1
Link to post
Share on other sites
28 minutes ago, Chemical X said:

asking for wife......

 

she has a vlookup that ties back to a column in a spreadsheet.  if she moves the column, the vlookup goes blank.

anyway to not have the vlookup go blank regardless of where the column lies?

 

thx

any chance your wife could just learn to use excel?

  • Like 1
Link to post
Share on other sites
On 12/11/2016 at 11:55 AM, Long Ball Larry said:

Excel 2010 automatically formats new sheets as tables (at least when expanded from pivot tables).

You can manually change it from a "table" to "a range", but anyone know of a way to make that the default setting?

I don't know of a setting that controls this.

Link to post
Share on other sites

Anyone know how to make excel not auto format long numbers into scientific notation during a paste?  Or not format "3-2" as a date.  Or keep leading zeroes on a zip code?

Saving it into a text file then importing, having to manually change every column to text is incredibly tedious.

Edited by BroadwayG
Link to post
Share on other sites

 

6 minutes ago, Walking Boot said:

I prefer index-match to vlookup

:goodposting: 

i never use VLOOKUP. It sucks. I think there's a post in the first couple of pages explaining the difference for folks interested. 

Link to post
Share on other sites
3 minutes ago, BroadwayG said:

Anyone know how to make excel not auto format long numbers into scientific notation during a paste?  Or not format "3-2" as a date.  Or keep leading zeroes on a zip code?

Saving it into a text file then importing, having to manually change every column to text is incredibly tedious.

For the zip code issue - either format the column to text before importing or make a custom format of '00000'. 

I haven't had to deal with numbers that large, so not sure about the long numbers issue. 

Link to post
Share on other sites

I'm copying/pasting from elsewhere, so preformatting isn't an option.  Thankfully they put the scientific notation limit at 11 so that unformatted phone numbers can be pasted ok.  However, a 12-digit UPC will get scientific notationed on pasting, unless of course it comes with a leading zero, then your data is up the creek.

Edited by BroadwayG
Link to post
Share on other sites
4 minutes ago, BroadwayG said:

I'm copying/pasting from elsewhere, so preformatting isn't an option.  Thankfully they put the scientific notation limit at 11 so that unformatted phone numbers can be pasted ok.  However, a 12-digit UPC will get scientific notationed on pasting, unless of course it comes with a leading zero, then your data is up the creek.

Paste Special - Values 

Link to post
Share on other sites
3 minutes ago, Ned said:

Paste Special - Values 

I'm copy/pasting from outside of Excel so I get the option of pasting as text or Unicode Text when I try paste special.  Neither one works to actually paste as text, it formats everything as a number.

Link to post
Share on other sites

Here are some values I'd like to copy and paste into Excel as text.  You'd think it wouldn't be a big deal, but this has terrorized me since the 1990s.

 

01234
1856324585855521
3-2

Edited by BroadwayG
Link to post
Share on other sites
25 minutes ago, BroadwayG said:

Here are some values I'd like to copy and paste into Excel as text.  You'd think it wouldn't be a big deal, but this has terrorized me since the 1990s.

 

01234
1856324585855521
3-2

Why can't you just format the cells as Text and then paste them?

Link to post
Share on other sites

 

12 hours ago, BroadwayG said:

Here are some values I'd like to copy and paste into Excel as text.  You'd think it wouldn't be a big deal, but this has terrorized me since the 1990s.

 

01234
1856324585855521
3-2

I just copy/pasted this fine as text.  No idea why you're not able to do it.

You said you've created a file and then imported it... is the file layout the same every time?  If so, you can easily macro the import (I understand how annoying the import wizard is).

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Recently Browsing   0 members

    No registered users viewing this page.


×
×
  • Create New...