Hi All
ive created this formula and it works perfect, but as your see in a sec its rather big.
Can anyone think of a smarter way to get the same result.
The below checks to see if there is a date in the BOLDED cell and if there is it moved onto the next till it cant find a date, when it cant find a date it returns that columns name. There are 29 columns and as you can see by the formula i look at every second column apart from 12.
The formula is on the front page of the database
I use named ranges to do my checks in P8 (Partners names) and C8 which referenced my 4 different sheets
Hope that all made sense
=IF(VLOOKUP($P$8,INDIRECT("'"&C8&"'!"&"$B$2:$AC$110"),2,0)=0,INDIRECT(C8&"!C2"),IF(VLOOKUP($P$8,INDIRECT("'"&C8&"'!"&"$B$2:$AC$110"),4,0)=0,INDIRECT(C8&"!E2"),IF(VLOOKUP($P$8,INDIRECT("'"&C8&"'!"&"$B$2:$AC$110"),6,0)=0,INDIRECT(C8&"!G2"),IF(VLOOKUP($P$8,INDIRECT("'"&C8&"'!"&"$B$2:$AC$110"),8,0)=0,INDIRECT(C8&"!I2"),IF(VLOOKUP($P$8,INDIRECT("'"&C8&"'!"&"$B$2:$AC$110"),10,0)=0,INDIRECT(C8&"!K2"),IF(VLOOKUP($P$8,INDIRECT("'"&C8&"'!"&"$B$2:$AC$110"),14,0)=0,INDIRECT(C8&"!O2"),IF(VLOOKUP($P$8,INDIRECT("'"&C8&"'!"&"$B$2:$AC$110"),16,0)=0,INDIRECT(C8&"!Q2"),IF(VLOOKUP($P$8,INDIRECT("'"&C8&"'!"&"$B$2:$AC$110"),18,0)=0,INDIRECT(C8&"!S2"),IF(VLOOKUP($P$8,INDIRECT("'"&C8&"'!"&"$B$2:$AC$110"),20,0)=0,INDIRECT(C8&"!U2"),IF(VLOOKUP($P$8,INDIRECT("'"&C8&"'!"&"$B$2:$AC$110"),22,0)=0,INDIRECT(C8&"!W2"),IF(VLOOKUP($P$8,INDIRECT("'"&C8&"'!"&"$B$2:$AC$110"),24,0)=0,INDIRECT(C8&"!Y2"),IF(VLOOKUP($P$8,INDIRECT("'"&C8&"'!"&"$B$2:$AC$110"),26,0)=0,INDIRECT(C8&"!AA2"),IF(VLOOKUP($P$8,INDIRECT("'"&C8&"'!"&"$B$2:$AC$110"),28,0)=0,INDIRECT(C8&"!AC2"),"Management_Fee_Completed")))))))))))))
Preview Table
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Partners[/TD]
[TD]Finance_Q1[/TD]
[TD]Finance_Days_Q1[/TD]
[TD]Comm_Val_Q1[/TD]
[TD]Comm_Days[/TD]
[TD]Finance_State_Q1[/TD]
[TD]Fiance_2_Days[/TD]
[/TR]
[TR]
[TD]Partner_1[/TD]
[TD]01/01/19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Partner_2[/TD]
[TD]01/01/19[/TD]
[TD][/TD]
[TD]01/01/19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
ive created this formula and it works perfect, but as your see in a sec its rather big.
Can anyone think of a smarter way to get the same result.
The below checks to see if there is a date in the BOLDED cell and if there is it moved onto the next till it cant find a date, when it cant find a date it returns that columns name. There are 29 columns and as you can see by the formula i look at every second column apart from 12.
The formula is on the front page of the database
I use named ranges to do my checks in P8 (Partners names) and C8 which referenced my 4 different sheets
Hope that all made sense
=IF(VLOOKUP($P$8,INDIRECT("'"&C8&"'!"&"$B$2:$AC$110"),2,0)=0,INDIRECT(C8&"!C2"),IF(VLOOKUP($P$8,INDIRECT("'"&C8&"'!"&"$B$2:$AC$110"),4,0)=0,INDIRECT(C8&"!E2"),IF(VLOOKUP($P$8,INDIRECT("'"&C8&"'!"&"$B$2:$AC$110"),6,0)=0,INDIRECT(C8&"!G2"),IF(VLOOKUP($P$8,INDIRECT("'"&C8&"'!"&"$B$2:$AC$110"),8,0)=0,INDIRECT(C8&"!I2"),IF(VLOOKUP($P$8,INDIRECT("'"&C8&"'!"&"$B$2:$AC$110"),10,0)=0,INDIRECT(C8&"!K2"),IF(VLOOKUP($P$8,INDIRECT("'"&C8&"'!"&"$B$2:$AC$110"),14,0)=0,INDIRECT(C8&"!O2"),IF(VLOOKUP($P$8,INDIRECT("'"&C8&"'!"&"$B$2:$AC$110"),16,0)=0,INDIRECT(C8&"!Q2"),IF(VLOOKUP($P$8,INDIRECT("'"&C8&"'!"&"$B$2:$AC$110"),18,0)=0,INDIRECT(C8&"!S2"),IF(VLOOKUP($P$8,INDIRECT("'"&C8&"'!"&"$B$2:$AC$110"),20,0)=0,INDIRECT(C8&"!U2"),IF(VLOOKUP($P$8,INDIRECT("'"&C8&"'!"&"$B$2:$AC$110"),22,0)=0,INDIRECT(C8&"!W2"),IF(VLOOKUP($P$8,INDIRECT("'"&C8&"'!"&"$B$2:$AC$110"),24,0)=0,INDIRECT(C8&"!Y2"),IF(VLOOKUP($P$8,INDIRECT("'"&C8&"'!"&"$B$2:$AC$110"),26,0)=0,INDIRECT(C8&"!AA2"),IF(VLOOKUP($P$8,INDIRECT("'"&C8&"'!"&"$B$2:$AC$110"),28,0)=0,INDIRECT(C8&"!AC2"),"Management_Fee_Completed")))))))))))))
Preview Table
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Partners[/TD]
[TD]Finance_Q1[/TD]
[TD]Finance_Days_Q1[/TD]
[TD]Comm_Val_Q1[/TD]
[TD]Comm_Days[/TD]
[TD]Finance_State_Q1[/TD]
[TD]Fiance_2_Days[/TD]
[/TR]
[TR]
[TD]Partner_1[/TD]
[TD]01/01/19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Partner_2[/TD]
[TD]01/01/19[/TD]
[TD][/TD]
[TD]01/01/19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]