VSTACK

Question

hi all

I have used VStack for the first time and found it a massive timesaver for a file I have had to put together.

The issue I have found - unless the files I have 'stacked' are open too, when I open the consolidated file with the formula in, it just displays a #REF error. When I open the files, it works again. Is this how it works and, if so, how do I then send the final consolidated file to other people?

TIA, Louise

Answer 1

Hi,

When the source workbook is closed, the VSTACK() function still works. If you see an error, it is because of something else - not because of the VSTACK function.

Answer 2

Hi

Well, I thought of doing that but then if any of the original data changes, my consolidated copy won't. This is the issue I had because some of the data was amended the day after but my consolidated workbook didn't replicate those changes.

It just doesn't make sense that when I consolidate them, I then have to have them all open any way - kind of defeats the whole object in a way.

Thanks, Louise

Answer 3

P.S. With the August 28, 2023 update, you can use the keyboard shortcut Ctrl+Shift+V to paste values only.

Answer 4

You could replace the formula result with its values:

• Select the entire spill range.
• Copy it.
• Click the lower half of the Paste button on the Home tab of the ribbon.
• Select Values or press V.
• Save the workbook under another name, to avoid overwriting the original version.