Can Anyone Explain This?

[fendy]

I have a worksheet where I enter values in column F. Column H has a vlookup formula to look up the value in column F. When I enter a new value on a blank row in column F the formula in the H column is automatically copying itself down. I have no clue how this is happening, but I would like to be able to duplicate it.

Thanks

 

[Scott Huish]

On the sheet where this is happening, right click the sheet tab and click on View Code, is there code in there?

 

[PaddyD]

review the excel help fle for 'Guidelines for creating a list on a worksheet', in particular the link to 'Turn on or off extended formats and formulas'

 

[fendy]

There is no code in the sheet.

 

[fendy]

review the excel help fle for 'Guidelines for creating a list on a worksheet', in particular the link to 'Turn on or off extended formats and formulas'

Extended formats and formulas is on and always has been. I have used this sheet for months and never had it do this until today. I would love to figure out how to make all my sheets do the same thing.

 

[PaddyD]

have you read the help file entry:

When you turn on extended formats and formulas, Microsoft Excel automatically formats new data that you type at the end of a list to match the preceding rows and automatically copies formulas that repeat in every row. To be extended, formats and formulas must appear in at least three of the five list rows preceding the new row.

..anyway, in my experience the functionality is temperamental to say the least. This is because excel only does this when it sees a list, and historically it's not been that good a spotting them. In Excel 2003, the list management functions have been much improved - e.g. the addition of a data | list | create list option