View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Lmm Lmm is offline
external usenet poster
 
Posts: 5
Default 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