Range formula Query 1

[robcarr]

Dear All,

I have a ss with all the current months data in that uses this formula to find the correct info for the agent

=IF(ISNA(VLOOKUP($A7,aux1,3,FALSE))/(24*60*60),"",VLOOKUP($A7,aux1,3,FALSE)/(24*60*60))

this formulas is in B7, in C7 I have the following formula

=IF(ISNA(VLOOKUP($A7,aux2,3,FALSE))/(24*60*60),"",VLOOKUP($A7,aux2,3,FALSE)/(24*60*60))

for each column upto column AF i have the same formula except the aux2 would increase by 1 so column d would aux3, is there any way in either formulas or coding to get the formula to autofill all the columns by adding 1 each time to the aux, as I have to do this for 10 sheets in the book, and each sheet has 31 columns to change the aux part from 1 to 31.

Any suggestions help greatly appreciated.


Thanks Rob.

 

[xlbo]

this should do it:
=IF(ISNA(VLOOKUP($A7,indirect("aux" & column()-1),3,FALSE))/(24*60*60),"",VLOOKUP($A7,indirect("aux" & column()-1),3,FALSE)/(24*60*60))

Being as the column the formula is in is C (col 3) and it refs Aux2, I have used column()-1 as the incrementer - change to suit if necessary


Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[robcarr]

thanks Geoff, as always a perfect answer to the request, works a treat saved me hours of formatting at the end of the month.



Thanks Rob.