Is there a specific Excel Function that doesn't this?
 

I am looking for a way or a function that would perform this function. I
will use 1 row to explain what I am trying to do. Take row3, as an example,
B3 and C3 are my formular cells and D3 to L3 are my input cells. What I am
looking for is a formular that I can use in B3 and C3 that will tell me which
cells in Range(D3:L3) has values in them.
For instance if I have values in Range(E3:G3) I want B3=2 and C3=4 or if I
have values in Range(D3:F3) then Range(I3:K3), I want B3=1 and C3=8 and if I
have just one cell, H3 with value, then both B3 and C3 will equal 5.

Is there any function in excel that can do this?

Thanks
Ayo

Is there a specific Excel Function that doesn't this?
 

Could you explain the logic behind your desired returns?

For instance if I have values in Range(E3:G3) I want B3=2 and C3=4

Where do the 2 and 4 come from?

have values in Range(D3:F3) then Range(I3:K3), I want B3=1 and C3=8

Why 1 and 8?


Gord Dibben MS Excel MVP

On Wed, 17 Jun 2009 07:30:24 -0700, Ayo
wrote:

I am looking for a way or a function that would perform this function. I
will use 1 row to explain what I am trying to do. Take row3, as an example,
B3 and C3 are my formular cells and D3 to L3 are my input cells. What I am
looking for is a formular that I can use in B3 and C3 that will tell me which
cells in Range(D3:L3) has values in them.
For instance if I have values in Range(E3:G3) I want B3=2 and C3=4 or if I
have values in Range(D3:F3) then Range(I3:K3), I want B3=1 and C3=8 and if I
have just one cell, H3 with value, then both B3 and C3 will equal 5.

Is there any function in excel that can do this?

Thanks
Ayo

Is there a specific Excel Function that doesn't this?
 

I am trying to create a project plan. I have one already but I am trying to
see if there is another way to do. Range(D1:L1) will have the numbers 1 to 9.
These are the numbers that will go into B3 and C3 based on where the values
are in D3 to L3.

"Gord Dibben" wrote:

Could you explain the logic behind your desired returns?

For instance if I have values in Range(E3:G3) I want B3=2 and C3=4

Where do the 2 and 4 come from?

have values in Range(D3:F3) then Range(I3:K3), I want B3=1 and C3=8

Why 1 and 8?


Gord Dibben MS Excel MVP

On Wed, 17 Jun 2009 07:30:24 -0700, Ayo
wrote:

I am looking for a way or a function that would perform this function. I
will use 1 row to explain what I am trying to do. Take row3, as an example,
B3 and C3 are my formular cells and D3 to L3 are my input cells. What I am
looking for is a formular that I can use in B3 and C3 that will tell me which
cells in Range(D3:L3) has values in them.
For instance if I have values in Range(E3:G3) I want B3=2 and C3=4 or if I
have values in Range(D3:F3) then Range(I3:K3), I want B3=1 and C3=8 and if I
have just one cell, H3 with value, then both B3 and C3 will equal 5.

Is there any function in excel that can do this?

Thanks
Ayo

Is there a specific Excel Function that doesn't this?
 

Assuming 1 to 9 in D1:L1 are your actual values, put these array-entered**
formulas in the indicated cells...

B3:
=IF(COUNTA(D2:L2)=0,"",MIN(IF(D2:L2="","",COLUMN(D 2:L2)-3)))

C3:
=IF(COUNTA(D2:L2)=0,"",LOOKUP(2,1/(D2:L2<""),COLUMN(A2:L2)))

**Commit both formulas using Ctrl+Shift+Enter, not just Enter by itself

--
Rick (MVP - Excel)


"Ayo" wrote in message
...
I am trying to create a project plan. I have one already but I am trying to
see if there is another way to do. Range(D1:L1) will have the numbers 1 to
9.
These are the numbers that will go into B3 and C3 based on where the
values
are in D3 to L3.

"Gord Dibben" wrote:

Could you explain the logic behind your desired returns?

For instance if I have values in Range(E3:G3) I want B3=2 and C3=4

Where do the 2 and 4 come from?

have values in Range(D3:F3) then Range(I3:K3), I want B3=1 and C3=8

Why 1 and 8?


Gord Dibben MS Excel MVP

On Wed, 17 Jun 2009 07:30:24 -0700, Ayo
wrote:

I am looking for a way or a function that would perform this function.
I
will use 1 row to explain what I am trying to do. Take row3, as an
example,
B3 and C3 are my formular cells and D3 to L3 are my input cells. What I
am
looking for is a formular that I can use in B3 and C3 that will tell me
which
cells in Range(D3:L3) has values in them.
For instance if I have values in Range(E3:G3) I want B3=2 and C3=4 or
if I
have values in Range(D3:F3) then Range(I3:K3), I want B3=1 and C3=8 and
if I
have just one cell, H3 with value, then both B3 and C3 will equal 5.

Is there any function in excel that can do this?

Thanks
Ayo

Is there a specific Excel Function that doesn't this?
 

Thanks Rick. I think I should be able to take it from here.

"Rick Rothstein" wrote:

Assuming 1 to 9 in D1:L1 are your actual values, put these array-entered**
formulas in the indicated cells...

B3:
=IF(COUNTA(D2:L2)=0,"",MIN(IF(D2:L2="","",COLUMN(D 2:L2)-3)))

C3:
=IF(COUNTA(D2:L2)=0,"",LOOKUP(2,1/(D2:L2<""),COLUMN(A2:L2)))

**Commit both formulas using Ctrl+Shift+Enter, not just Enter by itself

--
Rick (MVP - Excel)


"Ayo" wrote in message
...
I am trying to create a project plan. I have one already but I am trying to
see if there is another way to do. Range(D1:L1) will have the numbers 1 to
9.
These are the numbers that will go into B3 and C3 based on where the
values
are in D3 to L3.

"Gord Dibben" wrote:

Could you explain the logic behind your desired returns?

For instance if I have values in Range(E3:G3) I want B3=2 and C3=4

Where do the 2 and 4 come from?

have values in Range(D3:F3) then Range(I3:K3), I want B3=1 and C3=8

Why 1 and 8?


Gord Dibben MS Excel MVP

On Wed, 17 Jun 2009 07:30:24 -0700, Ayo
wrote:

I am looking for a way or a function that would perform this function.
I
will use 1 row to explain what I am trying to do. Take row3, as an
example,
B3 and C3 are my formular cells and D3 to L3 are my input cells. What I
am
looking for is a formular that I can use in B3 and C3 that will tell me
which
cells in Range(D3:L3) has values in them.
For instance if I have values in Range(E3:G3) I want B3=2 and C3=4 or
if I
have values in Range(D3:F3) then Range(I3:K3), I want B3=1 and C3=8 and
if I
have just one cell, H3 with value, then both B3 and C3 will equal 5.

Is there any function in excel that can do this?

Thanks
Ayo