Conditionally Extending the Reference of a String

[apor]

Hello everyone,

I am currently working with a sheet that extracts data from a pdf that i upload using queries & connections.

the string looks like this in cell B2
B2=IF(LEN(INDIRECT("Page002!C12"))=11,INDIRECT("Page002!C12"), IF(LEN(INDIRECT("Page002!D12"))=11,INDIRECT("Page002!D12"), IF(LEN(INDIRECT("Page002!E12"))=11,INDIRECT("Page002!E12"),"") ) )
B3=IF(LEN(INDIRECT("Page002!C22"))=11,INDIRECT("Page002!C22"), IF(LEN(INDIRECT("Page002!D22"))=11,INDIRECT("Page002!D22"), IF(LEN(INDIRECT("Page002!E22"))=11,INDIRECT("Page002!E22"),"") ) )
B4=IF(LEN(INDIRECT("Page002!C32"))=11,INDIRECT("Page002!C32"), IF(LEN(INDIRECT("Page002!D32"))=11,INDIRECT("Page002!D32"), IF(LEN(INDIRECT("Page002!E32"))=11,INDIRECT("Page002!E32"),"") ) )
B5=IF(LEN(INDIRECT("Page002!C42"))=11,INDIRECT("Page002!C42"), IF(LEN(INDIRECT("Page002!D42"))=11,INDIRECT("Page002!D42"), IF(LEN(INDIRECT("Page002!E42"))=11,INDIRECT("Page002!E42"),"") ) )
it is somewhat laborios and takes the data from either cell c12/22/32/42, cell d12/22/32/42 or cell e12/22/32/42 from the sheet with the name Page002 if the length of the cell is 11.

then, starting at B6 to B9, it starts over, only with the sheet called Page003
B6==IF(LEN(INDIRECT("Page003!C12"))=11,INDIRECT("Page003!C12"), IF(LEN(INDIRECT("Page003!D12"))=11,INDIRECT("Page003!D12"), IF(LEN(INDIRECT("Page003!E12"))=11,INDIRECT("Page003!E12"),"") ) )
an so on....

i have to extende the string up until Page100, hwoever, i have been doing this by hand and it takes hours. i need to copy the strings of B2-B5 and paste them, and then change the Sheet Reference by hand.

Is there a way to possibly shorten the string or automatically extend it down?

 

[OaklandJim]

This sounds doable -- propagating the formulas -- but I cannot tell without more. Might you post a workbook with fake but realistic data? Do using the link icon above the message area. Put file on Dropbox, 1Drive, Google Drive etc.