Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto Fill
I read through all the posts, but couldn't find a solution that worked for
this. I have a report that's pulled out of another system and converted into XL. The column - A - "EMPLOYEE ID" is what I need to be able to sort on however the report doesn't list it for every row associated with that employee, just the first row that is associated. Is there a way by using macro or perhaps vlookup or an if statement to automatically fill in the employee ID number until it encounters the next non blank line? The spreadsheet looks like: A B C D Employee ID Deduction Type Deduction Amount Total YTD 1001 BMH $12 $48 CDP $10 $50 ABC $100 $200 XYZ $5 $5 2001 BMH $12 $48 CDP $10 $50 ABC $100 $200 XYZ $5 $50 3001 BMH $12 $48 CDP $10 $50 ABC $100 $200 XYZ $5 $50 So ideally it would fill in 1001 in column A until it gets to 2001 and then would fill in 2001 until it gets to 3001 and so on. Thanks in advance for the assistance!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto Fill
go to column E and enter in row 2 (assuming that's where the first employee
begins) =a2 then in row 3 enter =if(isblank(a3),e2,a3) and copy this formula down. then you can paste the results as values back into column A "Dedrie" wrote: I read through all the posts, but couldn't find a solution that worked for this. I have a report that's pulled out of another system and converted into XL. The column - A - "EMPLOYEE ID" is what I need to be able to sort on however the report doesn't list it for every row associated with that employee, just the first row that is associated. Is there a way by using macro or perhaps vlookup or an if statement to automatically fill in the employee ID number until it encounters the next non blank line? The spreadsheet looks like: A B C D Employee ID Deduction Type Deduction Amount Total YTD 1001 BMH $12 $48 CDP $10 $50 ABC $100 $200 XYZ $5 $5 2001 BMH $12 $48 CDP $10 $50 ABC $100 $200 XYZ $5 $50 3001 BMH $12 $48 CDP $10 $50 ABC $100 $200 XYZ $5 $50 So ideally it would fill in 1001 in column A until it gets to 2001 and then would fill in 2001 until it gets to 3001 and so on. Thanks in advance for the assistance!! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto Fill
Thanks -- that worked.
I have a similar report, but instead of leaving blanks it lists the employee name under the employee number such as: Employee ID Deduction Type Deduction Amount Total YTD 1001 BMH $12 $48 James CDP $10 $50 ABC $100 $200 XYZ $5 $5 2001 BMH $12 $48 John CDP $10 $50 ABC $100 $200 XYZ $5 $50 3001 BMH $12 $48 Sue CDP $10 $50 ABC $100 $200 XYZ $5 $50 Do you know how I could modify if statement you provided to fill in it as well but in a different column. I tried using the statement you provided but because the row below is non-blank I can't figure it out. Thanks again! "JAdamJ" wrote: go to column E and enter in row 2 (assuming that's where the first employee begins) =a2 then in row 3 enter =if(isblank(a3),e2,a3) and copy this formula down. then you can paste the results as values back into column A "Dedrie" wrote: I read through all the posts, but couldn't find a solution that worked for this. I have a report that's pulled out of another system and converted into XL. The column - A - "EMPLOYEE ID" is what I need to be able to sort on however the report doesn't list it for every row associated with that employee, just the first row that is associated. Is there a way by using macro or perhaps vlookup or an if statement to automatically fill in the employee ID number until it encounters the next non blank line? The spreadsheet looks like: A B C D Employee ID Deduction Type Deduction Amount Total YTD 1001 BMH $12 $48 CDP $10 $50 ABC $100 $200 XYZ $5 $5 2001 BMH $12 $48 CDP $10 $50 ABC $100 $200 XYZ $5 $50 3001 BMH $12 $48 CDP $10 $50 ABC $100 $200 XYZ $5 $50 So ideally it would fill in 1001 in column A until it gets to 2001 and then would fill in 2001 until it gets to 3001 and so on. Thanks in advance for the assistance!! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto Fill
I don't have time to go into the exact details right now, but if the employee
ID is truely a number (not text) then you could similarly use the isnumber(value) function and refer to the cell below the number for the name. Let me know if you still need help and I can be more specific later. "Dedrie" wrote: Thanks -- that worked. I have a similar report, but instead of leaving blanks it lists the employee name under the employee number such as: Employee ID Deduction Type Deduction Amount Total YTD 1001 BMH $12 $48 James CDP $10 $50 ABC $100 $200 XYZ $5 $5 2001 BMH $12 $48 John CDP $10 $50 ABC $100 $200 XYZ $5 $50 3001 BMH $12 $48 Sue CDP $10 $50 ABC $100 $200 XYZ $5 $50 Do you know how I could modify if statement you provided to fill in it as well but in a different column. I tried using the statement you provided but because the row below is non-blank I can't figure it out. Thanks again! "JAdamJ" wrote: go to column E and enter in row 2 (assuming that's where the first employee begins) =a2 then in row 3 enter =if(isblank(a3),e2,a3) and copy this formula down. then you can paste the results as values back into column A "Dedrie" wrote: I read through all the posts, but couldn't find a solution that worked for this. I have a report that's pulled out of another system and converted into XL. The column - A - "EMPLOYEE ID" is what I need to be able to sort on however the report doesn't list it for every row associated with that employee, just the first row that is associated. Is there a way by using macro or perhaps vlookup or an if statement to automatically fill in the employee ID number until it encounters the next non blank line? The spreadsheet looks like: A B C D Employee ID Deduction Type Deduction Amount Total YTD 1001 BMH $12 $48 CDP $10 $50 ABC $100 $200 XYZ $5 $5 2001 BMH $12 $48 CDP $10 $50 ABC $100 $200 XYZ $5 $50 3001 BMH $12 $48 CDP $10 $50 ABC $100 $200 XYZ $5 $50 So ideally it would fill in 1001 in column A until it gets to 2001 and then would fill in 2001 until it gets to 3001 and so on. Thanks in advance for the assistance!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro for Auto Fill | Excel Discussion (Misc queries) | |||
stop auto fill from thinking for itself!!!!!!! | New Users to Excel | |||
Auto Fill Options | Excel Discussion (Misc queries) | |||
Auto fill option box disappeared | Excel Worksheet Functions | |||
Auto Fill Options | Excel Discussion (Misc queries) |