Defining a Range

[Cody.Alford]

Hello,

We have reports for my work. We have a terminal box sheet that lists the devices and their values. We also have air outlet sheets that shows outlets on the devices. As we build our report, we list all the devices for the job on the terminal box sheet, then proceed to "take off" the devices on the air outlet sheets. I currently have an XLOOKUP formula trying to search for the sum of the outlets on each individual device (the cells I highlighted correlate to the terminal box totals). First question is how can I return the value that is highlighted as opposed to the cell that it is currently returning. And bonus follow up question is how can I have the formula include all airoutlet sheets (as we usually have somewhere around 10 air outlet sheets per 1 terminal box sheet).

Thanks for taking time to help, and please let me know if I need to explain anything further.

[Glenn Kennedy]

I was flying for 4.5 hours today. It turns out that a boring flight is an excellent time to learn something new on Excel. So I did!

Delete all expected results in column F of your result sheet. In Cell F2, use this formula. Do not copy down, etc, etc. The results will (if your O365 is sufficiently up to date) spill down the page.

If you need to add more sheets (your sheet names are very unfriendly-looking to an outsider),just add them in this bit of the formula - comma separated.

VSTACK(vavairoutrptpg!A14:L43,'vavairoutrptpg (2)'!A14:L43).

You >>SHOULD<<< not need to do anything else. The results will/should/might spill out down column F.

As I type this, safely in flight mode somewhere over the Bay of Biscay, someone else will probably have answered this for you. But since I spent a fair bit of time over it... I'm going to share it with you anyway!!!

Please Login or Register  to view this content.

[Glenn Kennedy]

No! I'm the only taker, so far!!