Referencing entire column of data from another sheet.

  • Thread starter Thread starter gtslabs
  • Start date Start date
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?
 
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.
 
Back
Top