I currently have a formula in a cell that populates the sheet2 cells with the latest 18 columns from sheet 1.
The problem I have is when the cell on sheet 1 is number 0 it stops the formula at the cell before but I need it to recognise the 0 and carry on.
This is the long winded formula I have used.
=IF(DOCIN.XLS!$EQ35="",DOCIN.XLS!DY35,IF(DOCIN.XLS!$ER35="",DOCIN.XLS!DZ35,IF(DOCIN.XLS!$ES35="",DOCIN.XLS!EA35,
IF(DOCIN.XLS!$ET35="",DOCIN.XLS!EB35,IF(DOCIN.XLS!$EU35="",DOCIN.XLS!EC35,IF(DOCIN.XLS!$EV35="",DOCIN.XLS!ED35,
IF(DOCIN.XLS!$EW35="",DOCIN.XLS!EE35,IF(DOCIN.XLS!$EX35="",DOCIN.XLS!EF35,IF(DOCIN.XLS!$EY35="",DOCIN.XLS!EG35,
IF(DOCIN.XLS!$EZ35="",DOCIN.XLS!EH35,IF(DOCIN.XLS!$FA35="",DOCIN.XLS!EI35,IF(DOCIN.XLS!$FB35="",DOCIN.XLS!EJ35,
IF(DOCIN.XLS!$FC35="",DOCIN.XLS!EK35,IF(DOCIN.XLS!$FD35="",DOCIN.XLS!EL35,IF(DOCIN.XLS!$FE35="",DOCIN.XLS!EM35,
IF(DOCIN.XLS!$FF35="",DOCIN.XLS!EN35,IF(DOCIN.XLS!$FG35="",DOCIN.XLS!EO35,IF(DOCIN.XLS!$FH35="",DOCIN.XLS!EP35,
IF(DOCIN.XLS!$FI35="",DOCIN.XLS!ER35,IF(DOCIN.XLS!$FJ35="",DOCIN.XLS!ER35,IF(DOCIN.XLS!$FK35="",DOCIN.XLS!ES35,
IF(DOCIN.XLS!$FL35="",DOCIN.XLS!ET35,IF(DOCIN.XLS!$FM35="",DOCIN.XLS!EU35,IF(DOCIN.XLS!$FN35="",DOCIN.XLS!EV35,
IF(DOCIN.XLS!$FO35="",DOCIN.XLS!EW35,IF(DOCIN.XLS!$FP35="",DOCIN.XLS!EX35,IF(DOCIN.XLS!$FQ35="",DOCIN.XLS!EY35,
IF(DOCIN.XLS!$FR35="",DOCIN.XLS!EZ35,IF(DOCIN.XLS!$FS35="",DOCIN.XLS!FA35,IF(DOCIN.XLS!$FT35="",DOCIN.XLS!FB35,
IF(DOCIN.XLS!$FU35="",DOCIN.XLS!FC35,IF(DOCIN.XLS!$FV35="",DOCIN.XLS!FD35,IF(DOCIN.XLS!$FW35="",DOCIN.XLS!FE35
,IF(DOCIN.XLS!$FX35="",DOCIN.XLS!FF35,IF(DOCIN.XLS!$FY35="",DOCIN.XLS!FG35,IF(DOCIN.XLS!$FZ35="",DOCIN.XLS!FH35,
IF(DOCIN.XLS!$GA35="",DOCIN.XLS!FI35,IF(DOCIN.XLS!$GB35="",DOCIN.XLS!FJ35,IF(DOCIN.XLS!$GC35="",DOCIN.XLS!FK35,
IF(DOCIN.XLS!$GD35="",DOCIN.XLS!FL35,IF(DOCIN.XLS!$GE35="",DOCIN.XLS!FM35,IF(DOCIN.XLS!$GF35="",DOCIN.XLS!FN35,
IF(DOCIN.XLS!$GG35="",DOCIN.XLS!FO35,IF(DOCIN.XLS!$GH35="",DOCIN.XLS!FP35,IF(DOCIN.XLS!$GI35="",DOCIN.XLS!FQ35,
IF(DOCIN.XLS!$GJ35="",DOCIN.XLS!FR35,DOCIN.XLS!FR35))))))))))))))))))))))))))))))))))))))))))))))
There is probably an easier way of doing this.....cheers
The problem I have is when the cell on sheet 1 is number 0 it stops the formula at the cell before but I need it to recognise the 0 and carry on.
This is the long winded formula I have used.
=IF(DOCIN.XLS!$EQ35="",DOCIN.XLS!DY35,IF(DOCIN.XLS!$ER35="",DOCIN.XLS!DZ35,IF(DOCIN.XLS!$ES35="",DOCIN.XLS!EA35,
IF(DOCIN.XLS!$ET35="",DOCIN.XLS!EB35,IF(DOCIN.XLS!$EU35="",DOCIN.XLS!EC35,IF(DOCIN.XLS!$EV35="",DOCIN.XLS!ED35,
IF(DOCIN.XLS!$EW35="",DOCIN.XLS!EE35,IF(DOCIN.XLS!$EX35="",DOCIN.XLS!EF35,IF(DOCIN.XLS!$EY35="",DOCIN.XLS!EG35,
IF(DOCIN.XLS!$EZ35="",DOCIN.XLS!EH35,IF(DOCIN.XLS!$FA35="",DOCIN.XLS!EI35,IF(DOCIN.XLS!$FB35="",DOCIN.XLS!EJ35,
IF(DOCIN.XLS!$FC35="",DOCIN.XLS!EK35,IF(DOCIN.XLS!$FD35="",DOCIN.XLS!EL35,IF(DOCIN.XLS!$FE35="",DOCIN.XLS!EM35,
IF(DOCIN.XLS!$FF35="",DOCIN.XLS!EN35,IF(DOCIN.XLS!$FG35="",DOCIN.XLS!EO35,IF(DOCIN.XLS!$FH35="",DOCIN.XLS!EP35,
IF(DOCIN.XLS!$FI35="",DOCIN.XLS!ER35,IF(DOCIN.XLS!$FJ35="",DOCIN.XLS!ER35,IF(DOCIN.XLS!$FK35="",DOCIN.XLS!ES35,
IF(DOCIN.XLS!$FL35="",DOCIN.XLS!ET35,IF(DOCIN.XLS!$FM35="",DOCIN.XLS!EU35,IF(DOCIN.XLS!$FN35="",DOCIN.XLS!EV35,
IF(DOCIN.XLS!$FO35="",DOCIN.XLS!EW35,IF(DOCIN.XLS!$FP35="",DOCIN.XLS!EX35,IF(DOCIN.XLS!$FQ35="",DOCIN.XLS!EY35,
IF(DOCIN.XLS!$FR35="",DOCIN.XLS!EZ35,IF(DOCIN.XLS!$FS35="",DOCIN.XLS!FA35,IF(DOCIN.XLS!$FT35="",DOCIN.XLS!FB35,
IF(DOCIN.XLS!$FU35="",DOCIN.XLS!FC35,IF(DOCIN.XLS!$FV35="",DOCIN.XLS!FD35,IF(DOCIN.XLS!$FW35="",DOCIN.XLS!FE35
,IF(DOCIN.XLS!$FX35="",DOCIN.XLS!FF35,IF(DOCIN.XLS!$FY35="",DOCIN.XLS!FG35,IF(DOCIN.XLS!$FZ35="",DOCIN.XLS!FH35,
IF(DOCIN.XLS!$GA35="",DOCIN.XLS!FI35,IF(DOCIN.XLS!$GB35="",DOCIN.XLS!FJ35,IF(DOCIN.XLS!$GC35="",DOCIN.XLS!FK35,
IF(DOCIN.XLS!$GD35="",DOCIN.XLS!FL35,IF(DOCIN.XLS!$GE35="",DOCIN.XLS!FM35,IF(DOCIN.XLS!$GF35="",DOCIN.XLS!FN35,
IF(DOCIN.XLS!$GG35="",DOCIN.XLS!FO35,IF(DOCIN.XLS!$GH35="",DOCIN.XLS!FP35,IF(DOCIN.XLS!$GI35="",DOCIN.XLS!FQ35,
IF(DOCIN.XLS!$GJ35="",DOCIN.XLS!FR35,DOCIN.XLS!FR35))))))))))))))))))))))))))))))))))))))))))))))
There is probably an easier way of doing this.....cheers