This formula uses tabulated data on tab antistatic.

=INDEX(apestatic!$E$42:$L$2140,MATCH('Graphed data'!$E4,apestatic!$E$42:$E$2140,0),MATCH(H$3,apestatic!$E$42:$L$42,0))

Along the left of the tabluated data are the date&time values and across the top are unique labels. The formula itself is on another tab called Graphed data. The shape of the tabulated data can change vertically as time passes and horizontally other data columns to trend are added.

Normally one outlines the data with a mouse, but instead I would like to pass the cell addresses to the formula so that each time the data table shrinks or grows. That way I do not need to repeat selecting cells and copying the formula.

On tab apestatic, I have formulas that determine the cell address of the corners of the tabulated data, for example the corners of the data are currently

  • top of left column $E$42 -- stored in cell 'Get Data'!d1
  • btm of left column $E$2140 -- stored in cell 'Get Data'!e1
  • top of far Rt column $L$42 -- stored in cell 'Get Data'!
  • btm of far Rt column $L$2140 -- stored in cell 'Get Data'!N3
  • worksheet name apestatic -- stored in cell 'Get Data'!B2

So how do I pass those cell addresses from these cells on 'Get Data' to the formula, since they reference cells on a different worksheet? would like to make it so the data on a separate sheet can be looked up, without having to manually select the data each time. INDIRECT(B1&"!"&E1&":"&F1) gives INDIRECT("apestatic!e81:e2140") but that evaluates to #VALUE! if just placed in a cell

I was trying to use indirect( ) and concantinate & to create the ranges useing the apetatic sheetname and the cell addresses, but I haven't figured out how create the items in italics in the formula. Refering to ranges may be one issue. Suggestions?


To solve one mystery: Excel places single quotes around the sheet name if the sheet name contains spaces.

=INDEX(apestatic!$E$42:$L$2140,MATCH('Graphed data'!$E4,apestatic!$E$42:$E$2140,0),MATCH(H$3,apestatic!$E$42:$L$42,0))

I adjusted the formula to use an absolute reference for the range in the last Match. This will enable you to copy the formula down in the 'Graphed data' sheet.

You never described what the problem was, but maybe this is the answer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.