Thanks Ankur, Explanation of Offset function:- =OFFSET(Sheet1!$F$1,0,0,COUNTA(Sheet1!$F:$F),COUNTA(Sheet1!$1:$1))
In the above formula, last two COUNTAs are increasing the range downwards and toward right. So ultimately the range is increasing from F1 to left, till row 1 has data and to down, till Column F has data. If you have heading of the row/column, then you might want to subtract 1 from the respective COUNTAs. First Counta is for downward range and Second Counta is for range towards right, starting from F1. Let me know in case you find any difficulty to understand the same. Best Regards, -- DILIP KUMAR PANDEY MBA-HR,B COM(Hons.),BCA Mobile: +91 9810929744 dilipan...@gmail.com dilipan...@yahoo.com New Delhi - 110062 On Wed, Jan 13, 2010 at 11:03 AM, ankur <ankurpande...@gmail.com> wrote: > hi Dilip Sir, > > Very Good Use of Offset Function, > > Can You Please Explain How this work here. > > On 1/12/10, Dilip Pandey <dilipan...@gmail.com> wrote: > > Hi, > > > > Using Offset function, this task can be done. > > I have attached a sample file in which new added columns will > automatically > > get covered in the lookup name being used. > > > > Best Regards, > > -- > > DILIP KUMAR PANDEY > > MBA-HR,B COM(Hons.),BCA > > Mobile: +91 9810929744 > > dilipan...@gmail.com > > dilipan...@yahoo.com > > New Delhi - 110062 > > > > > > On Mon, Jan 11, 2010 at 7:49 PM, bas001 <mxbas...@gmail.com> wrote: > > > >> Hello, I need to alter the array of the lookup table e.g. adding a new > >> column to the array. I am using the lookup name. I there a way to > >> refresh the name so it it contain the new column . Thanks > >> > >> -- > >> > >> > ---------------------------------------------------------------------------------- > >> Some important links for excel users: > >> 1. Follow us in TWITTER for tips tricks and links : > >> http://twitter.com/exceldailytip > >> 2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at > >> http://www.excelitems.com > >> 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 > >> If you find any spam message in the group, please send an email to: > >> Ayush Jain @ jainayus...@gmail.com > >> <><><><><><><><><><><><><><><><><><><><><><> > >> HELP US GROW !! > >> > >> We reach over 6,500 subscribers worldwide and receive many nice notes > >> about > >> the learning and support from the group.Let friends and co-workers know > >> they > >> can subscribe to group at > >> http://groups.google.com/group/excel-macros/subscribe > >> > >> > > > > > -- > Have A Nice Time & Enjoy Life > > Regards: > CMA Ankur Pandey > (Someone Different) > > I'm not the best but i'm not like the rest~~ > > -- > > ---------------------------------------------------------------------------------- > Some important links for excel users: > 1. Follow us in TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at > http://www.excelitems.com > 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 > If you find any spam message in the group, please send an email to: > Ayush Jain @ jainayus...@gmail.com > <><><><><><><><><><><><><><><><><><><><><><> > HELP US GROW !! > > We reach over 6,500 subscribers worldwide and receive many nice notes about > the learning and support from the group.Let friends and co-workers know they > can subscribe to group at > http://groups.google.com/group/excel-macros/subscribe > >
-- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us in TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 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 If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
