|
Return value of heading if an adjacent cell in a row is not blank
I need some help/ideas in creating a macro or subroutine that returns the
column heading of the last cell in a row series that is not blank, and populates the column heading in a cell adjacent to the last task date. I'm using a worksheet to track the dates of when a particular task is completed, and then I want to know what was the last task completed in this series in the cell next to the last task on that row. The data looks like this: Task 1 Task 2 Task 3 Task 4 Task 5 Last Task completed 4/1 4/5 4/6 4/8 Task 4 3/8 3/7 Task 2 3/15 3/17 3/18 3/22 3/29 Task 5 4/12 4/14 4/15 Task 3 ....and so on. What I'd like to do is have Excel check the row to see if it has a date value and then go to the next cell until it finds one that is blank. If it is blank, then return the column heading (task) of the last cell that was not blank in the appropriate cell (in this case "Last Task Completed". I'm using Excel 2000 and I'm comfortable using Excel, but beyond writing simple macros and functions - I'm somewhat limited as I'm not familiar with writing VBA code. So any ideas or suggestions will be greatly appreciated! -- Thanks, Wil H. |
Return value of heading if an adjacent cell in a row is not blank
Suppose your data are laid out starting in cell A1. That means that
'Task 5' is in cell E1. Now, if the tasks can be completed only in sequence (i.e., 1 must precede 2, which must precede 3, etc.), then the formula, in F2, =OFFSET($A$1,0,COUNTA(A2:E2)-1,1,1) gives you the desired result. Copy F2 as far down column F as required. Alternatively, if tasks can be completed in any sequence, use the array formula =INDEX($A$1:$E$1,MAX(COLUMN(A2:E2)*(A2:E2<""))) -- An array formula is entered with CTRL-SHIFT-ENTER rather than just ENTER. If done correctly, XL will display curly brackets { and } around the formula Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... I need some help/ideas in creating a macro or subroutine that returns the column heading of the last cell in a row series that is not blank, and populates the column heading in a cell adjacent to the last task date. I'm using a worksheet to track the dates of when a particular task is completed, and then I want to know what was the last task completed in this series in the cell next to the last task on that row. The data looks like this: Task 1 Task 2 Task 3 Task 4 Task 5 Last Task completed 4/1 4/5 4/6 4/8 Task 4 3/8 3/7 Task 2 3/15 3/17 3/18 3/22 3/29 Task 5 4/12 4/14 4/15 Task 3 ...and so on. What I'd like to do is have Excel check the row to see if it has a date value and then go to the next cell until it finds one that is blank. If it is blank, then return the column heading (task) of the last cell that was not blank in the appropriate cell (in this case "Last Task Completed". I'm using Excel 2000 and I'm comfortable using Excel, but beyond writing simple macros and functions - I'm somewhat limited as I'm not familiar with writing VBA code. So any ideas or suggestions will be greatly appreciated! |
Return value of heading if an adjacent cell in a row is not bl
Thank you for taking time to respond to my posting. Tushar's solution is the
one that produces the result that I'm looking for. Again, thank you for your help. "Tushar Mehta" wrote: Suppose your data are laid out starting in cell A1. That means that 'Task 5' is in cell E1. Now, if the tasks can be completed only in sequence (i.e., 1 must precede 2, which must precede 3, etc.), then the formula, in F2, =OFFSET($A$1,0,COUNTA(A2:E2)-1,1,1) gives you the desired result. Copy F2 as far down column F as required. Alternatively, if tasks can be completed in any sequence, use the array formula =INDEX($A$1:$E$1,MAX(COLUMN(A2:E2)*(A2:E2<""))) -- An array formula is entered with CTRL-SHIFT-ENTER rather than just ENTER. If done correctly, XL will display curly brackets { and } around the formula Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... I need some help/ideas in creating a macro or subroutine that returns the column heading of the last cell in a row series that is not blank, and populates the column heading in a cell adjacent to the last task date. I'm using a worksheet to track the dates of when a particular task is completed, and then I want to know what was the last task completed in this series in the cell next to the last task on that row. The data looks like this: Task 1 Task 2 Task 3 Task 4 Task 5 Last Task completed 4/1 4/5 4/6 4/8 Task 4 3/8 3/7 Task 2 3/15 3/17 3/18 3/22 3/29 Task 5 4/12 4/14 4/15 Task 3 ...and so on. What I'd like to do is have Excel check the row to see if it has a date value and then go to the next cell until it finds one that is blank. If it is blank, then return the column heading (task) of the last cell that was not blank in the appropriate cell (in this case "Last Task Completed". I'm using Excel 2000 and I'm comfortable using Excel, but beyond writing simple macros and functions - I'm somewhat limited as I'm not familiar with writing VBA code. So any ideas or suggestions will be greatly appreciated! |
Return value of heading if an adjacent cell in a row is not bl
Testing shows that all three formulas produce the same result. Tushars could
even be =OFFSET($A$1,0,COUNTA(3:3)-1,1,1) =INDEX(1:1,MAX(COLUMN(3:3)*(3:3<""))) cse -- Don Guillett SalesAid Software "Wil H." wrote in message ... Thank you for taking time to respond to my posting. Tushar's solution is the one that produces the result that I'm looking for. Again, thank you for your help. "Tushar Mehta" wrote: Suppose your data are laid out starting in cell A1. That means that 'Task 5' is in cell E1. Now, if the tasks can be completed only in sequence (i.e., 1 must precede 2, which must precede 3, etc.), then the formula, in F2, =OFFSET($A$1,0,COUNTA(A2:E2)-1,1,1) gives you the desired result. Copy F2 as far down column F as required. Alternatively, if tasks can be completed in any sequence, use the array formula =INDEX($A$1:$E$1,MAX(COLUMN(A2:E2)*(A2:E2<""))) -- An array formula is entered with CTRL-SHIFT-ENTER rather than just ENTER. If done correctly, XL will display curly brackets { and } around the formula Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... I need some help/ideas in creating a macro or subroutine that returns the column heading of the last cell in a row series that is not blank, and populates the column heading in a cell adjacent to the last task date. I'm using a worksheet to track the dates of when a particular task is completed, and then I want to know what was the last task completed in this series in the cell next to the last task on that row. The data looks like this: Task 1 Task 2 Task 3 Task 4 Task 5 Last Task completed 4/1 4/5 4/6 4/8 Task 4 3/8 3/7 Task 2 3/15 3/17 3/18 3/22 3/29 Task 5 4/12 4/14 4/15 Task 3 ...and so on. What I'd like to do is have Excel check the row to see if it has a date value and then go to the next cell until it finds one that is blank. If it is blank, then return the column heading (task) of the last cell that was not blank in the appropriate cell (in this case "Last Task Completed". I'm using Excel 2000 and I'm comfortable using Excel, but beyond writing simple macros and functions - I'm somewhat limited as I'm not familiar with writing VBA code. So any ideas or suggestions will be greatly appreciated! |
| All times are GMT +1. The time now is 02:59 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com