Dear Ankit, Excel INDIRECT function takes a text string and converts this into a cell reference.
Excel does not understand the text string "B1" to mean a reference to the cell B1. Therefore, if you extract or build up a reference to a cell or range using text, you will need to use the INDIRECT function to convert this into a reference that Excel can understand. The format of the function is : INDIRECT( *Ref_text*, *A1* ) Where the arguments are as follows : *Ref_text* - The text describing the reference *A1* - An optional logical argument that defines the style of the *Ref_text* reference. This can be either : - True - to denote that the reference is in A1 style - False - to denote that the reference is in R1C1 style If this argument is omitted, it takes on the default value "True" -- On Fri, Nov 18, 2011 at 12:54 PM, Ankit Agrawal <ankit.agrawal...@gmail.com>wrote: > Hi, > > Plz tell me what is the use of indirect function & what it can do? > > Regards, > Ankit > > > On Tue, Nov 15, 2011 at 2:49 PM, NOORAIN ANSARI > <noorain.ans...@gmail.com>wrote: > >> Dear Smitha, >> >> Please find & see attached sheet with example of below formula. >> >> >> -- >> Thanks & regards, >> Noorain Ansari >> *http://excelmacroworld.blogspot.com/*<http://excelmacroworld.blogspot.com/> >> *http://noorain-ansari.blogspot.com/*<http://noorain-ansari.blogspot.com/> >> >> On Tue, Nov 15, 2011 at 12:13 PM, smitha.kumari < >> smitha.kum...@speridian.com> wrote: >> >>> Hi,**** >>> >>> ** ** >>> >>> Any one please explain why these quotes are used in this formula**** >>> >>> ** ** >>> >>> =SUMIF(INDIRECT("'"&B$5&"'!A:A"),$A6,INDIRECT("'"&B$5&"'!I:I"))**** >>> >>> ** ** >>> >>> Regards**** >>> >>> Smitha **** >>> >>> ** ** >>> >>> -- >>> FORUM RULES (934+ members already BANNED for violation) >>> >>> 1) Use concise, accurate thread titles. Poor thread titles, like Please >>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice >>> will not get quick attention or may not be answered. >>> >>> 2) Don't post a question in the thread of another member. >>> >>> 3) Don't post questions regarding breaking or bypassing any security >>> measure. >>> >>> 4) Acknowledge the responses you receive, good or bad. >>> >>> 5) Cross-promotion of, or links to, forums competitive to this forum in >>> signatures are prohibited. >>> >>> NOTE : Don't ever post personal or confidential data in a workbook. >>> Forum owners and members are not responsible for any loss. >>> >>> >>> ------------------------------------------------------------------------------------------------------ >>> To post to this group, send email to excel-macros@googlegroups.com >>> >> >> >> -- >> FORUM RULES (934+ members already BANNED for violation) >> >> 1) Use concise, accurate thread titles. Poor thread titles, like Please >> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice >> will not get quick attention or may not be answered. >> >> 2) Don't post a question in the thread of another member. >> >> 3) Don't post questions regarding breaking or bypassing any security >> measure. >> >> 4) Acknowledge the responses you receive, good or bad. >> >> 5) Cross-promotion of, or links to, forums competitive to this forum in >> signatures are prohibited. >> >> NOTE : Don't ever post personal or confidential data in a workbook. Forum >> owners and members are not responsible for any loss. >> >> >> ------------------------------------------------------------------------------------------------------ >> To post to this group, send email to excel-macros@googlegroups.com >> > > -- > FORUM RULES (934+ members already BANNED for violation) > > 1) Use concise, accurate thread titles. Poor thread titles, like Please > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice > will not get quick attention or may not be answered. > > 2) Don't post a question in the thread of another member. > > 3) Don't post questions regarding breaking or bypassing any security > measure. > > 4) Acknowledge the responses you receive, good or bad. > > 5) Cross-promotion of, or links to, forums competitive to this forum in > signatures are prohibited. > > NOTE : Don't ever post personal or confidential data in a workbook. Forum > owners and members are not responsible for any loss. > > > ------------------------------------------------------------------------------------------------------ > To post to this group, send email to excel-macros@googlegroups.com > -- Thanks & regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*<http://excelmacroworld.blogspot.com/> *http://noorain-ansari.blogspot.com/* <http://noorain-ansari.blogspot.com/> -- FORUM RULES (934+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ------------------------------------------------------------------------------------------------------ To post to this group, send email to excel-macros@googlegroups.com
