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

[Ayo]

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

 

[Gord Dibben]

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

 

[Ayo]

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.

 

[Rick Rothstein]

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(D2: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

 

[Ayo]

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