Data from columns to a static table

[Faz]

I have created a column that has 14 rows of text, the data associated with this column will chnage by adding a new column on a weekly basis. The intention is for specific cells from this column to be inputted into a second table, and in a specific cell, that will allow me to create create a waterfall graph of the weeks data.


The previous weeks data is to be retained as an historic record.


How do I get Excel to read the column of data and get it to pick up the next data in the column, and on a weekly basis rather than doing this manually?


Thanks, Faz

 

[dan_l]

ohhhh.....it's fiscal year end here at Chandoo.org.........


So basically:


you have records.......it doesn't matter what the specifics are, but just to make sure I've got a mental grasp on what you're trying to do:


Season 1 Season 2 Season 3

Wins:

Losses

Goals:

Goals Against:


And you want to select a Season from your data, to display in some sort of 'report' type thing.


Fastest answer I have is hlookup w/ a little data validation. Create a list of 'seasons', call it list_seasons. Create a drop down box with the list of seasons. Use an hlookup to point at the specific row you're looking for to place it in the correct cell. Make the chart based on the returned values from the hlookup.

 

[Faz]

Okay not that clear, but effectively how do I get an excel table to look at a column of data and only pick data that is within the latest column, and not the previous column. A new column will be added with data every week.


What kind of macro or formula do I need to apply?


Faz

 

[dan_l]

Ok so:


Data:


Days: 1 2 3 4 5

Value 1 2 6 10 14 18

Value 2 3 7 11 15 19

Value 3 4 8 12 16 20

Value 4 5 9 13 17 21


=HLOOKUP(LARGE($B$1:$F$1,1),$A$1:$F$5,2,FALSE)


Simple explanation:

Chandoo says:


=hlookup(what,where,return_what,mylistissorted)


So for the what, instead of giving it a fixed value, we're telling it to take the largest value from the list.

 

[Faz]

Thanks for this.


I have tried it, and works, but would like this to be able to pick up the next value in the column, irrespective if the number is large or small - whhat would the formular look like, assuming the table was updated weekly.


Data.


Week 1 2 3 4 5...52

Value 20 10 3 4 5 ... -8

Value 30 20 40 5 6 2


Is it also possible to do this with an offset equation where the data can expand Across to The Last Numeric Entry in the table?


Faz

 

[dan_l]

=HLOOKUP(COUNT(A1:F1),A1:F5,2,FALSE)


just counts how many values are in the header row, hlookups based on that.