How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask The-PC-Guy Your Own Question
The-PC-Guy, Computer Manager
Category: Microsoft Office
Satisfied Customers: 1964
Experience:  20 years experience providing remote computer support
Type Your Microsoft Office Question Here...
The-PC-Guy is online now
A new question is answered every 9 seconds

I need help with a complicated excel formula that got corrupted.

Customer Question

i need help with a complicated excel formula that got corrupted. i'm willing to pay.
Submitted: 2 years ago.
Category: Microsoft Office
Expert:  Jason Jones replied 2 years ago.

If anyone wishes to ask Jason a question directly,
they can do so at the following page:

My name is Jason. I look forward to helping you today.
May I take a look at the document? Please, tell me which cells need to be fixed.
Please, be forewarned that the site is not secure:
- Click the following link:
- Upload the file to that website
- Once it is uploaded, the resulting page will display a "File ID" number.
- Please, give me that "File ID Number"
Thank you,
Customer: replied 2 years ago.
Hi Jason, the file # ***** *****
Customer: replied 2 years ago.
once you receive it please call me at(###) ###-####so i can explain to you the issue. i cannot explain it as a reply.
Expert:  Jason Jones replied 2 years ago.
I am sorry, but I cannot make any calls out.
May I ask for you to describe it here?
Customer: replied 2 years ago.
i cannot it's too long and too complicated to explain.
Expert:  Jason Jones replied 2 years ago.
You can type what you wish to say on the phone.
If you do need to speak with someone, I can opt out of the question and allow another to help.
Customer: replied 2 years ago.
please do Jason, it's too long for me to type since i'm at work now and it's work related.
Expert:  Jason Jones replied 2 years ago.
Thank you.
Expert:  The-PC-Guy replied 2 years ago.
Thanks for using JustAnswer. My name is***** will do whatever I can to answer your question
I see you want a phone call to resolve this issue unfortunately due to site rules we are prohibited from doing that. However I can offer you an alternative.
If you want to set up a remote screen share, where you could show me what your issue is and I can attempt to correct it that way. This option might make the most sense.
Please let me know how you want to proceed
Customer: replied 2 years ago.
sure lets do it.
Expert:  The-PC-Guy replied 2 years ago.
first i am sending an offer for additional service to cover the time,
please accept the offer
then I will tell you how to do it.
Customer: replied 2 years ago.
I decline the offer thank you
Expert:  The-PC-Guy replied 2 years ago.
ok good luck
Expert:  The-PC-Guy replied 2 years ago.
if you change your mind at some point feel free to contact me
Customer: replied 2 years ago.
The issue is as follows;
The excel workbook was created several years ago and was working fine till about a month ago.
It has 4 worksheets named: "Mid month letter" "End month letter" "End month warning" that help us generate lateness reports once every 2 weeks (see Mid month letter), once a month (see end month letter) and a monthly write up warning notice (see end month warning). the 2 other spreadsheets are "input" where we type in the days of the month the employee was late using an "x" and the last spreadsheet is the "process 1 do not touch" which contains the formula.Once we type in the employee # (in column A) it's suppose to fill in automatically the full employee name and the dates he/she was late on each and every spreadsheet. (see "Dear: 16"). where 16 is the employee id #.
As of now it not generating anything on the "Mid month letter" and the "End month letter". The "End month Warning" is generating info up till user #92. any user above #92 generates 0.Please fix the issue for us.Thank you
Expert:  The-PC-Guy replied 2 years ago.
i know what your problem is
First you have the vlookup formula referring to a named range which only goes for about the first 100 rows or so. Any data you try to look up beyond that will not populate. I can fix that so it uses all rows.
2nd, you have some 0s in your column B for some of the names, if you lookup that ID# ***** if it has a 0 in the cell you are looking at that is the value that will go in the letter.
So the question for you is if the value that is being returned is a 0 or blank what would you like the value in the letter cell B4 and B6 to be? do you want that just to default to blank?
Customer: replied 2 years ago.
In the mid-month and the end month letters cell b4 should allow me to type the employee id number and then automatically cell b5 should bring up his name and also cell b6 should bring up the dates he was late from the input spreadsheet. For the end month warning spreadsheet i need to type the id number in cell d6 and it should bring up automatically his name in cell d4 as well as his position in cell d5 and the dates he was late from the input spreadsheet in cell d8. Thanks
Expert:  The-PC-Guy replied 2 years ago.
yes, however in the case his name has a 0 or is blank, what should the value of cells b5 and b6, and d5 d6 ectt. be?
Expert:  The-PC-Guy replied 2 years ago.
should be working now the way you requested
let me know if you need any changes
download changed file from here of Lateness Report 4-15-JA-Mod.xls
let me know if you have any questions, problems, or concerns
Do not rate negatively, instead continue the conversation with me so I can address any of your concerns
if you have any questions in the future you can visit my profile
and ask a question right in my box,
also you can put "PC Guy only" in your question title if you want to get to me.
Customer: replied 2 years ago.
i tried it and it's not generating any results. in the mid month letter when i type the employee id # (from column A in the input sheet) it should type in his name (DEAR: Mike Levy) in cell B4. (type his # ***** cell K4). And generates the dates on cell B6.
On End month letter sheet same thing.
On the End month warning i type his ID # (from Input column A) in cell D6 will type in his name in D4 and his position in D5 as well as the dates of the entire month in cell D8.
Expert:  The-PC-Guy replied 2 years ago.
did you download the updated version I sent you?
see this image
is that not the output you resquested?
Customer: replied 2 years ago.
Yes looking at the picture it's good but when i tried it on the actual workbook it didn't work
Expert:  The-PC-Guy replied 2 years ago.
well it does work on my computer, so.
if you want to do the remote support option at some point for the additional fee we discussed, that is always an option. Then at least I could see why something would be happening differently on your computer.
If you still wish to decline the offer, that is your option too. At the very least since I did fix the formula, please rate the answer so I will get reimbursed thus far.

Related Microsoft Office Questions