Using indirect function within an Index formula, when the data is on a separate worksheet

Question

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?

Answer 1

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.

Answer 2

You can use it using TWO INDIRECT's to get the job done:

=INDIRECT(B1&"!"&E1) : INDIRECT(B1&"!"&F1)

This will give you a live range, oddly enough.

The alternative is to set up the text in your cells that have the range corners information to be a range text. So, for example, E1 and F1 might have "E81" and "E2140". Frustratingly, they don't work with the single INDIRECT and for whatever reason, you don't wish to use the two INDIRECT approach above. So set cell... oh... G1 to have a concatenation of E1 and F1: e81:e2140. Complete with the colon in between. Now INDIRECT can get the range in a single step:

=INDIRECT(B1&"!"&G1)

I think that is the question you are asking. By the way, two things about the strings you build to present to INDIRECT:

1. As mentioned by teylyn, if a sheet name has spaces in it, it must be surrounded by a pair of single quotes. So'Get Data', not Get Data. HAPPILY, Excel is just fine with single quotes surrounding sheet names with NO spaces! So you can just add the single quotes all the time without any concern that it will ever hurt. As she noted, the opposite is definitely not true!

2. Thos single quotes and the ! character directly before the actual cell addresses can be put into the strings you are creating... wherever those actually are (you state they are in one place, then immediately show a formula looking for them elsewhere so who knows, eh?). So find the address however you do, like the $E$42 for the upper left corner (that then immediately becomes E81 in your formula...) and consider just concatenating them immediately into full strings like 'apestatic'!$E42 so that in your working formulas you just need to refer to the cell itself inside the INDIRECT (like INDIRECT('Get Data'!E1) instead of INDIRECT("'"&"Get Data"&"'"&"!"&E1)). Might make your life easier though you can certainly do it either way that seems most natural to you. Same goes for also creating ranges you know you'll want: build them in those "Get Data" sheet cells with the colon in between (like my fictional G1 cell having E81:E2140 in it) or using the two INDIRECT's and remembering the colon in between is not part of any string so it's just : and not &":"& like when making it part of a whole string for the range in the single INDIRECT method that did not work for you. Do whichever matches your mind the best because YOU are the one who has to understand it at a glance, not one of us.