How to find the first nonzero cell in a row
Hi,
I tried the INDEX & MATCH formulas and it works! But if my row # is
dynamic, i.e. take your example below that if row 3 be replaced with
different row #, how to incorporate the dynamic # in MATCH.
Lmm
"Katie" wrote:
Thanks! That worked perfectly.
"Ron Rosenfeld" wrote:
On Mon, 17 Oct 2005 12:26:04 -0700, "Katie"
wrote:
I have a worksheet that has store names as rows, week numbers as columns, and
inventory quantities as cells. I need a column that has a formula that shows
the first week that has inventory at each store.
Basically, I need to find the the column with the first non-zero cell in
each row, then return the week number listed at the top of that column in the
column header. I need the appropriate weeknumber for each row.
Any ideas would be appreciated!
Thanks
Katie
Assumptions:
1. Your week numbers are in row 1
2. Your weeknumbers and data start in Column D
This **array** formula will return the contents of row1 that is in the same
column as the first non-zero contents of the referenced row (row 3 in this
instance):
=INDEX($D$1:$IV$1,,MATCH(TRUE,D3:IV30,0))
You can copy/drag the formula down as needed.
To enter an array formula, after typing or pasting it in, you must hold down
<ctrl<shift while hitting <enter. Excel will place braces {...} around the
formula if you did it correctly.
--ron
|