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

  • Thread starter Thread starter Ayo
  • Start date Start date
A

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
 
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
Click to expand...

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
Click to expand...

Why 1 and 8?


Gord Dibben MS Excel MVP
 
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.
 
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
 
Back
Top