named range to load Sheet1 from Sheet2?

**e2Excel** · 06-18-2013, 01:16 PM

I'm trying to use a named range or formula to get a specific range of data from one sheet to load into another. We have a data sheet that has data loaded to it everyday, so the row data is always changing. This sheet has a bunch of columns and rows, but I'm simply trying to get one column and all its rows right now.

The following formula I've been trying to use on my main sheet that is referencing our "Data" sheet, but all it does is return the value 0.
=OFFSET(Data!$C$2,0,0,COUNT((Data!$C:$C)))

The following is small clip of our Data sheet:
CT Date CO CH
AVG 2/5/2012 10 0
AAT 2/5/2012 1 0
A_CE 2/5/2012 3 0
2/6/2012 10 0
2/7/2012 1 0
2/6/2012 1 0
2/6/2012 10 0
2/7/2012 9 0

Column C here is "CO" and by the formula above I'm trying to say Start at row 2, which should be value 10 and take all the rows of data in just this column. Not sure if this is right or I'm doing it right, but I only get the value 0 back.

Can someone tell me how to go about doing this, so that our main sheet auto updates when our Data sheet changes and how to get these values of to the main sheet?

**FDibbins** · 06-18-2013, 02:02 PM

Hi and welcome to the forum

It might be easier to understand what you have/are trying to do, if we could see a sample of the data you are working with?

However, if your columns have headings, and you want to return the data from a specific heading, maybe you could try using index/match instead? something like....

=index($A$1:$E$100,row(A2),match("heading name",$A$1:$E$1,0))

**FDibbins** · 06-18-2013, 02:02 PM

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

**e2Excel** · 06-18-2013, 02:23 PM

Thanks for the response FDibbins!

I've attached a very simple example with Sheet 2 being the "Data" sheet. I'd like to get all the values from column C "CO" to Sheet 1. I left the headers in, but in reality I may or may not have them.

Assuming I can find a way to do that so that when the values for column C change it is reflected in Sheet 1 (somewhere) my long term goal is that whenever the data in the Data sheet changes it's reflected in Sheet 1.

IE: Sheet 1 should show
1
10
1
3
1
10
10
9

and any time the Data sheet is updated it should automatically be reflected in Sheet 1
Hopefully this makes sense