Is it possible to selectively convert VLOOKUP results to values in a budget.

[rtater375]

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.  

[#2]

@rtater375 

You've had over 80 views and no responses. I suspect that the lack of responses is due to the confusing nature of what you describe. So let me ask if it's possible for you to post a copy of the spreadsheet(s) involved, to supplement your verbal description. You might be able to attach a file or two in the area below the text box where you create your entries in this forum. If you're using the full text mode, that area looks like this

If that doesn't work, you can post files on OneDrive or GoogleDrive (or equivalent) and paste a link here that grants access.

All of that having been said, it's likely the case that VLOOKUP is not the most effective way to get data into a summary of expenses and the like for a budget analysis. Depending on the nature of the raw data, a Pivot Table can do the full summary of a year's worth of data, taking care of all the sifting and sorting. I'm attaching a simple example of the Pivot Table as the sole tool to summarize transactional data. Bear in mind: this is a simple example. Pivot Tables have lots of flexibility to help summarize a table of raw data.

You also are working with an old version of Excel. If you can do so, I'd recommend bringing your software up-to-date; there are many new functions and abilities. 

You'd help us help you, though, by sharing a copy of the data you're working with, as noted above.