Creating a formula that can navigate a poor design may be possible, but it's the equivalent of just putting a larger bandage on an infection, rather than treating the underlying cause. If you're open to that kind of re-thinking, in the long run you'd benefit. I've worked with this kind of data for decades.what you describe sounds as if you've taken what used to be a method of keeping records on paper and moved it to Excel, hoping to take advantage of Excel's magic to do some of the review and summarization of attendance logs, but maybe you didn't really re-think the method of keeping the raw data in the first place. I say this as a person who was the director of the HR/Payroll database for a major corporation (I'm now long retired). Excel really is good at taking single databases (in the form of a well-designed Excel Table), for such things as employee attendance records, and then producing the kind of report you're trying to create. My very frank reaction on reading your description is to think you probably would benefit from re-thinking the basic design here. In particular, I wonder why you seem to think it necessary to have multiple sheets with redundant (or so it would seem) data. If there is any possibility of your creating a facsimile of it that uses, say, the names of Star Wars or Disney characters in place of the actual names, it would really be helpful to see how you've organized the data. If there are other ways i can arrive at my answer without using vlookup, kindly let me know also as im trying to cut the manual steps it takes it in doing the vlookup against each realize that the actual spreadsheet almost certainly contains the real names of real people, as well as perhaps other confidential information. So i want excel to tell me the maximum number of days an employee has missed by cross referencing each sheet, i checked google and i found the IF error and Vlook up combination but when i tried it, the data it returned was not accurate, this was the formula (=IFERROR(VLOOKUP(B2,six!B:C,2,FALSE),IFERROR(VLOOKUP(B2,FOUR!B:C,2,FALSE),IFERROR(VLOOKUP(B2,THREE!B:C,2,FALSE),IFERROR(VLOOKUP(B2,TWO!B:C,2,FALSE), "Not Found")))) It will be ideal to first append data from all worksheets into a single worksheet and then write a VLOOKUP () function. I usually do a vlookup four times, is there a way i can use one formula so i started from the two days missed tab and i did a vlookup against the six, four and three days tab. days missed data but i want it to return six days only. If an employee missed six days they will also be on the two ,three, four. The REAL problem is, i'm a complete novice. I cant find a way to SUM the cells without having multiple errors. The problem i'm facing, is that i need to sum the cells across the sheets. Hello i am trying to do a vlookup across multiple sheets that have the same information, so basically i'm trying to see how many days an employee has missed, so there is data for employees who have missed two, three, four and six days. I'm currently trying to use the VLOOKUP function across multiple sheets, which isn't an issue.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |