Named ranges

    I am doing a spreadsheet that has a sheet with some stock on it. I have a column entitled 'Product' which contains motherboard products. I have named that range as motherboards.


    I have a macro that allows the user to add an item to the motherboard list from a separate sheet; but the named range doesn't continue with the addition.


    How can I make it so the named range expands with every addtion to the sheet?


    I am really stuck so any help would be appreciated!

    "If it ain't the truth it ain't the blues"

    Welcome to Ozgrid


    Assuming your Product column is in Sheet1 column A, you would insert the following formula into the refers to box of the Insert>Name>Define menu


    =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)


    You just need to adjust that to suit your sheet/column names


    Be aware that the column can contain no blank cells for this to work.


    Hope it helps

    My product entries start at B2 and go down. When I use the macro, it inserts a row between the headings and the 1st entry, so would this still work?

    "If it ain't the truth it ain't the blues"

    Yes, so long as you don't ever delete cell A1... otherwise you will get a #REF error


    Inserting rows anywhere from A2 downwards will be OK


    :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login