INDIRECT to read F9:F34 typed in a cell.

RET79 · May 17th, 2002, 01:07 PM

Hi.

Consider this function

=INDIRECT('C:MyPathMyFolder[myfile.xls]mysheet'!A1)

which picks out the value from A1 in that file.

However, on this input sheet I have, the user would type A1 in cell C10 so the formula would read

=INDIRECT("'C:MyPathMyFolder[myfile.xls]mysheet'!"&C10)

and as C10 contains text A1 this is the same as the first equation, picks out value of A1 from myfile.xls.

Now, I want the user to be able to type A1:A10 in C10 say. Of course, this won't work:

=INDIRECT('C:MyPathMyFolder[myfile.xls]mysheet'!A1:A10)

But I have got this to work:

=SUM(INDIRECT('C:MyPathMyFolder[myfile.xls]mysheet'!A1):INDIRECT('C:MyPathMyFolder[myfile.xls]mysheet'!A10))

That works.

But, I want the user to be able to type in a range like A1:A4 or just a single cell like A22 in cell C10 of my input sheet and she indirects does the rest.

My aim is to keep the working of all this as minimal and elegant as possible, thanks in advance.

RET79

RET79 · May 17th, 2002, 01:10 PM

so I guess one idea is for a worksheet function to look at cell C10, see if there is a ":" there, if so then the SUM(INDIRECT:INDIRECT) would do.

sorry I am not sure how to go about that, assuming that is the way to do it.

RET79

RET79 · May 17th, 2002, 01:15 PM

Best I have managed to do it is to have another column. For instance to sum A1:A10 now the user would type in

A1 in cell C10 and A10 in cell D10.

Then its a case of using IF, SUM and INDIRECT on these cells, but it;s a bit messy, any better suggestions appreciated.

RET79

Aladin Akyurek · May 17th, 2002, 01:26 PM

Best I have managed to do it is to have another column. For instance to sum A1:A10 now the user would type in

A1 in cell C10 and A10 in cell D10.

Then its a case of using IF, SUM and INDIRECT on these cells, but it;s a bit messy, any better suggestions appreciated.

RET,

=SUM(INDIRECT("'C:MyPath\MyFolder\[myfile.xls]mysheet'!"&C10))

where C10 houses a value like A1:A10 entered by a user,

should simply work.

Aladin

Jack in the UK · May 17th, 2002, 01:27 PM

Hi

im a bit lost with your posts but when ever i use indirect, i name that range as X or X and indirect from there save the messing about from sheets and all you need is the book and name!

Hope this helps, but guess you knew this already.

RET79 · May 17th, 2002, 01:29 PM

Aladin,

You are absolutely right, staring me in the face wasn't it.

Many thanks,

RET79

May 17th, 2002, 01:27 PM	#5
Jack in the UK Board Regular Join Date: Feb 2002 Posts: 3,065	Hi im a bit lost with your posts but when ever i use indirect, i name that range as X or X and indirect from there save the messing about from sheets and all you need is the book and name! Hope this helps, but guess you knew this already. __________________ Free Excel based Web Toolbar available here. Jack in the UK J & R Excel Solutions "making Excel work for you"

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 17th, 2002, 01:07 PM	#1
RET79 Board Regular Join Date: Mar 2002 Location: England, UK. Posts: 526	Hi. Consider this function =INDIRECT('C:MyPathMyFolder[myfile.xls]mysheet'!A1) which picks out the value from A1 in that file. However, on this input sheet I have, the user would type A1 in cell C10 so the formula would read =INDIRECT("'C:MyPathMyFolder[myfile.xls]mysheet'!"&C10) and as C10 contains text A1 this is the same as the first equation, picks out value of A1 from myfile.xls. Now, I want the user to be able to type A1:A10 in C10 say. Of course, this won't work: =INDIRECT('C:MyPathMyFolder[myfile.xls]mysheet'!A1:A10) But I have got this to work: =SUM(INDIRECT('C:MyPathMyFolder[myfile.xls]mysheet'!A1):INDIRECT('C:MyPathMyFolder[myfile.xls]mysheet'!A10)) That works. But, I want the user to be able to type in a range like A1:A4 or just a single cell like A22 in cell C10 of my input sheet and she indirects does the rest. My aim is to keep the working of all this as minimal and elegant as possible, thanks in advance. RET79

May 17th, 2002, 01:10 PM	#2
RET79 Board Regular Join Date: Mar 2002 Location: England, UK. Posts: 526	so I guess one idea is for a worksheet function to look at cell C10, see if there is a ":" there, if so then the SUM(INDIRECT:INDIRECT) would do. sorry I am not sure how to go about that, assuming that is the way to do it. RET79

May 17th, 2002, 01:15 PM	#3
RET79 Board Regular Join Date: Mar 2002 Location: England, UK. Posts: 526	Best I have managed to do it is to have another column. For instance to sum A1:A10 now the user would type in A1 in cell C10 and A10 in cell D10. Then its a case of using IF, SUM and INDIRECT on these cells, but it;s a bit messy, any better suggestions appreciated. RET79

May 17th, 2002, 01:26 PM	#4
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,317	Best I have managed to do it is to have another column. For instance to sum A1:A10 now the user would type in A1 in cell C10 and A10 in cell D10. Then its a case of using IF, SUM and INDIRECT on these cells, but it;s a bit messy, any better suggestions appreciated. RET, =SUM(INDIRECT("'C:MyPath\MyFolder\[myfile.xls]mysheet'!"&C10)) where C10 houses a value like A1:A10 entered by a user, should simply work. Aladin

May 17th, 2002, 01:29 PM	#6
RET79 Board Regular Join Date: Mar 2002 Location: England, UK. Posts: 526	Aladin, You are absolutely right, staring me in the face wasn't it. Many thanks, RET79