Can we use if function on a cell that is a dependent cell of other cell

[VuiLeong]

Device/OS: PC/Windows 10

Excel version: Office Professional 10 Plus 2010, Version 14.0.7258.5000  

I would like to seek for advice for the following

Input data 1: dates in Column A and values and Column C

Input data 2: each value in Column C will be set as a dependent cell in exactly one column from Column F to Column O

If the value is set as a dependent cell in a column (from Column F to Column O), say Column F, then the F1 need to be printed to the left of the value

Please refer to the photo

Illustration 1

Input data 1: I have keyed-in the date in A2 and 29.4786 in C2,
Input data 2: C2 is set as dependent cell of M2 (I keyed in =C2 at M2))

Then, I need to print “theta” (at M1) at B2

Illustration 2

Input data 1: I have keyed-in the date in A6 and 29.4786 in C6,
Input data 2: C6 is set as dependent cell of I5 (I keyed in =C6 at I5))

Then, I need to print “delta” (at I1) at B6

Since I have keyed-in the date in A8 and 979.616 in C8, and keyed-in =C8 in L6, so I need to print “eta” in B8.

Can this be done automatically after the step of input data 2?

Thank you for your help in advanced.

[#2 · Accepted solution]

@VuiLeong You don't mention column E in your description, so I ignored it.

In B2:

=IF(C2="","",INDEX($F$1:$O$1,SUMPRODUCT(($F$2:$O$45=C2)*(COLUMN($F$1:$O$1)-COLUMN($F$1)+1))))

Fill down as far as you want.

[#3]

@VuiLeong

Credit and another example: Updating cell values based on the values of other cells in the same row in Excel  

Add this formula to B2:

=IF(NOT(ISBLANK(F2)),$F$1,IF(NOT(ISBLANK(G2)),$G$1,IF(NOT(ISBLANK(H2)),$H$1,IF(NOT(ISBLANK(I2)),$I$1,IF(NOT(ISBLANK(J2)),$J$1,IF(NOT(ISBLANK(K2)),$K$1,IF(NOT(ISBLANK(L2)),$L$2,IF(NOT(ISBLANK(M2)),$M$1,IF(NOT(ISBLANK(N2)),$N$1,IF(NOT(ISBLANK(O2)),$O$2,""))))))))))

This to B4:

=IF(NOT(ISBLANK(F3)),$F$1,IF(NOT(ISBLANK(G3)),$G$1,IF(NOT(ISBLANK(H3)),$H$1,IF(NOT(ISBLANK(I3)),$I$1,IF(NOT(ISBLANK(J3)),$J$1,IF(NOT(ISBLANK(K3)),$K$1,IF(NOT(ISBLANK(L3)),$L$3,IF(NOT(ISBLANK(M3)),$M$1,IF(NOT(ISBLANK(N3)),$N$1,IF(NOT(ISBLANK(O3)),$O$3,""))))))))))

This to B6:

=IF(NOT(ISBLANK(F4)),$F$1,IF(NOT(ISBLANK(G4)),$G$1,IF(NOT(ISBLANK(H4)),$H$1,IF(NOT(ISBLANK(I4)),$I$1,IF(NOT(ISBLANK(J4)),$J$1,IF(NOT(ISBLANK(K4)),$K$1,IF(NOT(ISBLANK(L4)),$L$4,IF(NOT(ISBLANK(M4)),$M$1,IF(NOT(ISBLANK(N4)),$N$1,IF(NOT(ISBLANK(O4)),$O$4,""))))))))))

This to B8:

=IF(NOT(ISBLANK(F5)),$F$1,IF(NOT(ISBLANK(G5)),$G$1,IF(NOT(ISBLANK(H5)),$H$1,IF(NOT(ISBLANK(I5)),$I$1,IF(NOT(ISBLANK(J5)),$J$1,IF(NOT(ISBLANK(K5)),$K$1,IF(NOT(ISBLANK(L5)),$L$5,IF(NOT(ISBLANK(M5)),$M$1,IF(NOT(ISBLANK(N5)),$N$1,IF(NOT(ISBLANK(O5)),$O$5,""))))))))))

[#4]

@Hans Vogelaar Thank you for the reply. It works well!

[#5]

@Vedran_Loani Thank you so much for taking time and effort to produce such a formula. It works for B2 and B4, just needs a bit of amendment for B6 and B8. Thank you once again!

[#6]

@VuiLeong you're welcome, my reply applies if the values in the C column are unknown or if the values in the C column are added after entering values in the F2:O2 range but since you enter the values in the C column then =IF(C2="","",INDEX($F$1:$O$1,SUMPRODUCT(($F$2:$O$45=C2)*(COLUMN($F$1:$O$1)-COLUMN($F$1)+1)))) is better

[#7]

@Vedran_Loani
Thanks for further explanation.

[#8]

@Hans Vogelaar @Vedran_Loani

Can i trouble you to guide on this wonderful formula?
=IF(C2="","",INDEX($F$1:$O$1,SUMPRODUCT(($F$2:$O$45=C2)*(COLUMN($F$1:$O$1)-COLUMN($F$1)+1))))

i tried to study and comprehend this formula. I found out that
SUMPRODUCT(($F$2:$O$45=C2)*(COLUMN($F$1:$O$1)-COLUMN($F$1)+1)) returns 8, thus INDEX($F$1:$O$1, 8) will return theta

i did my own study on the formula sumproduct, column, index in the past 2 days, but i cannot figure out why SUMPRODUCT(($F$2:$O$45=C2)*(COLUMN($F$1:$O$1)-COLUMN($F$1)+1) will return 8

Please correct me if i am wrong. i found out that
COLUMN($F$1:$O$1 return 6
COLUMN($F$1) return 6
so (COLUMN($F$1:$O$1)-COLUMN($F$1)+1) = 1 ?? why we need this for?

The first array in sumproduct is ($F$2:$O$45=C2). The array is NOT a column.
I could not find any similar example in https://exceljet.net, https://www.ablebits.com and etc. There are many examples on how to use sumproduct. However, in all the examples regarding sumproduct, the first array is a column. Hence, i do not comprehend this ($F$2:$O$45=C2) at all.

If it is too troublesome to explain, appreciate if you could suggest some relevant online resources so that i can do my own self-study.

Thank you for reading this post

[#9]

@VuiLeong 

COLUMN($F$1:$O$1) does not just return 6, it returns an array of the column numbers of F1:O1:

{6,7,8,9,10,11,12,13,14,15}

COLUMN($F$1:$O$1)-COLUMN($F$1)+1 subtracts 6 then adds 1, so it effectively subtracts 5:

{1,2,3,4,5,6,7,8,9,10}

$F$2:$O$45=C2 returns an array of TRUE/FALSE values: TRUE if a cell in F2:O45 equals C2, FALSE otherwise. When you force Excel to use TRUE/FALSE values in numeric calculations, it treats TRUE as 1 and FALSE as 0.

So when we multiply the array with the array {1,2,3,4,5,6,7,8,9,10}, it will act as an array of all zeros except for a 1 corresponding to the cell that equals C2.

SUMPRODUCT multiplies this with the column index in {1,2,3,4,5,6,7,8,9,10}, all the rest evaluates to 0. So the sum is the column index.

This index is used by the INDEX function to return the appropriate header in F1:O1.

[#10]

@Hans Vogelaar Thanks a lot for your guidance and help!