Avinash, Yes, that is one the beauties of using tables -- they automatcially expand and contract. The structured table references will always refer to the applicable columns/parts of the table no matter how much or little data is in the table.
If you move columns around, just be sure to update the vlookup and vbavlookup formulas since they refer to colums by column index rather than name. If you use regular A1 notation anywhere instead of the structured references, then it may not refer to the whole table range.. best to stick to structured references to enjoy that auto-expansion benefit. Asa From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Avinash Sent: Saturday, March 24, 2012 8:42 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Re: Arry Formula help Dear Asa, Thanks A lotttttttttttttttt it works well. Both the formula and the results are working fine plus it is not time consuming. thank you very much . but i have just one question if i put additional data in to the table then also it will work as it is or do i need to rebuilt all the formulas cuz the data in the workbook may get updated once in a month or something. like new user addition or deletion so can u please tell me do i need to rebuilt the all the formulas or it wlll automatically extend the range of the table i mean new data also available in results?? Regards, Avinash. On Saturday, March 24, 2012 7:26:14 PM UTC+5:30, Asa R. wrote: Avinash my friend, My pleasure. As to the formula for C2… You should get the same result for formula #1 and formula #3. The downside with those is that they refer to the Responsibility column using A1 notation, which might lead to errors later. The formula I provided uses notation only available in Excel 2010, I just learned. The @ notation is a shorthand for referencing a column in the current row. More info and other 2007/2010 table differences at http://blogs.office.com/b/ <http://blogs.office.com/b/microsoft-excel/archive/2010/01/25/table-improvements-in-excel-2010.aspx> microsoft-excel/archive/2010/01/25/table-improvements-in-excel-2010.aspx . You can try the following which I believe should work: This is the Excel 2007 version of the formula I provided: =tbl_txtList[[#This Row],[UserMap]]&"--"&SUMPRODUCT(--([Responsibility]:tbl_txtList[[#This Row],[Responsibility]]=tbl_txtList[[#This Row],[Responsibility]])) But! Don't bother with it. . . I could have sworn it worked, but it doesn't seem to give the correct result. I knew I was taking a shortcut on the notation a bit, but shucks I thought I double checked it and it worked. Here, this version really should work: =tbl_txtList[[#This Row],[UserMap]]&"--"&SUMPRODUCT(--(INDEX([Responsibility],1):tbl_txtList[[#This Row],[Responsibility]]=tbl_txtList[[#This Row],[Responsibility]])) In Excel 2010 notation it's easier to read: =[@UserMap]&"--"&SUMPRODUCT(--(INDEX([Responsibility],1):[@Responsibility]=[@Responsibility])) But I also recommend against using SUMPRODUCT for this. My preference is for this COUNTIF version, which is faster: =tbl_txtList[[#This Row],[UserMap]]&"--"&COUNTIF(INDEX([Responsibility],1):tbl_txtList[[#This Row],[Responsibility]],tbl_txtList[[#This Row],[Responsibility]]) The Excel 2010 notation would be: =[@UserMap]&"--"&COUNTIF(INDEX([Responsibility],1):[@Responsibility],[@Responsibility]) In the Setts sheet, you have two problems with the Report Key and related data. (1) You have 8 formulas in the Report Key column (cells E8:E15). For vbaVlookup to work as intended, you need to use just one formula and array enter it over that entire range (or whatever vertical range is the maximum number of values you'll need to retrieve). Highlight all 8 cells, ctrl-click in the top cell (E8) if it's not already the active cell, hit F2 or click into the formula bar, then hit Ctrl-Shift-Enter to confirm your formula as an array formula (and any time you edit the formula, you must hit ctrl-shift-enter when done). (2) You use the following formula in those cells: =vbaVlookup($D$2,tbl_txtList,3,"A") The "A" at the end of the formula indicates that you want the result returned in a horizontal array. The default is for a vertical array (or you can specify "V"), which is what you need since you have a vertical range of cells. The formula should change to: =vbaVlookup($D$2,tbl_txtList,3) Asa From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com <mailto:excel-macros@googlegroups.com> ] On Behalf Of Avinash Sent: Friday, March 23, 2012 7:34 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Re: Arry Formula help Dear Asa, thanks for your help and using your technique i've found new ways to perform new task and more use of tables but can you please see the below formula which you have given cuz it is giving me diffrent values 1 - Actual formula ************ =$A2&"--"&SUMPRODUCT(--($B$2:$B2=$B2)) 2 - your formula ************ in 'txt List'!C2: =[@UserMap]&"--"&SUMPRODUCT(--([Responsibility]:[@Responsibility]=[@Responsibility])) 3 - Formula which i have used ************ =tbl_txtList[[#This Row],[UserMap]]&"--"&SUMPRODUCT(--($B$2:$B2=$B2)) in my workbook i used formula number 3 which is also giving me the proper result so can you please let me know which formula should i use ??? and is it formula number 3 is correct?? also i want to tell you that your table technique is realy awesome and it is very fast but can you please look in setts sub sheet and see column G, H and I cuz after doing all of this i want final results in colume F (currently explained in column I current values are highlited in Column G Required Values are in H and bassed on the required values results are highlited in column I. please open the attachement workbook for better understanding.. and thanks in advanced for your support. Regards, Avinash -- FORUM RULES (986+ 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 (986+ 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 (986+ 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
