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)

Networkdays.intl

When it seems like something should be possible, hit F1.

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

 
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)

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

 
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.
Click to expand...
Try paste without formatting or paste as plain text.

 
Last edited by a moderator:
hagmania said:
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.
Click to expand...
Try paste without formatting or paste as plain text.
Click to expand...
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.

 
Last edited by a moderator:
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:

 
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.
Click to expand...
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.

 
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:
Click to expand...
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. 

 
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. 
Click to expand...
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.

 
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: AssociateColumn B: ScoreCreate 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.6Associate: Score:Brian 40Julie 30
Steve 30
Bob 20
Joe 10Brian 100Julie 100Steve 100Bob 100Joe 100Brian 200Julie 200Steve 200Bob 200Joe 200Brian 10
Julie 10
Steve 10
Bob 10
Joe 10Brian 50Julie 50Steve 50Bob 50Joe 50

 
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: AssociateColumn B: ScoreCreate 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.6Associate: Score:Brian 40Julie 30
Steve 30
Bob 20
Joe 10Brian 100Julie 100Steve 100Bob 100Joe 100Brian 200Julie 200Steve 200Bob 200Joe 200Brian 10
Julie 10
Steve 10
Bob 10
Joe 10Brian 50Julie 50Steve 50Bob 50Joe 50
Click to expand...
Use a =SUMIF > 39

 
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!

 
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!
Click to expand...
So you want each cell be a hyperlink to different tabs named "open stores", "closed stores", etc?

 
Ned said:
So you want each cell be a hyperlink to different tabs named "open stores", "closed stores", etc?
Click to expand...
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.

 
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.
Click to expand...
That would require a macro. I could write something but would need the workbook. 

 
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?

 
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?
Click to expand...
=IF(MONTH(A1)<10,YEAR(A1),YEAR(A1)+1)

?

 
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.
Click to expand...
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

 
  • Smile
Reactions: Ned
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
Click to expand...
wife had column formatted as date, not numerical............

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

 
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.

 
Last edited by a moderator:
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

 
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
Click to expand...
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. 

 
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?

 
Last edited by a moderator:
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?
Click to expand...
I don't know of a setting that controls this.

 
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.

 
Last edited by a moderator:
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.
Click to expand...
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. 

 
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.

 
Last edited by a moderator:
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.
Click to expand...
Paste Special - Values 

 
Ned said:
Paste Special - Values 
Click to expand...
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.

 
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

 
Last edited by a moderator:
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
Click to expand...
Why can't you just format the cells as Text and then paste them?

 
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
Click to expand...
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).

 
You must log in or register to reply here.

Similar threads

ragincajun
Excel AI!!!!
Replies
26
Views
986
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
443
bigbottom
bigbottom

Users who are viewing this thread

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