Reference Single Column from Sheet Range Reference

BarneeL

Is there a way I can easily reference a single column from a wide Sheet Range Reference?

I have 50 columns on a separate sheet, each one I need to run a COUNTIF() on.

Rather than creating 50 range references across sheets, is there a way I can reference the whole range, as you would in a VLOOKUP, but then reference the whole column rather than just an individual cell?

 

Thanks for any advice!

Paul Newcome

I can't think of a way to do this exactly as you are looking for, but there may be other options or workarounds depending on your specific case.

 

Is it going to be the same COUNTIF() for all of the columns? How is the data being displayed on your metrics sheet?

BarneeL

Thanks for the reply!

 

It will be the same set of COUNTIF()s against each column. There are 30 COUNTIF()s per column, displayed horizontally (although the criteria are auto generated with a Formula once we have the range reference working). So referencing the range is the only issue with the way I'm doing it at the moment.

 

If you can think of an alternative way I'd be open to see if we can structure it differently.

Paul Newcome

Would it be possible to set up the metrics grid on the data sheet and then cell link?

 

Once you enter your formulas in the first column on the data sheet, you could dragfill right to auto-update the column references in the formulas. You can then (as long as the grids are the same size and shape) cell link the entire grid to your Metrics sheet.

 

Does this sound like something that could work?

BarneeL

Not an ideal solution, as I have other sheets that reference full columns in the data sheet that this solution would interfere with. I would have to create the Formulas in new columns I guess. I wish there was a way to set up a metrics section on a sheet...

Paul Newcome

My apologies...

 

I meant to create a metrics section on the data sheet. If you have 50 columns to pull metrics on, add 51 columns to the sheet. One for a spacer and 50 for the grid. If your 50 columns to be referenced are in a different order from what you will be using on your metrics sheet, I suggest temporarily dragging them into the order you want them in. Once you set up the metrics section, you can drag the data columns back to their original order, and the metrics section will maintain what they are supposed to.

.

I will attach a few screenshots to this post to give you an idea (on a smaller scale) of what I am talking about.

.

In the first screenshot you will see the data sheet with the metrics section to the right of the spacer column. As long as your metrics columns are in the same order and spacing as your data columns, you can enter the formulas into the first column and dragfill.

.

The second screenshot shows my actual Metrics sheet. I built a basic grid with my column names across the top and the metrics label on the left in the primary column.

 

I selected all of the cells within the grid ([A Metrics]1:[E Metrics]3), right-clicked to access the options (I use a windows computer), and clicked on "Link from Cell in Other Sheet..."

 

I then selected my data sheet, highlighted the appropriate cells (AM1:EM3), and clicked on "Create Link".

.

This does require a fair amount of setup in your case because you are referencing 50 columns with 30 different formulas for your grid, bit this will allow you to reference entire columns, update those column references automatically using dragfill, and still be able to pull the data to a different sheet.

 

Although depending on how you are using this data, you may not need to use a separate Metrics Sheet. A lot of uses for the data can be pulled directly from the metrics section on your data sheet itself.