Referencing entire column of data from another sheet.

G

gtslabs

I want to plot a column of data but need to define easily what column
to chart.
I have a worksheet called data with each column representing a year
and each row representing a day, so 365 rows. The Year is in ROW 1 and
the data is in rows 2..366
On another sheet called Charting I have A1 = to the year of data I
want to plot.
What do I need to put in A2..A366 so it pulls in the correct data
based on the year in A1?
 
S

Shane Devenshire

Here are two formula you could use

=OFFSET(Sheet1!$A$1,ROW(A1),MATCH(Sheet2!$A$1,Sheet1!$B$1:$J$1))
or
=INDEX(Sheet1!$B$1:J366,ROW(),MATCH(Sheet2!$A$1,Sheet1!$B$1:$J$1))

Where Sheet1!B1:J1 are the years, A1 has the year you want to display and
the row data runs from B1:J366, with titles on the first row. Don't forget
leap year! You adjust as appropriate for your data.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel Subtracting multiple columns from another column 3
Cell Referencing 5
Excel Is this possible?? 3
Copy Data from 1 Sheet to Another 3
Getting the average from a column using date constrants 8
VLookup Data, Find 2 Adjacent Rows, Return Value in 1 Row 3
Create a new sheet dependant on values 1
Find last column matching criteria 2

Top