Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
hlookup or other method
I have a spread sheet of 100 rows & 60 columns
Bottom row is years 2008 thru 2068 -- Range A1:BH100 Columns have numbers scattered randomly. Most numbers are 0 Some rows have only one column with a number, others have several columns with numbers. The numbers are of different values and are not in ascending or decending value. How can I start in a1 and locate the first column,in the row, with a number greater than 0 and then identify the year? I need to start with " If(A1 = 0 ----. I have tried lookup, hlookup & match but probably am not doing something right. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
hlookup or other method
First of all, you'd need 61 columns to go from 2008 to 2068.
That brings you out to BI100, not BH100! Try this *array* formula in BJ1: =INDEX(A$100:BI$100,MATCH(TRUE,A1:BI10,0)) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. You *must also* use CSE when revising the formula. *After* the CSE entry, copy the formula down to BJ100. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Duplatt" wrote in message ... I have a spread sheet of 100 rows & 60 columns Bottom row is years 2008 thru 2068 -- Range A1:BH100 Columns have numbers scattered randomly. Most numbers are 0 Some rows have only one column with a number, others have several columns with numbers. The numbers are of different values and are not in ascending or decending value. How can I start in a1 and locate the first column,in the row, with a number greater than 0 and then identify the year? I need to start with " If(A1 = 0 ----. I have tried lookup, hlookup & match but probably am not doing something right. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
hlookup or other method
RD - Thank You, it worked great-- and you are right,of course, about the
number of columns. Appreciated -- Duane "Ragdyer" wrote: First of all, you'd need 61 columns to go from 2008 to 2068. That brings you out to BI100, not BH100! Try this *array* formula in BJ1: =INDEX(A$100:BI$100,MATCH(TRUE,A1:BI10,0)) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. You *must also* use CSE when revising the formula. *After* the CSE entry, copy the formula down to BJ100. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Duplatt" wrote in message ... I have a spread sheet of 100 rows & 60 columns Bottom row is years 2008 thru 2068 -- Range A1:BH100 Columns have numbers scattered randomly. Most numbers are 0 Some rows have only one column with a number, others have several columns with numbers. The numbers are of different values and are not in ascending or decending value. How can I start in a1 and locate the first column,in the row, with a number greater than 0 and then identify the year? I need to start with " If(A1 = 0 ----. I have tried lookup, hlookup & match but probably am not doing something right. |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
hlookup or other method
You're welcome, and your feed-back is appreciated.
-- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "Duplatt" wrote in message ... RD - Thank You, it worked great-- and you are right,of course, about the number of columns. Appreciated -- Duane "Ragdyer" wrote: First of all, you'd need 61 columns to go from 2008 to 2068. That brings you out to BI100, not BH100! Try this *array* formula in BJ1: =INDEX(A$100:BI$100,MATCH(TRUE,A1:BI10,0)) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. You *must also* use CSE when revising the formula. *After* the CSE entry, copy the formula down to BJ100. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Duplatt" wrote in message ... I have a spread sheet of 100 rows & 60 columns Bottom row is years 2008 thru 2068 -- Range A1:BH100 Columns have numbers scattered randomly. Most numbers are 0 Some rows have only one column with a number, others have several columns with numbers. The numbers are of different values and are not in ascending or decending value. How can I start in a1 and locate the first column,in the row, with a number greater than 0 and then identify the year? I need to start with " If(A1 = 0 ----. I have tried lookup, hlookup & match but probably am not doing something right. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Please post this thread a correct full method, method about | New Users to Excel | |||
Please post this thread a complete correct method, method about te | New Users to Excel | |||
Another Method or 2? | Excel Discussion (Misc queries) | |||
Which method to use | New Users to Excel | |||
best method | Excel Discussion (Misc queries) |