Cell default Value

[Stidd]

Hi

I would like to be able to define a default value for a field in an excel table that carrys forward to new rows in the same way formulas do. Any help appreciated!

Regards,

Si

 

[MrKowz]

Try setting up that value as a formula.

For example, if you want "US" to appear in all cells in a column, use the formula ="US"

 

[Stidd]

Thanks for the reply MrKowz. Unfortunately that doesn't work unless I am doing it wrong. The formulas only carry forwards if there is a dynamic range invloved from a table field like [Column1] or [@Column1]. Simple formulas don't seem to carry forwards...

 

[MrKowz]

Thanks for the reply MrKowz. Unfortunately that doesn't work unless I am doing it wrong. The formulas only carry forwards if there is a dynamic range invloved from a table field like [Column1] or [@Column1]. Simple formulas don't seem to carry forwards...

What version of Excel are you using? It seems to work on Excel 2010 on my end.

 

[Stidd]

Thanks for you help. I'm using 2010 too. I'm confused. I've had a play now and you are right that it does work if put ="US" as the first entry in a given field. You can then even change entrys as required.

However, if there are existing entries in the field in previous rows putting ="US" in the next blank record and expecting it to carry forwards doesn't work. Unfortunately, that is what I am trying to do My table already has loads of entries in it. I am going to try just cutting the rows out, putting in the default formula and then pasting the old entries back in... but there must be a better way?

I also wanted to auto number each new entry uniquely as Access would do. I can do OFFSET() and ROW() variations but these all change when you sort by different columns. Any thoughts?

 

[dcchrist]

Thanks for you help. I'm using 2010 too. I'm confused. I've had a play now and you are right that it does work if put ="US" as the first entry in a given field. You can then even change entrys as required.

However, if there are existing entries in the field in previous rows putting ="US" in the next blank record and expecting it to carry forwards doesn't work. Unfortunately, that is what I am trying to do My table already has loads of entries in it. I am going to try just cutting the rows out, putting in the default formula and then pasting the old entries back in... but there must be a better way?

I also wanted to auto number each new entry uniquely as Access would do. I can do OFFSET() and ROW() variations but these all change when you sort by different columns. Any thoughts?

Try converting your table back to a range and then converting it back to a table. This has worked for me in the past. If you are using macros that reference that table name, remember to rename your table to the same table name you used before.