Posts by mhabib

  • [Solved] VBA: getting letter names of columns

    Hello Jen!


    Try the following steps


    1. Make a lookup table in sheet3 with the numbers 1 to 256 in column A (one for each column label in a sheet). In the next column, fill in the column labels (A, B, C, ... AA, AB, ..., IV). Call this table Column_Labels


    (This can be done much faster than most people would imagine. Let me know if you need the steps.)


    2. In sheet2 write the following formula. (I'm assuming that your array needs to be linked to cell A1 in sheet1 (Sheet1!$A$1).


    =Sheet1!$A$1&VLOOKUP(COLUMN(),Column_Labels,2,0)&ROW()


    This will concatenate the formula's host cell reference to the list item. Whenever you shift your array to another block of cells, the result will reflect the new cell references.


    HTH


    M. Habib


    (PS Excel file attached - took about 5 minutes to create.)

  • Ua pivot table using a macro

    Hi there!


    You can avoid the range problem by extending your list range to include the 65536th row (Shift + CTRL + DownArrow). This will accommodate any new data in your list. However, additional fields will require a re-declaration of the list range. (Beware, the default data operation will become COUNT rather than SUM. But this can be fixed in Field Properties)



    You could also create a macro that assigns a name to your list range by starting at the top left corner of the data and extending till the end of the sheet (Shift + CTRL + End). In this case you can use the range name rather than range coordinates in the wizard's list range .


    HTH


    m. habib