This is my file - let me know if you have received it and then I will explain more.
The sheet I am looking at is 9Pre (1st sheet) the data is stored on sheet PrePost9. If you look at cells K13 or N13 or P13 you can see that they are all 0s which is not what I need - row13 is pulling in 0s and i would prefer "Z" and row 14 is pulling in a blank cell but recording a 0 and I would like it to be 'blank'. Two parts to my question.
It seems to work GREAT!!!!! I will continue to play with it and see if it works for everything!!!
Thanks for your help!!!
OK on further review this is not working - I tried it out and it appears as you drag down the formula it actually modifies good data and changes them to blank - the cell references are OFF between the two sheets as it is referencing different people.
Is there a way to take your IF(Prepost9!H3=0,"" etc. and place it at the end of the IFERROR portion and have it change a '0' to a blank after it is already in the 1st sheet????
Also I was hoping that a '0' could be a "Z" or something like that instead of a blank.
Also The workbook that I sent you was macro free whereas my actual workbook has macros - that should not affect the way this works right?
OK this works GREAT - but I have one final question and it may be too difficult????
If the data sheet (PrePost9) has a blank it returns a '0' and your formula changes it to a blank, which is perfect.
Now if the data sheet (PrePost9) has a '0' it returns a '0' and your formula changes it also to a blank which is not what I want.
is there a way to modify your formula so that if the data sheet has a blank your formula changes it to a blank which it does now BUT at the same time if the data sheet has a '0' can your formula change the '0' into a "Z" for example?
I am a teacher and we need to know when a student receives a '0' on the data sheet but in the main sheet (9Pre) I cannot have the '0' affect all the results so if I can have a 'Z' I know the student scored '0' but it does not affect the results.
I hope this makes sense - as it is right now it is MUCH BETTER but not perfect.
I am SO SO SORRY to ask this again but I just do not see this working even though you added another branch to the IF statement.
Jose (row13) has zeros for scores and your formula returns a blank and not a letter such as "Z"
Karly (row14) has a blank for her scores and your formula returns a blank which is perfect.
I tried putting in a 'Z' and I got that to appear but then the blank was also a 'Z' - what I really need is '0' to be 'Z' and blank to be blank.
You have already earned a 'tip' but if you could get this to work I would be grateful.
Hi Jess, did you get my last response?? What do you think, I really cannot proceed with my project until I have some kind of answer.
OK I went away for a while and now I tried duplicating what you did for the Mile into Push Ups and Sit Ups and then dragged the formula down expecting not to see any zeros at all and yet there are quite a few - so something is still not quite right - I know it is late so maybe we can look at this tomorrow?
OK I will try one more time to explain what is happening - first of all you have explained the situation perfectly when you wrote the following statement:
The cure here is a nested IF. If the vlookup result is 0, it will display Z, if not, then another IF takes place. If the vlookup formula results to BLANK, then display it as blank. Otherwise, display the vlookup formula results.
The problem is it does not work - I tried exactly what you wrote but it displays everything as a 'Z'. So all of the blanks are coming out as 'Z' and NOT BLANK.
The blanks that you see are for students who have no scores on the data sheet (prepost9).
Simply look at the Jose (row13) who has 0s in the data sheet and your formula returns a 'Z' - perfect! Now look at Karly (row14) she has a blank and your formula returns a 'Z' as well.
It certainly appears that your first IF statement works for '0' and blanks as the second IF statement is not working at all.
I have tried your formula in all of my columns and it just does not work - you are so close - please look at the two students in question and you will see what I am talking about.
Please let me know if you are still able to work on this for me today or should I ask another person?
When I downloaded your most recent file - revision 4 - Column L is my original formula - your new formula is in column K - and when I drag your formula down (K12) it assigns a 'Z' to both Jose (K13) and to Karly (K14). Karly has a blank for this data point in sheet prepost9 and therefore should have a blank cell in K14 and not a 'Z'.
I am so sorry for this but I think I am correct in what I am saying and looking at the file you sent.
I am a school teacher and we have an assessment program that scans all the fitness data in for our students and then I export that scanned data into an excel sheet which is my prepost9 results - cells C271-C277 is just what was exported apparently the student did not put their name in correctly.
I then have ALL the enrolled students entered in sheet pre9 so there is not a one to one relationship between the two sheets.
If a student is absent or misses one of the tests the scanned sheet records a '0' or sometimes a decimal point for the Mile and we do not want absent students or a true zero to affect the overall results which is why I want zeros to be recorded as a 'Z' and not as true '0'
OK I am beginning to understand - I think.
I just called the company that programs our scanning software and if a student is absent it exports a '0' and not a blank cell. In my sheet (prepost9) I must have deleted some of the '0's which then made those first few cells to be blank as I was trying something out. In other words I believe my prepost9 sheet will never have any blank cells.
So I need to rethink our scoring system - if a student actually receives a true '0' then we will record a '1' and then I can use your formula to convert all '1's to a 'Z' which indicates that the student received a zero but as a 'Z' it will not affect the results. If a student is absent our program exports a '0' and then I will replace all zeros with a blank cell which will also not affect our results.
So let me play around with this for a day or two and I will get back to you. You have been most helpful and patient with me and I apologize for confusing you with my attempts. I will leave you a very positive rating and come back and give you a tip once I have everything figured out.