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 (1 Viewer)
- Thread starter Ned
- Start date
Ned
Footballguy
TEXTJOIN is new in Excel 365. Other than that, I don't think there's that simple of a solution.
TheIronSheik
SUPER ELITE UPPER TIER
Chemical X
Footballguy
got an if then question.
if the number in cell is greater than 25% of the value in cell b, then cell c should be yes. if lower, then no.
any thoughts?
if the number in cell is greater than 25% of the value in cell b, then cell c should be yes. if lower, then no.
any thoughts?
Ned
Footballguy
assuming bold is cell A2, put this in C2...
=IF(A2/B2>0.25,"yes","no")
What about equal to 25%?
Ignoramus
Footballguy
Ned
Footballguy
So you're saying you want Sheet1 A1 to be red when it's greater than Sheet2 B2? If so, do this...
Select Sheet1 A1 and create a new conditional format. Choose 'Use a formula to determine which cells to format' and enter this formula "=$A$1>Sheet2!$B$2". Change the formatting as you need and that should take care of it.
Ned
Footballguy
Go to Manage Rules under Conditional Formatting. Edit the rule and use this formula "=A1>Sheet2!$B$2" (all you're doing is removing the locked ref to A1). Then make it apply to A1:F1.
Nick Vermeil
Footballguy
TheIronSheik
SUPER ELITE UPPER TIER
So I'm not really sure if this is Excel or my computer. But I've tried changing computer settings and can't fix it. Either way, maybe someone will know what to do here. Last week, we moved offices and everything was unplugged and plugged back in. My laptop itself was in my possession, so I don't think really anything has/could've changed.
I have a spreadsheet open on each of my 2 screens. When I hover over screen 1, and scroll with the mouse wheel, I move that sheet up and down. If I move to screen 2, I used to be able to use the scroll wheel and move that spreadsheet up and down without clicking on it first. Now, if I move from one screen to the other, the mouse wheel still works the screen I was last using, not the one I'm hovering over.
Seems like a petty thing, but I'm so used to going back and forth, I'm actually driving myself insane.
I have a spreadsheet open on each of my 2 screens. When I hover over screen 1, and scroll with the mouse wheel, I move that sheet up and down. If I move to screen 2, I used to be able to use the scroll wheel and move that spreadsheet up and down without clicking on it first. Now, if I move from one screen to the other, the mouse wheel still works the screen I was last using, not the one I'm hovering over.
Seems like a petty thing, but I'm so used to going back and forth, I'm actually driving myself insane.
Brony
Footballguy
You can set up VBA to run anytime that you make a change in the document. I generally don't like to leave any macro on auto-run - potential for it to slow down the workbook and you need to make sure that you are only applying this macro to the current workbook and not Excel in general. I have set-up macros to run when a drop-down value changes and that seems to go OK.
Theoretically you could set up a PivotTable to display this data - just set the state field as a Report Filter, then when a user selects the state the PivotTable would update to display only those records - you can easily set it up to display whichever fields you need to. Not as "tech savvy" as going VBA, but is one way that could work in a pinch.
Or, and probably better, if your data is set up as a table, you could set up slicers to filter the data table. Link to Microsoft article on this Another link on setting up table slicers
Or, and probably better, if your data is set up as a table, you could set up slicers to filter the data table. Link to Microsoft article on this Another link on setting up table slicers
Ned
Footballguy
TheIronSheik
SUPER ELITE UPPER TIER
This is such a small stupid thing, I'm sure I just didn't learn it when I first started using Excel, but it pisses me off constantly.
Is there a way to insert spaces into a sheet when you already (apparently) have something copied and in the chamber? Sometimes I'll be working in another tab or sheet and copy something and forget about it. Then I right click on something to Insert a new line and instead of "Insert" it says "Insert Copied Cells". I have to click on a cell, type some letter and hit Enter. Then I can just Insert. I'm fairly sure there's a better way around it, but after 20 years, I've yet to find it.
Is there a way to insert spaces into a sheet when you already (apparently) have something copied and in the chamber? Sometimes I'll be working in another tab or sheet and copy something and forget about it. Then I right click on something to Insert a new line and instead of "Insert" it says "Insert Copied Cells". I have to click on a cell, type some letter and hit Enter. Then I can just Insert. I'm fairly sure there's a better way around it, but after 20 years, I've yet to find it.
hagmania
Footballguy
TheIronSheik
SUPER ELITE UPPER TIER
beer 30
Footballguy
TheIronSheik
SUPER ELITE UPPER TIER
Yes. But probably should be in a more broad topic than just Excel.beer 30 said:
Brony
Footballguy
I should know the answer to this, but I'm stumped.
At my current job, there are some activities that involve working with big data tables stored in XLS. For example, 15 to 20 tabs with each having tables of 1,000 to 500,000 rows using 20 to 50 columns of data (file size 150+MB). Just general navigation is slow, but doing VBA on these files pulls everything to a crawl and locks up my computer for 20 minutes (best case) to permanent lock.
I could look into pushing these data tables to a DB such as SQL or Access, but I'd have to start over on a lot of processing initiatives and train newbies on these systems.
Any tips, tricks for these large files? I"ve stripped off formatting, turned calculations to manual, etc. Would more RAM help or using computer with bigger badder CPU? I'm not above parsing the tables out to multiple CSV or XLS files if that would help.
At my current job, there are some activities that involve working with big data tables stored in XLS. For example, 15 to 20 tabs with each having tables of 1,000 to 500,000 rows using 20 to 50 columns of data (file size 150+MB). Just general navigation is slow, but doing VBA on these files pulls everything to a crawl and locks up my computer for 20 minutes (best case) to permanent lock.
I could look into pushing these data tables to a DB such as SQL or Access, but I'd have to start over on a lot of processing initiatives and train newbies on these systems.
Any tips, tricks for these large files? I"ve stripped off formatting, turned calculations to manual, etc. Would more RAM help or using computer with bigger badder CPU? I'm not above parsing the tables out to multiple CSV or XLS files if that would help.
Ned
Footballguy
How old are these files? Are they growing/shrinking often? I've seen where files get out of hand where a lot of data and formatting was removed, but the file size is still huge. I've taken the data from the suspect file and moved just the raw data to a new workbook and it's helped.
I'd also look at converting the files to binary files (.xlsb). Should help a bit with sizing/processing.
Question 1:
Using the date field below, I would like to create a new field that subtracts today (current month/year) from this field's format below. There are some fields that do not have a date value ('-' or blank) - in those cases output a '-'.
Current Formula:
=1*DATEDIF(DATE(LEFT(A2,4),MID(A2,6,2),1),TODAY(),"m")
Current Formula is working, but outputs #VALUE instead of '-' when there is no value. How would I go about updating/fixing that?
Column A: Column B(Expected Value):
2019-03 04
2019-07 00
2019-06 01
2019-05 02
2019-04 03
- -
Question 2:
Using the date field below, how would I count the number of days between today and the date below? This field will have some fields with a blank value and some dates in Column C could be in the past. If dates are in the past or are missing so that the calculation is not logical, output a '-'.
Column C Column D (Expected Value):
07/22/2019 2
07/01/2019 -
07/20/2019 0
07/19/2019 -
Using the date field below, I would like to create a new field that subtracts today (current month/year) from this field's format below. There are some fields that do not have a date value ('-' or blank) - in those cases output a '-'.
Current Formula:
=1*DATEDIF(DATE(LEFT(A2,4),MID(A2,6,2),1),TODAY(),"m")
Current Formula is working, but outputs #VALUE instead of '-' when there is no value. How would I go about updating/fixing that?
Column A: Column B(Expected Value):
2019-03 04
2019-07 00
2019-06 01
2019-05 02
2019-04 03
- -
Question 2:
Using the date field below, how would I count the number of days between today and the date below? This field will have some fields with a blank value and some dates in Column C could be in the past. If dates are in the past or are missing so that the calculation is not logical, output a '-'.
Column C Column D (Expected Value):
07/22/2019 2
07/01/2019 -
07/20/2019 0
07/19/2019 -
Last edited by a moderator:
bryhamm
Footballguy
In the first part, is the field defined as text or is it a date? Your formula is pulling things as it it were a text, which might be causing issues. What are you trying to "show" in Question 1 (ie what is the goal of the answer)?
For Question 2, it seems you should be able to use an if/then statement where you are checking if the date in column C is less than today (ie in the past). If so, put a "-" in the answer. if not, use your DATEDIF formula
For Question 2, it seems you should be able to use an if/then statement where you are checking if the date in column C is less than today (ie in the past). If so, put a "-" in the answer. if not, use your DATEDIF formula
Hello,
How would I go about coding for this?
This code here gets me started, but how do I add to this:
=if(and(or(a1="A - Current",A1="A - Ret",a1=" or "A R1"),B1="N"),"K","INVALID"
If Column A = "A - Current" or "A - Ret" or "A R1"
and Column B = "N"
output a value of "K" in Column D else "INVALID"
ELSE
If Column A = "AM - A" or "AM - V"
and Column B = "N"
output a value of "D" in Column D, else "WRONG"
ELSE
If Column A = "AS - 1" or "AS - E" or "DB"
and Column B = "N"
and Column C = "As-Is (No Changes)" or "Modified - Standard" or "Modified - Sup"
output a "S" in Column D else "M", else "HELP"
ELSE
If Column A ="Trig - AM" or "Trig - DIy"
output a "J" in Column D else "RESEARCH"
ELSE
If Column A = "NONE" or "Other" or "Other-S"
and Column B = "N"
output a value of "N/A" in Column D.
END
How would I go about coding for this?
This code here gets me started, but how do I add to this:
=if(and(or(a1="A - Current",A1="A - Ret",a1=" or "A R1"),B1="N"),"K","INVALID"
If Column A = "A - Current" or "A - Ret" or "A R1"
and Column B = "N"
output a value of "K" in Column D else "INVALID"
ELSE
If Column A = "AM - A" or "AM - V"
and Column B = "N"
output a value of "D" in Column D, else "WRONG"
ELSE
If Column A = "AS - 1" or "AS - E" or "DB"
and Column B = "N"
and Column C = "As-Is (No Changes)" or "Modified - Standard" or "Modified - Sup"
output a "S" in Column D else "M", else "HELP"
ELSE
If Column A ="Trig - AM" or "Trig - DIy"
output a "J" in Column D else "RESEARCH"
ELSE
If Column A = "NONE" or "Other" or "Other-S"
and Column B = "N"
output a value of "N/A" in Column D.
END
Last edited by a moderator:
TheIronSheik
SUPER ELITE UPPER TIER
This should be an easy one. I'm sure it's something stupid and for the life of me I can't figure it out so I figured I'd just come here first instead of spending too much time troubleshooting something stupid.
I have conditional formatting that is turning two cells red for a negative result to a formula and green to a positive result to the formula. It's for all of the weeks of the year. For the weeks that haven't happened yet, it is returning a red result. I don't want any result for a blank cell. Technically, it's not blank because there's a formula in there, but why is it turning red with no result and how do I stop that? These are things I knew years ago but just don't use much anymore and the mind is going.
I have conditional formatting that is turning two cells red for a negative result to a formula and green to a positive result to the formula. It's for all of the weeks of the year. For the weeks that haven't happened yet, it is returning a red result. I don't want any result for a blank cell. Technically, it's not blank because there's a formula in there, but why is it turning red with no result and how do I stop that? These are things I knew years ago but just don't use much anymore and the mind is going.
Nathan R. Jessep
Footballguy
did you use a formula or one of the pre-set preconditional formatting rules? May just be a matter of changing it from "greater than or equal to" to "greater than" if it's just comparing the cell result value to 0.
TheIronSheik
SUPER ELITE UPPER TIER
I actually have a rule where if it's zero, it turns yellow. I put a zero in and it turned yellow. But blank it goes back to red.
Brony
Footballguy
If you are using a formula, change it to =AND(NOT(ISBLANK(A1)),<your other rule here>
SaintsInDome2006
Footballguy
Uhm, ok, I think this might be my first question here.
-> I'd like to take a long list of headings (basically 1000+ names) that runs horizontally and rather than retype that sucker I'd like to simply convert it to a typical up/down vertical list.
This must be a basic thing, anyone have a shortcut or instruction on this? Thanks.
-> I'd like to take a long list of headings (basically 1000+ names) that runs horizontally and rather than retype that sucker I'd like to simply convert it to a typical up/down vertical list.
This must be a basic thing, anyone have a shortcut or instruction on this? Thanks.
Ned
Footballguy
SaintsInDome2006
Footballguy
Titans_fan
Footballguy
I feel like an idiot for not knowing how to do this, but I keep running into a wall and now I'm turning to you for help.
What I have is Sheet 1 with First Name in column A and Last Name in column B. Columns C - H has data.
On Sheet 2, it's formatted exactly the same (First Name, Last Name, data).
I would like to combine the data from sheets 1 & 2 onto sheet 3.
The issue I'm having is there are names on sheet 1 that aren't on sheet 2 and vice versa. There are also some names on both sheets. I am looking for a solution (formula or VBA) that will:
1) If a person is on both sheets, add the data in each column (C-H) and place that person on a new row on sheet 3
2) If a person is on one sheet, but not both, copy the info onto sheet 3.
Sheet 3 will then become the "master" sheet and as new data is accumulated (yearly), I can utilize the same process to update the data.
Thanks in advance for the help! I think it's an easy fix, I've just got myself out of sorts and can't get over the hump.
What I have is Sheet 1 with First Name in column A and Last Name in column B. Columns C - H has data.
On Sheet 2, it's formatted exactly the same (First Name, Last Name, data).
I would like to combine the data from sheets 1 & 2 onto sheet 3.
The issue I'm having is there are names on sheet 1 that aren't on sheet 2 and vice versa. There are also some names on both sheets. I am looking for a solution (formula or VBA) that will:
1) If a person is on both sheets, add the data in each column (C-H) and place that person on a new row on sheet 3
2) If a person is on one sheet, but not both, copy the info onto sheet 3.
Sheet 3 will then become the "master" sheet and as new data is accumulated (yearly), I can utilize the same process to update the data.
Thanks in advance for the help! I think it's an easy fix, I've just got myself out of sorts and can't get over the hump.
Ned
Footballguy
It sounds like it should be as simple as copying all data from both sheets into sheet3 and then using the Remove Duplicates tool.
Titans_fan
Footballguy
Unfortunately, it's not.
Here's an example.
Sheet 1 has all the RBs since 2000 listed with First Name in column A and Last Name in column B. Columns C-H contain career rushing yards, rushing TDs, receiving yds, etc.
Sheet 2 has all the same info, except from just the 2018 season.
As such, sheet 1 has Barry Sanders, but sheet 2 doesn't because he didn't play in 2018. Sheet 2 has Saquon Barkley, but sheet 1 doesn't because 2018 was his first year. Both sheet 1 and sheet 2 have Adrian Peterson because he played in 2018 and earlier.
Sheet 3 should have Barry Sanders info from sheet 1 on one line because his career numbers didn't change following the 2018 season. Sheet 3 should also have Saquon Barkley's info from sheet 2 on another line because he didn't play before 2018, so his career numbers are just from 2018.
Finally, sheet 3 should add Adrian Peterson's data from columns C-H from both sheet 1 and sheet 2 because he did play in 2018 and thus his career totals have changed.
Make sense?
Last edited by a moderator:
Ned
Footballguy
Yup, that's a little different since C:H are totals. You can do it a couple of ways, but I'm going to assumed you're not interested in Pivot Tables... If you just want the raw data consolidated down into a single sheet, this should do the trick:
- copy A:B from both sheets 1 & 2 into sheet 3.
- Use Remove Duplicates tool to get unique list of player names in sheet 3.
- in C:H, use SUMIFS to get your totals back. "=SUMIFS(Sheet1!C:C,Sheet1!$A:$A,Sheet3!$A2,Sheet1!$B:$B,Sheet3!$B2)+SUMIFS(Sheet2!C:C,Sheet2!$A:$A,Sheet3!$A2,Sheet2!$B:$B,Sheet3!$B2)"
- Copy/Paste that formula across the C:H range you need in sheet 3.
- If you don't want the formulas there, you can do.... Copy/Paste Special - Values Only in C:H to remove formulas.
Titans_fan
Footballguy
Apple Jack
Footballguy
We had a third party develop a macro for a client dashboard. I need to lock hidden worksheets. Does anybody know if there is a way to do this without writing it into the macro?
SaintsInDome2006
Footballguy
Ok here's another:
- I have a series of cells with numbers in them.
So for instance, if I'm ranking RBs (each is a cell with the number):
***********
What if I want to be able to sort the listing, so I want to take the numbers out so it will just be:
***********
Elliott
McCaffery
Bell
Johnson
***********
(and then I can sort it by text).
Can I do this by formula or via menu command rather than working through it manually?
Thanks!
- I have a series of cells with numbers in them.
So for instance, if I'm ranking RBs (each is a cell with the number):
***********
- Elliott
- McCaffery
- Bell
- Johnson
What if I want to be able to sort the listing, so I want to take the numbers out so it will just be:
***********
Elliott
McCaffery
Bell
Johnson
***********
(and then I can sort it by text).
Can I do this by formula or via menu command rather than working through it manually?
Thanks!
Last edited by a moderator:
Ned
Footballguy
Just so I'm understanding - your cell is "1. Elliott"? Or is it cell A1 is "1." and cell B1 is "Elliott"?
SaintsInDome2006
Footballguy
Ned
Footballguy
SaintsInDome2006
Footballguy
Ned
Footballguy
I've never needed to do that - I thought 'center across selection' would do the trick (after selecting a few adjacent cells below), but it won't recognize the vertical selection.
Buckychudd
Footballguy
I'm not aware of that ability, but it's pretty easy to auto change the height of a cell to fit. Just highlight the range and double click on one of the row lines in the axis.
BroadwayG
Footballguy
Am I misunderstanding, or isn't this what the 'Wrap Text' button is for?
Apparently I am misunderstanding. Sounds like something you'd use a Text Box for to escape the rules of cells.
Last edited by a moderator:
Pigskin Fanatic
Footballguy
you can do this across columns, but afaik you can't do this across rows. to do this across columns, select the two (or more columns) of cells, format cells..., and under horizontal alignment select "Center Across Selection".
TheIronSheik
SUPER ELITE UPPER TIER
Big Text's wife has a stalker.Walking Boot said:
Nick Vermeil
Footballguy
Is there a way to set permissions on a spreadsheet that will allow people to input data but not change the structure of the sheet or edit formulas?
