# Improving my Formula

#### Cooki

##### Board Regular
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

=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

 Partners Finance_Q1 Finance_Days_Q1 Comm_Val_Q1 Comm_Days Finance_State_Q1 Fiance_2_Days Partner_1 01/01/19 Partner_2 01/01/19 01/01/19

<tbody>
</tbody>

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### Special-K99

##### Well-known Member
Is there some reason why

IF(VLOOKUP(\$P\$8,INDIRECT("'"&C8&"'!"&"\$B\$2:\$AC\$110"),12,0)=0,INDIRECT(C8&"!M2"),

doesnt appear in that formula ?

Last edited:

#### Cooki

##### Board Regular
I dont need bring back that information.

Its really just im proving this formula =IF(VLOOKUP(\$P\$8,INDIRECT("'"&C8&"'!"&"\$B\$2:\$AC\$110"),2,0)=0,INDIRECT(C8&"!C2") and both the highlighted being increased by 2

4 = E
6 = G
8 = I
10 = K
SKIP 12
14 = O

And so on till 28 = AC

Replies
5
Views
79
Replies
10
Views
333
Replies
19
Views
120
Replies
2
Views
174
Replies
1
Views
218

1,127,554
Messages
5,625,474
Members
416,109
Latest member
TripleA00123

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back