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 (1 Viewer)

Ned said:
I've never seen or used Report Manager - I'm assuming its an Add-in.  You could do a mail merge in Word where Word will pick up your spreadsheet as the data source.

https://support.microsoft.com/en-us/kb/294683
Click to expand...
This would be my suggestion as well. I primarily have used mail merge for creating mailing labels, but I don't see why you couldn't create a 4x6 layout with the fields you need and essentially get the same thing you are looking for.

The non-Excel way would be to use Access to do this, but that would be a bit more complicated than I think you need.

 
Last edited by a moderator:
Walking Boot said:
Just checking, there's no way to return a Null value with an IF formula, right? I can't use " " because it gives me a #VALUE error in a formula elsewhere, and if I use 0, it looks awful because the format for the cell is a date, so it shows 1/1/1904 instead of 0 or nothing. Ideally, I want it to return as if it's an empty cell.
Click to expand...
That's basically a circular issue if you think about it - you're asking it to delete itself.

In your other formula that's referencing this cell, why not add an IF statement to check if it's ""?  Otherwise, you'd need to write a quick macro to wipe out your "" or 0.

=IF(I10="","yup","nope")

 
Walking Boot said:
Just checking, there's no way to return a Null value with an IF formula, right? I can't use " " because it gives me a #VALUE error in a formula elsewhere, and if I use 0, it looks awful because the format for the cell is a date, so it shows 1/1/1904 instead of 0 or nothing. Ideally, I want it to return as if it's an empty cell.
Click to expand...


Instead of " " just put in ""    So the formula reads =IF(expression you are checking, "return this if true, "")     If the expression works out to be not true, you get a blank cell returned.

If that does not work, or if I am misunderstanding the question, perhaps you could use the ISERROR() function of the ISBLANK(). I use this a lot, especially with VLOOKUP or MATCH.

Basically the formula structures as follows  

=IF(ISERROR(Your statement to check),"Value to return if it is an error", "Value to return if it is not"))

if checking for blank cells, then use ISBLANK in lieu of ISERROR

 
Walking Boot said:
Yeah, that could do it, except other people in the office use this spreadsheet and they won't understand it. :kicksrock:

Trying to "automate" as much as possible for the simpletons I work with.

Also tried using INDEX(MATCH()) and that also returns a '0' if the cell it's looking for is blank, which is disappointing. I was hoping to find some kind of formula where I could get an empty value that wouldn't give me #VALUE all over the place.

I think I'm just going to go back to doing it the manual way, which introduces more possibility for error, but, is also paradoxically more idiot-proof.
Click to expand...
Not exactly sure what you doing, but I think you could use an IFERROR() in those cells that you are getting a #VALUE in.  If the formula produces an error you can have it gracefully display nothign with IFERROR(FORMULA,"")

https://exceljet.net/excel-functions/excel-iferror-function

 
Walking Boot said:
Sadly, the cell I want to gracefully display nothing isn't the one I'm getting the #VALUE error in. It's in a range of cells elsewhere, which the cell that contains #VALUE is looking at.

Thanks everyone for trying, but I think this is one of those things that excel just can't do neatly.
Click to expand...
If you wrap an IFERROR() around any #VALUE error cell it will not display a "#VALUE" if that's what you are trying to do.

 
Ned said:
I've never seen or used Report Manager - I'm assuming its an Add-in.  You could do a mail merge in Word where Word will pick up your spreadsheet as the data source.

https://support.microsoft.com/en-us/kb/294683
Click to expand...
Alright, I am on the right track. My one issue now is when I merge my excel report into a label on Word, the date gets jumbled. On Excel it appears as 7/11/16 and then when it merges into Word it turns into stuff that makes no sense, like this...42562. I googled a bunch of stuff and found the issues but have not had success in getting it solved. I am on Office 365. Any ideas what I need to do?

 
Last edited by a moderator:
Beef Ravioli said:
Alright, I am on the right track. My one issue now is when I merge my excel report into a label on Word, the date gets jumbled. On Excel it appears as 7/11/16 and then when it merges into Word it turns into stuff that makes no sense, like this...42562. I googled a bunch of stuff and found the issues but have not had success in getting it done. I am on Office 365. Any ideas what I need to do?
Click to expand...
Need to add a date format to the mergefield in Word.. a la {MERGEFIELD MyDate \@ "dd/MMM/yyyy"}

 
Walking Boot said:
Thanks, but that won't do the trick either. Doesn't matter if I use " " or "", I still get a #VALUE error in a different formula that references this range of cells. Even though the cell itself looks blank, it's actually a data type that throws an error in the second formula (it's WORKDAY, btw, and I'm trying to get a range of cells that list the holidays it should skip based on what country is entered in a cell elsewhere. Like, type "US" in A1, and it'll show a list of US holiday dates in the range on another part of the worksheet. Type "CANADA" and it'll list the Canadian holidays. Etc). The thing I don't like is that if I use "" or " ", WORKDAY can't work with that. I could use 0, or, just nothing, like: =IF(TRUE, 1, ) which WORKDAY is OK with, but that displays '1/1/1904' in the cell and that looks really really ugly and will confuse a lot of my coworkers.

The whole thing works except only with 1/1/1904 all over the place. :kicksrock:
Click to expand...
If you build the If statement to return something such as "Not a Holiday" instead of the null value, will your WORKDAY formula work correctly?

Another work around would be to conditionally format the row that your IF statement is in so that if it returns 1/1/1904 (which I believe is the 0 value for date fields), the font is the same color as the cell fill so it appears blank, thus not displaying a value that confuses your coworkers.

Did some googling and it appears there is no way for Excel to return a truly empty cell as the result of a formula, as it does have the formula in there.

 
Beef Ravioli said:
Alright, I am on the right track. My one issue now is when I merge my excel report into a label on Word, the date gets jumbled. On Excel it appears as 7/11/16 and then when it merges into Word it turns into stuff that makes no sense, like this...42562. I googled a bunch of stuff and found the issues but have not had success in getting it done. I am on Office 365. Any ideas what I need to do?
Click to expand...
Make sure that your date fields are formatted as dates in Excel. If they aren't, Word can have some trouble picking up that they are dates and will display them as numbers. See if you are lucky and the solution is as simple as that.

 
acarey50 said:
Make sure that your date fields are formatted as dates in Excel. If they aren't, Word can have some trouble picking up that they are dates and will display them as numbers. See if you are lucky and the solution is as simple as that.
Click to expand...
Is that a fairly simple process?

 
Walking Boot said:
To the first question, no. Having it return words screws up the WORKDAY() function.

I figured out a workaround, though. I can get it to display "0" in a cell, instead of 1/1/1904, which is good enough. I forced it using TEXT(). Specifically,

=IF(TRUE,C1,TEXT(0,"h"))

So now, if the evaluation is true, it displays the date saved in C1 in the cell. If the evaluation is false, it displays the hour of 1/1/1904, which is 0. Quite an odd kludge, but it'll work for now.

My coworkers will never understand that part of the formula, but at least it displays ok. Now I have a column full of either dates or 0s, so it's easy to tell at a glance which are the holidays (the actual dates) and which aren't (the zeroes).
Click to expand...
You can format the date cells as a date and display a zero value as blank at the same time by creating a custom number format.  Under format cells, select the Date format that you want to use (say "m/d/yyyy") and then choose Custom and edit the format by adding two semi-colons at the end ("m/d/yyyy;;")  What this will do is display a date for positive values and display a blank cell for negative and zero values.

 
Walking Boot said:
SantaRosaUte said:
You can format the date cells as a date and display a zero value as blank at the same time by creating a custom number format.  Under format cells, select the Date format that you want to use (say "m/d/yyyy") and then choose Custom and edit the format by adding two semi-colons at the end ("m/d/yyyy;;")  What this will do is display a date for positive values and display a blank cell for negative and zero values.
Click to expand...
This works even better, thanks! The 0s aren't displayed as 1/1/1904, and hiding them doesn't mess up the WORKDAY function. Neat trick!
Click to expand...
:goodposting:  

Props for this, @SantaRosaUte.  I didn't know this one!

 
Google docs question:

2 Worksheets:  Draft Board & Player Selection

=filter('Player Selection'!C1:D303,ISERROR('Player Selection'!A1:A303))

Column A on Draft Board has this filter.  It is going to look at the Player Selection sheet and only show those that have an error (not selected).

=VLOOKUP(C1,'Draft Board'!D3:V24,2,0)

I have also tried

=VLOOKUP(C1,'Draft Board'!D3:D24,'Draft Board'!F3:F24,'Draft Board'!H3:H24,'Draft Board'!J3:J24,'Draft Board'!L3:L24,'Draft Board'!N3:N24,'Draft Board'!P3:P24,'Draft Board'!R3:R24,'Draft Board'!T3:T24,'Draft Board'!V3:V24,2,0)

This is supposed to lookup anyone on the draft board that has been selected and take them away from column A on the draft board.

Example-The draft board will show players left to be drafted.

Abbrederis,Jared|GBP|WR|

If I draft him or any player in column D then he is no longer shown in A, which is what I want.  If I use any of the other columns the player stays.

Can anyone tell me what I am missing?

 
LAUNCH said:
https://docs.google.com/spreadsheets/d/1RNkbpONsb7oClXGkuWH_YgYj0jXJ3pUpcQh1eh5dS5I/edit?usp=sharing
Click to expand...
Col A formula on the Draft Board......  =filter('Player Selection'!C1:C303,'Player Selection'!A1:A303="AVAILABLE")

Col A formula on the Player Selection....... =if(ISERROR(match(C2,'Draft Board'!D:D,0)),if(ISERROR(match(C2,'Draft Board'!F:F,0)),if(ISERROR(match(C2,'Draft Board'!H:H,0)),if(ISERROR(match(C2,'Draft Board'!J:J,0)),"AVAILABLE","DRAFTED"),"DRAFTED"),"DRAFTED"),"DRAFTED")

 
LAUNCH said:
That's awesome.  Thank you so much!
Click to expand...
No problem.  Just a note though... you'll need to expand that out to the number of columns you want it to test.  I only went to J since I wasn't sure how deep you were going.

 
I have a spreadsheet that I use to balance transactions on a daily basis.  Everyday I search for the word "core".  Is there a formula or button I can add to just click on to search for this word instead of the short cut of Cntl F and then typing in "Core"?

 
dino259 said:
I have a spreadsheet that I use to balance transactions on a daily basis.  Everyday I search for the word "core".  Is there a formula or button I can add to just click on to search for this word instead of the short cut of Cntl F and then typing in "Core"?
Click to expand...
What are you doing after you find "core"?

 
Ned said:
What are you doing after you find "core"?
Click to expand...
It is a long list of transactions that I can delete.  Ideally I wouldn't bring them into excel in the first place but that software does not let me exclude that tran code.

 
dino259 said:
It is a long list of transactions that I can delete.  Ideally I wouldn't bring them into excel in the first place but that software does not let me exclude that tran code.
Click to expand...
Turn on filters and then filter on that trans code.  Highlight all of the rows and delete entire rows.

 
asking for my wife, I have no idea about excel....

she says something like when she does a vlook up, some values come up as #N/A.  she doesn't want this value and wants 0 or null or something like that, but not #N/A.

any advise?

thx

@Ned

 
Last edited by a moderator:
Chemical X said:
can you elaborate?  where does this go?
Click to expand...
She needs to put her VLOOKUP formula where @Ignoramus has the FORMULA noted.  This will return a 0 whenever her VLOOKUP returns an error.

One minor thing - she may need to exclude the quotes around the zero if she wants to be able to sum the column this formula will sit in.  Excel may treat that "0" as text instead of a number. 

 
Ned said:
She needs to put her VLOOKUP formula where @Ignoramus has the FORMULA noted.  This will return a 0 whenever her VLOOKUP returns an error.

One minor thing - she may need to exclude the quotes around the zero if she wants to be able to sum the column this formula will sit in.  Excel may treat that "0" as text instead of a number. 
Click to expand...
ahhhhh, now iUnderstand

 
acarey50 said:
Ned said:
I've never seen or used Report Manager - I'm assuming its an Add-in.  You could do a mail merge in Word where Word will pick up your spreadsheet as the data source.

https://support.micro

soft.com/en-us/kb/294683
Click to expand...
This would be my suggestion as well. I primarily have used mail merge for creating mailing labels, but I don't see why you couldn't create a 4x6 layout with the fields you need and essentially get the same thing you are looking for.

The non-Excel way would be to use Access to do this, but that would be a bit more complicated than I think you need.
Click to expand...
I know you have started, but I would look at Access for something like version 2.0. Once you figure out what you need, you can set up the system in Access in a way that you can pass it along to a less techie person. It is easy to set up so that people are inputing the data into easy to recognize and deal-with forms, with buttons to perform functions, instead of spreadsheets in Excel.

:2cents:

 
anyone help with a formula for this?

progressive scale for commission.  let's say there is a 200k commission. 

the first 5k is 100%  so 5k

5001-10k is 80%     so 4k

10,001-30k is 70%  so 14k

30,001-100k is 60%  so 42k

100,001+ is 50%  so 50k

how to write an excel formula to calculate this for different amounts would win user a prize!

@Ned

 
Walking Boot said:
You mean, "let's say there's a 200k sale", right? So the commission on that is 5k+4k+14k+42K+50K = 155k?

If the sale price is in A1, I think the formula is:

=IF(A1>100000, ((A1-100000)*0.5)+65000, IF(A1>30000, ((A1-30000)*0.6)+23000, IF(A1>10000, ((A1-10000)*0.7)+9000, IF(A1>5000, ((A1-5000)*0.8)+5000, A1)))

Basically, if A1 is in the top range, subtract 100k, take 50% of what's left, then add the previous commissions (65k) back in. Otherwise, repeat down the line.
Click to expand...
yes and no....

logic seems right, but maff is wrong.........I get 115k, not 155k

I tried writing the formula, but I get errors since I have no clue how to write this.

 
Walking Boot said:
There's probably an easier way to do it, I know I've seen a tax withholding calculator that breaks it down by bracket, just uses more cells. It's a bit wonky but it'll all fit in one cell.
Click to expand...
There's ways to do it where you can set up a table with the ranges and percentages, so that if these things change over time or are different by rep you can more easily change/customize them, but in a pinch the nested IF formula should work.

 
This started happening yesterday with my Excel 2010.

I try copying data from one excel saved document to another opened excel document and I get a pop up box reading

Microsoft Excel cannot paste the data

But I can copy and paste it within the original document.

Text, numbers whatever it won't work.

Also trying to change cell formats I'm getting a pop up box reading

Too many different cell formats

But if I open a excel sheet and type data into a cell I can change cell formats as many times as I want and don't get the error.

I can also copy and paste data from a previously saved Excel sheet into a newly opened Excel sheet and do not get those errors. 

When I save the sheet and go back to it the error problem returns in that sheet as well.

But I can paste by right clicking and using Paste Option Match Destination Formatting (M), but the Too many different cell formats problem remains.

My default save type for excel sheets is save as Excel Workbook.

 
Village Idiot said:
This started happening yesterday with my Excel 2010.

I try copying data from one excel saved document to another opened excel document and I get a pop up box reading

Microsoft Excel cannot paste the data

But I can copy and paste it within the original document.

Text, numbers whatever it won't work.

Also trying to change cell formats I'm getting a pop up box reading

Too many different cell formats

But if I open a excel sheet and type data into a cell I can change cell formats as many times as I want and don't get the error.

I can also copy and paste data from a previously saved Excel sheet into a newly opened Excel sheet and do not get those errors. 

When I save the sheet and go back to it the error problem returns in that sheet as well.

But I can paste by right clicking and using Paste Option Match Destination Formatting (M), but the Too many different cell formats problem remains.

My default save type for excel sheets is save as Excel Workbook.
Click to expand...
Have you completely shut down Excel recently?  I've never seen these errors before, but they both sound to me like you're exhausting resources.  When this happens to me (lack of resources), I have to shut down/restart Excel.

Is it a big range of data?

 
Ned said:
Have you completely shut down Excel recently?  I've never seen these errors before, but they both sound to me like you're exhausting resources.  When this happens to me (lack of resources), I have to shut down/restart Excel.

Is it a big range of data?
Click to expand...
I closed everything and rebooted my computer yesterday when I saw this issue and it didn't help.

I've tried it with as few as 1 cell of data containing the word No and got the too many formats pop up.

The baffling part is I have never had this issue before no matter how many excel sheets I had open at once. And I've had as many as 6 open at once.

I'll try closing and reopening Excel again.

Thanks,.

 
Wow.

I had 2 excel sheets open, closed them both and reopened the one I ad the one cell Too many cell formats error in.

It let me change that cell format after I reopened it. 

I opened the other sheet I had open at the time and it still is letting me have my way with cell formats.

It's an inconvenience but if I have to do it to keep doing what I'm doing oh well.

 
Village Idiot said:
Wow.

I had 2 excel sheets open, closed them both and reopened the one I ad the one cell Too many cell formats error in.

It let me change that cell format after I reopened it. 

I opened the other sheet I had open at the time and it still is letting me have my way with cell formats.

It's an inconvenience but if I have to do it to keep doing what I'm doing oh well.
Click to expand...
Go to Windows Explorer and check the size.  I'm wondering if the workbook size is huge.  How involved is the sheet?  Would it be easy to copy/paste it to a new workbook and save the new one going forward?

 
Ned said:
Go to Windows Explorer and check the size.  I'm wondering if the workbook size is huge.  How involved is the sheet?  Would it be easy to copy/paste it to a new workbook and save the new one going forward?
Click to expand...
It's only 160 KB in size and I've tried copying the other sheets I was having this same issue with into a new sheet and it won't let me paste unless I do a paste with destination formatting, and even then sometimes it was cannot paste.

But still pastes it.

Or if I do a paste as a CSV file it works, then I have to completely re format it.

And all of these have no links anywhere else or any massive formulas in them that take a long time to update if changes are made.

I have far larger excel sheets I've created that have a lot of moving parts and macros that still work fine when I use them by pasting in data from word or a SAS generated e-mail containing data, and firing off a macro I've created to do an update.

 
Last edited by a moderator:
Village Idiot said:
It's only 160 KB in size and I've tried copying the other sheets I was having this same issue with into a new sheet and it won't let me paste unless I do a paste with destination formatting, and even then sometimes it was cannot paste.

But still pastes it.

Or if I do a paste as a CSV file it works, then I have to completely re format it.

And all of these have no links anywhere else or any massive formulas in them that take a long time to update if changes are made.

I have far larger excel sheets I've created that have a lot of moving parts and macros that still work fine when I use them by pasting in data from word or a SAS generated e-mail containing data, and firing off a macro I've created to do an update.
Click to expand...
Have you looked at this KB article?

 
I am setting up the spreadsheet for a Music Draft, and I have it set up 95%. I am having issues with creating sorted spreadsheets (e.g. Category, Drafter, Artist, etc.) based on the main sheet.

1) I am using this formula to grab the data from the main sheet:

=IF(ISBLANK(DRAFT!E2),"",+DRAFT!E2) - there isn't a space between the quotes.
Click to expand...

but when I sort the blank cells come first. From what I have read briefly, the blank fields were supposed to come last. (although this might be when performed on a list of numbers)

2) When I try to sort based on the Categories (in the main sheet I have set up a Validation/Pull Down list from a separate sheet for the Categories to make Category selection easier) the blank cells come in the middle of the list. (I have also setup a custom sort list based upon the Category list).

Any thoughts?

TIA!

 
Matthias said:
Are you sorting the formulas? Or turning your entries into hard coded names?

Here's what I'd do. 

#1: use vlookup. then transform all of the variable row references to fixed. 

#2: do a simple if function that if the cell is empty, 999. Else, 1.

#3: sort by your 999 column first and then your band or whatever second. 
Click to expand...
As far as the sorting, I am using the 'Sort' function from the menu. I think I'm sorting the cells. ???

In number 1, do I transform the references using $s in the formula?

OK, I understand the #2 and #3. That's clever!

Now the next silly question is all of this will work on Google Sheets? I need to put this online for everyone to look at/add their picks.

I don't think I can do a custom sort on Google Sheets.

Thanks for the quick response!!

 
Matthias said:
Here you go. I figured it out.

https://docs.google.com/spreadsheets/d/1UkQsrasHvk6bo6b8qypn-3350vHk5OC1bBx-c4r40Sg/edit?usp=sharing

In column D, you have if something is there, it's the # above +1. If it's empty, it's just the # above. Then do a vlookup for the #s going down the list and anything that's missing just will be skipped.
Click to expand...
Thanks! I will have to dig into this a bit. I got a couple Excel books at the library yesterday. I'll check them for vLookup.

I will put in v0.1 online for the draft and work on the sorted sheets.

Thanks again for your help.

 
Hello,

I have 2 date fields:

Column I8: 10/10

Column I9: 10/17

In Column I10, I want to subtract the difference in dates (I9 - I8), including only Business Days, and return a value of 5.

How do I go about doing that?

 
Last edited by a moderator:
You must log in or register to reply here.

Similar threads

ragincajun
Excel AI!!!!
Replies
26
Views
987
culdeus
culdeus
L
Work Efficiency & Organization
Replies
15
Views
554
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: 2 (members: 0, guests: 2)
Top