Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I have a table of monthly data for 2008. Monthly columns are empty until
month end data is available, except for the top row which contains the month names Jan thru Dec. How do I write a function to place the Excel Column Letter in a cell to identify the monthly column having the most recent data. If the top cell in a column is non-blank, then that column, and all before will complete. Hope this description is clear. Thx for your help, John |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Hi,
Well its not quite clear since you say the top column contains all the months but if it is not empty all the previous columns are complete? So I'm going to assume that row 2 contains data out to the last column which is complete. But row 1 contains data (month names) ever further. =LEFT(ADDRESS(1,MAX((2:2<"")*COLUMN(2:2)),4)) This is array entered - Press Shift+Ctrl+Enter instead of Enter. Also this formula assumes that row 2 only has data out to the last month, not data way outside the original data range. If that is the case edit the formula replacing 2:2 with something like A2:N2 or whatever is appropriate. If this is helpful, please click the Yes button. -- Thanks, Shane Devenshire "John" wrote: I have a table of monthly data for 2008. Monthly columns are empty until month end data is available, except for the top row which contains the month names Jan thru Dec. How do I write a function to place the Excel Column Letter in a cell to identify the monthly column having the most recent data. If the top cell in a column is non-blank, then that column, and all before will complete. Hope this description is clear. Thx for your help, John |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Try
'Sheet1 A1-L1 has headers Jan-12 'Following macro will place the Mon name in Sheet2 Cell A1 till the month row 2 is filled in Sheet1 Sub findLastMonth() Dim i As Integer For i = 1 To 12 If Sheets("Sheet1").Cells(2, i).Value = "" Then Sheets("Sheet2").Cells(1, 1).Value = _ Sheets("Sheet1").Cells(1, i - 1).Value Exit Sub End If Next i End Sub -- Always provide your feedback so that others know whether the solution worked or problem still persists ... "John" wrote: I have a table of monthly data for 2008. Monthly columns are empty until month end data is available, except for the top row which contains the month names Jan thru Dec. How do I write a function to place the Excel Column Letter in a cell to identify the monthly column having the most recent data. If the top cell in a column is non-blank, then that column, and all before will complete. Hope this description is clear. Thx for your help, John |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Sheeloo, Shanes formula solution worked fine for me so I did not need to go
the somewhat more involved approach using your macro. I think this would have worked also, but I did not try it. Thx so much, john "Sheeloo" wrote: Try 'Sheet1 A1-L1 has headers Jan-12 'Following macro will place the Mon name in Sheet2 Cell A1 till the month row 2 is filled in Sheet1 Sub findLastMonth() Dim i As Integer For i = 1 To 12 If Sheets("Sheet1").Cells(2, i).Value = "" Then Sheets("Sheet2").Cells(1, 1).Value = _ Sheets("Sheet1").Cells(1, i - 1).Value Exit Sub End If Next i End Sub -- Always provide your feedback so that others know whether the solution worked or problem still persists ... "John" wrote: I have a table of monthly data for 2008. Monthly columns are empty until month end data is available, except for the top row which contains the month names Jan thru Dec. How do I write a function to place the Excel Column Letter in a cell to identify the monthly column having the most recent data. If the top cell in a column is non-blank, then that column, and all before will complete. Hope this description is clear. Thx for your help, John |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Thanks John for your feedback.
Yes, Shanes solution was much better. Try this =INDIRECT("A"&MAX((2:2<"")*COLUMN(2:2))) if you want to get the heading corresponding to the last filled cell in row 2.. This does not need to be entered with ctrl-shift-enter -- Always provide your feedback so that others know whether the solution worked or problem still persists ... "John" wrote: Sheeloo, Shanes formula solution worked fine for me so I did not need to go the somewhat more involved approach using your macro. I think this would have worked also, but I did not try it. Thx so much, john "Sheeloo" wrote: Try 'Sheet1 A1-L1 has headers Jan-12 'Following macro will place the Mon name in Sheet2 Cell A1 till the month row 2 is filled in Sheet1 Sub findLastMonth() Dim i As Integer For i = 1 To 12 If Sheets("Sheet1").Cells(2, i).Value = "" Then Sheets("Sheet2").Cells(1, 1).Value = _ Sheets("Sheet1").Cells(1, i - 1).Value Exit Sub End If Next i End Sub -- Always provide your feedback so that others know whether the solution worked or problem still persists ... "John" wrote: I have a table of monthly data for 2008. Monthly columns are empty until month end data is available, except for the top row which contains the month names Jan thru Dec. How do I write a function to place the Excel Column Letter in a cell to identify the monthly column having the most recent data. If the top cell in a column is non-blank, then that column, and all before will complete. Hope this description is clear. Thx for your help, John |
#6
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Forumula in my last post SHOULD be entered with CTRL-SHIFT-ENTER...
-- Always provide your feedback so that others know whether the solution worked or problem still persists ... "John" wrote: Sheeloo, Shanes formula solution worked fine for me so I did not need to go the somewhat more involved approach using your macro. I think this would have worked also, but I did not try it. Thx so much, john "Sheeloo" wrote: Try 'Sheet1 A1-L1 has headers Jan-12 'Following macro will place the Mon name in Sheet2 Cell A1 till the month row 2 is filled in Sheet1 Sub findLastMonth() Dim i As Integer For i = 1 To 12 If Sheets("Sheet1").Cells(2, i).Value = "" Then Sheets("Sheet2").Cells(1, 1).Value = _ Sheets("Sheet1").Cells(1, i - 1).Value Exit Sub End If Next i End Sub -- Always provide your feedback so that others know whether the solution worked or problem still persists ... "John" wrote: I have a table of monthly data for 2008. Monthly columns are empty until month end data is available, except for the top row which contains the month names Jan thru Dec. How do I write a function to place the Excel Column Letter in a cell to identify the monthly column having the most recent data. If the top cell in a column is non-blank, then that column, and all before will complete. Hope this description is clear. Thx for your help, John |
#7
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Hi Sheeloo,
Much better? Not alway; it depends on what you are doing, so providing a code solutions may be very helpful for the user. Keep up the good work! -- Thanks, Shane Devenshire "Sheeloo" wrote: Thanks John for your feedback. Yes, Shanes solution was much better. Try this =INDIRECT("A"&MAX((2:2<"")*COLUMN(2:2))) if you want to get the heading corresponding to the last filled cell in row 2.. This does not need to be entered with ctrl-shift-enter -- Always provide your feedback so that others know whether the solution worked or problem still persists ... "John" wrote: Sheeloo, Shanes formula solution worked fine for me so I did not need to go the somewhat more involved approach using your macro. I think this would have worked also, but I did not try it. Thx so much, john "Sheeloo" wrote: Try 'Sheet1 A1-L1 has headers Jan-12 'Following macro will place the Mon name in Sheet2 Cell A1 till the month row 2 is filled in Sheet1 Sub findLastMonth() Dim i As Integer For i = 1 To 12 If Sheets("Sheet1").Cells(2, i).Value = "" Then Sheets("Sheet2").Cells(1, 1).Value = _ Sheets("Sheet1").Cells(1, i - 1).Value Exit Sub End If Next i End Sub -- Always provide your feedback so that others know whether the solution worked or problem still persists ... "John" wrote: I have a table of monthly data for 2008. Monthly columns are empty until month end data is available, except for the top row which contains the month names Jan thru Dec. How do I write a function to place the Excel Column Letter in a cell to identify the monthly column having the most recent data. If the top cell in a column is non-blank, then that column, and all before will complete. Hope this description is clear. Thx for your help, John |
#8
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Thanks Shane,
In this case it was better. You too... -- Always provide your feedback so that others know whether the solution worked or problem still persists ... "ShaneDevenshire" wrote: Hi Sheeloo, Much better? Not alway; it depends on what you are doing, so providing a code solutions may be very helpful for the user. Keep up the good work! -- Thanks, Shane Devenshire "Sheeloo" wrote: Thanks John for your feedback. Yes, Shanes solution was much better. Try this =INDIRECT("A"&MAX((2:2<"")*COLUMN(2:2))) if you want to get the heading corresponding to the last filled cell in row 2.. This does not need to be entered with ctrl-shift-enter -- Always provide your feedback so that others know whether the solution worked or problem still persists ... "John" wrote: Sheeloo, Shanes formula solution worked fine for me so I did not need to go the somewhat more involved approach using your macro. I think this would have worked also, but I did not try it. Thx so much, john "Sheeloo" wrote: Try 'Sheet1 A1-L1 has headers Jan-12 'Following macro will place the Mon name in Sheet2 Cell A1 till the month row 2 is filled in Sheet1 Sub findLastMonth() Dim i As Integer For i = 1 To 12 If Sheets("Sheet1").Cells(2, i).Value = "" Then Sheets("Sheet2").Cells(1, 1).Value = _ Sheets("Sheet1").Cells(1, i - 1).Value Exit Sub End If Next i End Sub -- Always provide your feedback so that others know whether the solution worked or problem still persists ... "John" wrote: I have a table of monthly data for 2008. Monthly columns are empty until month end data is available, except for the top row which contains the month names Jan thru Dec. How do I write a function to place the Excel Column Letter in a cell to identify the monthly column having the most recent data. If the top cell in a column is non-blank, then that column, and all before will complete. Hope this description is clear. Thx for your help, John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Identify the row in which a MAX number in a column resides | Excel Worksheet Functions | |||
looking for formula to identify next non-empty cell on another sheet | Excel Worksheet Functions | |||
Need to identify duplicate entries in a Table | Excel Worksheet Functions | |||
how do I identify the OLAP datasource of a pivot table | Excel Discussion (Misc queries) | |||
Formula to Identify Column Number | Excel Discussion (Misc queries) |