Fantasy Football - Footballguys Forums
Ned

***Official*** Excel Help Corner

Recommended Posts

6 minutes ago, Spin said:

Iferror(vlookup,value you want to return if vlookup fails)

Can you explain this a little further?  

I get this far:

=IFERROR(VLOOKUP(B:B,'Case data'!A:K,1,FALSE)

But then get stuck on the return value portion.

 

Also to note, the rows between sheet A and sheet B are not in an exact order so the unique number from sheet a, row 1 may match the unique number from sheet b, row 15.

Share this post


Link to post
Share on other sites

You have to add the default to that.  =IFERROR(VLOOKUP(B:B,'Case data'!A:K,1,FALSE) , Default)

Well, whatever value you want, not the "default word"  Let me set up a mock excel sheet and show you, give me a sec.

Edited by Spin

Share this post


Link to post
Share on other sites
2 minutes ago, Spin said:

You have to add the default to that.  =IFERROR(VLOOKUP(B:B,'sheet1'!A:K,1,FALSE) , Default)

Well, whatever value you want, not the "default word"  Let me set up a mock excel sheet and show you, give me a sec.

If it matches I'd want the returned value from the vlookup (in the example, "Sheet1, column A").  If not, "Sheet2, column B".

Share this post


Link to post
Share on other sites

=IFERROR(VLOOKUP(A1,Sheet1!A:H,2,FALSE),VLOOKUP(A1,Sheet2!A:B,2,FALSE))
 

Where is the value that you're trying to match, as in, where does this formula sit? Your vlookup will never work, because you're trying to find every value in B, you gotta give it one value to find.

Share this post


Link to post
Share on other sites

Did not read the whole thread but I will if you think Excel is the right program for me.

I know very little about the program but I want to develop a way to track visitors that come to our church.  A program to store their contact information so we can send them information down the line. We would want to be able to add to it each time a new individual/family visits. Is Excel my program?

There are church programs for that kind of stuff but we are a small church with a small budget, so we would prefer to not purchase an additional program if not necessary. 

Thanks in advance! 

Share this post


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

Did not read the whole thread but I will if you think Excel is the right program for me.

I know very little about the program but I want to develop a way to track visitors that come to our church.  A program to store their contact information so we can send them information down the line. We would want to be able to add to it each time a new individual/family visits. Is Excel my program?

There are church programs for that kind of stuff but we are a small church with a small budget, so we would prefer to not purchase an additional program if not necessary. 

Thanks in advance! 

It can easily be used to do this. Just set up a basic Excel sheet with the fields you want, likely: Name, Street Address, City, State, Zip Code - plus anything else you may want such as date of visit or any comments you may want regarding them.

From that setup you can easily create mailing labels in Word if you are so inclined, or have access to it for handwriting mailing addresses. Also, by having it in Excel, you can easily filter the information if you want to send targeted information - ie just to visitors from a particular city or zip code, etc.

Share this post


Link to post
Share on other sites

Beef Ravioli - what you need to do is called CRM (Customer Relationship Management).  There are free web CRM apps out there that will probably work for you.  I don't know a lot about this, so I can't recommend one.

Share this post


Link to post
Share on other sites
3 hours ago, acarey50 said:

It can easily be used to do this. Just set up a basic Excel sheet with the fields you want, likely: Name, Street Address, City, State, Zip Code - plus anything else you may want such as date of visit or any comments you may want regarding them.

From that setup you can easily create mailing labels in Word if you are so inclined, or have access to it for handwriting mailing addresses. Also, by having it in Excel, you can easily filter the information if you want to send targeted information - ie just to visitors from a particular city or zip code, etc.

Thanks! 

Share this post


Link to post
Share on other sites
3 hours ago, Thorpe said:

Beef Ravioli - what you need to do is called CRM (Customer Relationship Management).  There are free web CRM apps out there that will probably work for you.  I don't know a lot about this, so I can't recommend one.

Thanks! I will check it out. 

Share this post


Link to post
Share on other sites

@Kanil

18 hours ago, Kanil said:

Need some help.  

  • I'm trying to compare data from two sheets.
  • Sheet A has about ten columns with column one being a unique number and the rest of the columns being data
    • Example
      •        A          |  B  |  C  |  D  |  E  |  F  |  G  |  H  |  I  |  J  |
      • 123456789 |  q  |  e   |  r   |  4  |  6  |  i    |  F   |  c |  p  |
  • Sheet B has two columns, column one could contain the unique number in sheet A, column two being other data.
    • Rows that have an SSN will not have any info in the data column and rows that have a data will not have any info in the SSN column
      • Example:
      •         A          | B |
      • 123456789  |    |
      •                     | 3 |

What I need is:

If the value in sheet2 column A exists in sheet1 column A, return sheet 1 column B.  If NOT, return sheet2, column B.

  • Example return:
    • 123456789  |  q  |  e   |  r   |  4  |  6  |  i    |  F   |  c |  p  |
    •                     |  3  |

 

Essentially, I need to build an if/then into a vlookup and i'm too dumb to do it.

Where are you actually placing this formula?  I'm assuming you're putting it in Sheet2 Column C??  Otherwise, I agree with @Spin since you have to have a key to lookup.  The below formula assumes you're putting this in column C of Sheet2.  If not, we need more info!

=IFERROR(VLOOKUP(Sheet2!A2,Sheet1!A:K,2,FALSE),B2)

Edited by Ned

Share this post


Link to post
Share on other sites
On July 20, 2016 at 1:58 PM, Kanil said:

Can you explain this a little further?  

I get this far:

=IFERROR(VLOOKUP(B:B,'Case data'!A:K,1,FALSE)

But then get stuck on the return value portion.

 

Also to note, the rows between sheet A and sheet B are not in an exact order so the unique number from sheet a, row 1 may match the unique number from sheet b, row 15.

Are they sorted on sheet A? I believe with vlookup you'll have a problem if they aren't.

Share this post


Link to post
Share on other sites
On 7/20/2016 at 5:17 PM, Beef Ravioli said:

Did not read the whole thread but I will if you think Excel is the right program for me.

I know very little about the program but I want to develop a way to track visitors that come to our church.  A program to store their contact information so we can send them information down the line. We would want to be able to add to it each time a new individual/family visits. Is Excel my program?

There are church programs for that kind of stuff but we are a small church with a small budget, so we would prefer to not purchase an additional program if not necessary. 

Thanks in advance! 

Alright, I have started working on my "prospect list". I have headings as follows:

Last Name, First Name, Age/Grade, Address, Zip Code, Phone, Email, Date of Church Visit, Date of Followup Visit, Persons Making Visit, Comments, Interest Level of Prospect

Right now I have entered 22 of 100ish names and addresses, etc. I want to be able to print off individual prospects without printing out the whole sheet. I also want it in a format that does not look like a spreadsheet. If possible, I would like this to be able to be printed out on prospect cards (perhaps a 4x6).  Is this possible?

After googling, it says I should have a "Report Manager" under "View" with ways to print reports. I don't have that or I cannot find it. 

Thanks!

Share this post


Link to post
Share on other sites
10 minutes ago, Beef Ravioli said:

Alright, I have started working on my "prospect list". I have headings as follows:

Last Name, First Name, Age/Grade, Address, Zip Code, Phone, Email, Date of Church Visit, Date of Followup Visit, Persons Making Visit, Comments, Interest Level of Prospect

Right now I have entered 22 of 100ish names and addresses, etc. I want to be able to print off individual prospects without printing out the whole sheet. I also want it in a format that does not look like a spreadsheet. If possible, I would like this to be able to be printed out on prospect cards (perhaps a 4x6).  Is this possible?

After googling, it says I should have a "Report Manager" under "View" with ways to print reports. I don't have that or I cannot find it. 

Thanks!

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

Share this post


Link to post
Share on other sites
21 minutes ago, 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

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.

Edited by acarey50

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
15 minutes ago, 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.

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")

Share this post


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

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")

 

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.

Share this post


Link to post
Share on other sites
1 hour ago, 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.

 

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

Share this post


Link to post
Share on other sites

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:

Share this post


Link to post
Share on other sites
1 hour ago, 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.

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

Share this post


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

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

 

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.

Edited by Walking Boot

Share this post


Link to post
Share on other sites
1 minute ago, 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.

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.

Share this post


Link to post
Share on other sites
6 hours ago, 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

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?

Edited by Beef Ravioli

Share this post


Link to post
Share on other sites
3 minutes ago, 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?

Need to add a date format to the mergefield in Word.. a la {MERGEFIELD MyDate \@ "dd/MMM/yyyy"}

Share this post


Link to post
Share on other sites
47 minutes ago, 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:

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.

Share this post


Link to post
Share on other sites
18 minutes ago, 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?

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.

Share this post


Link to post
Share on other sites
2 minutes ago, acarey50 said:

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.

 

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

Share this post


Link to post
Share on other sites
13 minutes ago, 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.

Is that a fairly simple process?

Share this post


Link to post
Share on other sites
30 minutes ago, Beef Ravioli said:

Is that a fairly simple process?

Should just have to highlight the cells, right click, choose Format Cells..., then in the Number tab, select Date. Use whichever type you want (M/D/YYYY, or M/D/YY, or whichever displays the way you want).

Share this post


Link to post
Share on other sites
54 minutes ago, 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).

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.

  • Like 2

Share this post


Link to post
Share on other sites
16 minutes ago, Walking Boot said:

Should just have to highlight the cells, right click, choose Format Cells..., then in the Number tab, select Date. Use whichever type you want (M/D/YYYY, or M/D/YY, or whichever displays the way you want).

Thanks! That sounds easy enough.

Share this post


Link to post
Share on other sites
4 minutes ago, 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.

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!

 

Share this post


Link to post
Share on other sites
10 hours ago, Walking Boot said:
10 hours ago, 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.

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!

 

:goodposting: 

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

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites
19 minutes ago, LAUNCH said:

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")

  • Like 1

Share this post


Link to post
Share on other sites
2 minutes ago, LAUNCH said:

That's awesome.  Thank you so much!

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.

Share this post


Link to post
Share on other sites

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"?

 

Share this post


Link to post
Share on other sites
21 minutes ago, 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"?

 

What are you doing after you find "core"?

Share this post


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

What are you doing after you find "core"?

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.

Share this post


Link to post
Share on other sites
35 minutes ago, 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.

Turn on filters and then filter on that trans code.  Highlight all of the rows and delete entire rows.

Share this post


Link to post
Share on other sites

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

Edited by Chemical X

Share this post


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

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

=IFERROR(FORMULA,"0")

  • Like 1

Share this post


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

=IFERROR(FORMULA,"0")

can you elaborate?  where does this go?

Share this post


Link to post
Share on other sites
11 hours ago, Chemical X said:

can you elaborate?  where does this go?

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. 

  • Like 1

Share this post


Link to post
Share on other sites
1 hour ago, 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. 

ahhhhh, now iUnderstand

Share this post


Link to post
Share on other sites
On August 4, 2016 at 0:49 PM, acarey50 said:
On August 4, 2016 at 0:28 PM, 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

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.

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:

Share this post


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.