Do you ever hold down the Ctrl key to select multiple non adjacent cells or ranges? I’ve found a way to select multiple non adjacent ranges (any sheet) by pasting a formula into Excel’s Name Box!
Exploring Excel
There are other possible solutions (i.e. vba, hyperlinks?) but I enjoyed exploring this idea in Excel.
How It’s Useful
Holding down the Ctrl key while selecting cells or ranges is tedious. My solution allows you to select multiple non adjacent ranges (any sheet) based on your criteria.
How It Works
Any valid cell or range reference can be pasted into the Name Box. Examples:
- ‘StateData(4)’!D8
- ‘StateData(1)’!F15:M13
- ‘StateData(8)’!D16:J12, ‘StateData(8)’!F6:I2, ‘StateData(8)’!H21:K19
It can be text like above or formulas that dynamically create these references! The formula below creates a reference to this: ‘StateData(1)’!F15:M13
Download My Excel File
To really understand how it works download my Excel file and audit the formulas!
Homage To The Square
In March I visited Guggenheim museum in Manhattan. Some of the art was beautiful. Then there was Homage To The Square exhibit by Josef Albers. Paintings of squares!?
Maybe I should’ve called this post “Homage To Multiple Non Adjacent Cells And Ranges!“.
More about Homage To The Square: Guggenheim, KhanAcademy, Albersfoundation.
About me? I’ve been a Data Analyst since 2001.
I love Microsoft Excel 🙂
I live in Markham Ontario and work in Toronto.
Visit my blog, my recommended Excel training.