Hi.  Using Excel 2013 on Windows 10.  Every month I need to pull in new values into a budget (rows = budget category line items, and subtotals and grand totals; columns = months) column using VLOOKUP and then convert those VLOOKUP results to values so that they remain fixed when I run VLOOKUP for the subsequent month at a later date in the next column to the right.  However, within each month/column there are rows with the SUBTOTAL and SUM functions that I would like to preserve without having to retype them after converting all the monthly data in a column to values.  If I convert the entire column to values, I lose the SUBTOTAL and SUM formulae as well as the VLOOKUP formulae.  If I try to convert just the cells with VLOOKUP results, it is very inefficient because of the number of category line items (cells) involved.  

I have tried to protect just the SUBTOTAL and SUM cells in the column, but I find if any cells in a column are protected/locked, then I can’t run the COPY, PASTE AS VALUE on the entire column in one go, which is my aim (although PROTECT operating in that way makes some sense). IOW, I end up having to just select the cells that have VLOOKUP and convert them in isolation, a very time consuming process because of the number of different budget line items that are being subtotalled in each budget category. 

Is there a way to do this more easily?  I am not very familiar with VBA, but will learn it if someone thinks that is the most likely way to automate this.  

​Hi.  Using Excel 2013 on Windows 10.  Every month I need to pull in new values into a budget (rows = budget category line items, and subtotals and grand totals; columns = months) column using VLOOKUP and then convert those VLOOKUP results to values so that they remain fixed when I run VLOOKUP for the subsequent month at a later date in the next column to the right.  However, within each month/column there are rows with the SUBTOTAL and SUM functions that I would like to preserve without having to retype them after converting all the monthly data in a column to values.  If I convert the entire column to values, I lose the SUBTOTAL and SUM formulae as well as the VLOOKUP formulae.  If I try to convert just the cells with VLOOKUP results, it is very inefficient because of the number of category line items (cells) involved.  I have tried to protect just the SUBTOTAL and SUM cells in the column, but I find if any cells in a column are protected/locked, then I can’t run the COPY, PASTE AS VALUE on the entire column in one go, which is my aim (although PROTECT operating in that way makes some sense). IOW, I end up having to just select the cells that have VLOOKUP and convert them in isolation, a very time consuming process because of the number of different budget line items that are being subtotalled in each budget category.  Is there a way to do this more easily?  I am not very familiar with VBA, but will learn it if someone thinks that is the most likely way to automate this.      Read More

​