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


    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!