Can I use indirect function as a formula in my case! Please suggest.
Sent on my BlackBerry® from Vodafone -----Original Message----- From: Bé Trần Văn <betnmtdongna...@gmail.com> Sender: excel-macros@googlegroups.com Date: Thu, 6 Oct 2011 01:35:52 To: <excel-macros@googlegroups.com> Reply-To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Linking same format sheet 2011/10/5 Sam Mathai Chacko <samde...@gmail.com> > The only formula that is 'LIKE' indirect, is indirect itself. Don has also > suggested the same. Are you indirectly asking 'HOW' to apply an INDIRECT > formula? > > Sam > > > On Wed, Oct 5, 2011 at 8:40 PM, <chhajersand...@gmail.com> wrote: > >> I don't have any worksheet readily available with me now. >> But I am trying to explain it with example: >> I have a worksheet 1 with below mentioned sheets: >> Annex 1 >> Annex 2 >> And so on... >> >> I have anothe sheet say worksheet 2 with same name sheets viZ >> Annex 1 >> Annex 2 >> And so on... >> >> Now in annex 1 of worksheet 1 I have data as below: >> Column 1. Column 2 >> Row 1 Fixed assets. 25000 >> >> Row 2 Investment. 50000 >> >> And so on >> >> In annex 2 I have data as below >> Column 1. Column 2 >> Row 1 direct income 25000 >> >> Row 2 direct expenses 50000 >> >> And so on... >> >> >> Now in annex 1 of worksheet 2 I have data as below: >> Column 1. Column 2 >> Row 1 Fixed assets. >> >> Row 2 Investment. >> >> And so on >> >> In annex 2 I have data as below >> Column 1. Column 2 >> Row 1 direct income >> >> Row 2 direct expenses >> >> And so on...for all annexure >> >> Column 3,4 and towards right side all sheets (name of the sheets are >> annexure 1,2 and so on) of worksheet 2 contains old linked data which I >> required and do not change. >> >> My object is to link blank column 2 of worksheet 2 of every sheets with a >> formula where name of the worksheet becomes variable..so that I can use a >> single formula for every sheet. >> >> Hope I can make u all understood. >> >> Thanks for taking pain and for all ur co operation. >> >> Sandeep Chhajer. >> >> >> Sent on my BlackBerry® from Vodafone >> >> -----Original Message----- >> From: "dguillett1" <dguille...@gmail.com> >> Sender: excel-macros@googlegroups.com >> Date: Wed, 5 Oct 2011 09:42:55 >> To: <excel-macros@googlegroups.com> >> Reply-To: excel-macros@googlegroups.com >> Subject: Re: $$Excel-Macros$$ Linking same format sheet >> >> I, for one, still do not understand your requirement. Perhaps attaching a >> file or two would be helpful. >> >> -----Original Message----- >> From: chhajersand...@gmail.com >> Sent: Wednesday, October 05, 2011 9:17 AM >> To: excel-macros@googlegroups.com >> Subject: Re: $$Excel-Macros$$ Linking same format sheet >> >> Dear all >> actually I get daily sheet from branches (they send day wise) and need to >> update all my annexures (I need it to be kept for every day with the same >> format) accordingly. >> So please suggest any other furmula if indirect dose not work. >> >> Sent on my BlackBerry® from Vodafone >> >> -----Original Message----- >> From: "dguillett1" <dguille...@gmail.com> >> Sender: excel-macros@googlegroups.com >> Date: Wed, 5 Oct 2011 09:03:57 >> To: <excel-macros@googlegroups.com> >> Reply-To: excel-macros@googlegroups.com >> Subject: Re: $$Excel-Macros$$ Linking same format sheet >> >> >> Indirect may only be used with OPEN workbooks. >> Instead, have your formula to the closed workbook and use edit/replace to >> modify formulas. >> >> -----Original Message----- >> From: chhajersand...@gmail.com >> Sent: Wednesday, October 05, 2011 7:16 AM >> To: excel-macros@googlegroups.com >> Subject: $$Excel-Macros$$ Linking same format sheet >> >> Dear excel gurus >> >> I need to link two work sheet with same format. >> Like I have to link annexure 1 (name of a sheet) with corresponding figure >> in another workbook where also the sheet named annexure 1(I have 25 sheets >> with name annexure 1 to 25 and have to link all the sheets with >> corresponding name sheet of another file all the format is same in both of >> the workbook. Please provide with a solution) >> >> I want to use "indirect" kind of formula so that same formula can be used >> for linking all the sheets. >> >> Thanking you in advance. >> Sent on my BlackBerryŽ from Vodafone >> >> -- >> >> ---------------------------------------------------------------------------------- >> Some important links for excel users: >> 1. Follow us on TWITTER for tips tricks and links : >> http://twitter.com/exceldailytip >> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 >> 3. Excel tutorials at http://www.excel-macros.blogspot.com >> 4. Learn VBA Macros at http://www.quickvba.blogspot.com >> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >> >> To post to this group, send email to excel-macros@googlegroups.com >> >> <><><><><><><><><><><><><><><><><><><><><><> >> Like our page on facebook , Just follow below link >> http://www.facebook.com/discussexcel >> >> -- >> >> ---------------------------------------------------------------------------------- >> Some important links for excel users: >> 1. Follow us on TWITTER for tips tricks and links : >> http://twitter.com/exceldailytip >> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 >> 3. Excel tutorials at http://www.excel-macros.blogspot.com >> 4. Learn VBA Macros at http://www.quickvba.blogspot.com >> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >> >> To post to this group, send email to excel-macros@googlegroups.com >> >> <><><><><><><><><><><><><><><><><><><><><><> >> Like our page on facebook , Just follow below link >> http://www.facebook.com/discussexcel >> >> -- >> >> ---------------------------------------------------------------------------------- >> Some important links for excel users: >> 1. Follow us on TWITTER for tips tricks and links : >> http://twitter.com/exceldailytip >> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 >> 3. Excel tutorials at http://www.excel-macros.blogspot.com >> 4. Learn VBA Macros at http://www.quickvba.blogspot.com >> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >> >> To post to this group, send email to excel-macros@googlegroups.com >> >> <><><><><><><><><><><><><><><><><><><><><><> >> Like our page on facebook , Just follow below link >> http://www.facebook.com/discussexcel >> >> -- >> >> ---------------------------------------------------------------------------------- >> Some important links for excel users: >> 1. Follow us on TWITTER for tips tricks and links : >> http://twitter.com/exceldailytip >> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 >> 3. Excel tutorials at http://www.excel-macros.blogspot.com >> 4. Learn VBA Macros at http://www.quickvba.blogspot.com >> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >> >> To post to this group, send email to excel-macros@googlegroups.com >> >> <><><><><><><><><><><><><><><><><><><><><><> >> Like our page on facebook , Just follow below link >> http://www.facebook.com/discussexcel >> >> -- >> >> ---------------------------------------------------------------------------------- >> Some important links for excel users: >> 1. Follow us on TWITTER for tips tricks and links : >> http://twitter.com/exceldailytip >> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 >> 3. Excel tutorials at http://www.excel-macros.blogspot.com >> 4. Learn VBA Macros at http://www.quickvba.blogspot.com >> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >> >> To post to this group, send email to excel-macros@googlegroups.com >> >> <><><><><><><><><><><><><><><><><><><><><><> >> Like our page on facebook , Just follow below link >> http://www.facebook.com/discussexcel >> > > > > -- > Sam Mathai Chacko > > -- > > ---------------------------------------------------------------------------------- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/discussexcel > *Create a link to sheet.xls (see attachment)* *01. Create sheet: To create a link I created the following sheet.* - *Sheet 12 months named Link details:* I used to make links to each saint. - *Sheet named A total of 12 months:* I create a link to sum each month. - *The sheet Month1, Month2 ... .... Month12:* used to enter details of goods each month. In the sheet every month, in line 5 I use the formula Sum (example: Sum B5 to B100, because each month I enter the number below 100, the goal to automate the results). If each month the amount entered is greater than 100, you can use the formula B5 to B200. *02: Create a link:* *a. Create a link to the Sheet named A total of 12 months:* In Cell B5 Sum recipe I created from B5: B16 and Fill to the right to L5 (12-month review purposes). - Create a link to Month1: by typing in B5 = sign, followed by I click on the sheet named Month1 and click on cell B4 is finished I hit ENTER. Sequentially so I make cell B6 to B16 (for Month2 to Month12), next I click on Cell B5 hold down Shift and click on the B16, then I drop down the Shift key and fill right to the L16, is made to Link total of 12 Month Results. *b. Create a link to the Sheet named Link details 12 months:* *- Preparation:* I choose the columns M and in blue to mark (goal when I link the Month sheet and fill right to the position marked blue brown is stopped). TT in my column automatically numbered from A4 to A1182 (A1182 I estimate the 12 months to enter only about 1000 lines), the purpose of the automatic numbering from A4 to the A1182 is that when I link to each month detailing then know that results of the first month by looking at it the next 0. Creating a Link: - *Start link:* In Cell A4 Sheet of 12 months named Link details I enter an =, the next I click on the sheet name and click on cell A5 Month1 I hit ENTER (I = month1 results! A5 ), followed by my right side double click to automatically fill in the next line, then click Cell A4 hold Shift and click the mouse on the vertical scroll bar arrows and slowly roll down to the A62 I saw number 59 click at A62 (automatic numbering purposes as I applied here, whenever I see the number 0 is the last line of Month1 no data sheet), then I drop down the Shift key and fill right to the M62 (I have marked green), here is the link to complete Month1 Sheet. - Next I made link to Sheet Month2, by the way I type on the sign = 0 (A63), the next I click on the sheet name and click on cell A5 Month2 I hit ENTER and perform like it did in sheet Month1. Likewise I make links to the left Month. -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
