Pull data (last 100 days) from one sheet to another and transpose it (OFFSET & COUNT??)

[pgp007]

Dear Excel Friends,

I was hoping someone could help me pull data from one sheet to another and just pull the last 100 workdays (trading) of data on an ongoing basis. Also I would like to transpose the data.

Please let me explain the exact details.

I manually update a sheet which I call "source data" daily (adding a row) with the closing prices of a futures exchange.

But I would like to automatically move the last 100 days of data from the ¨Source data sheet" to another sheet (CalWorksheet) transposing the date and data.

Attached are the two worksheets with the data and template.

Please let me know if you need more information or explantion.

Many Thanks!

PS I added the most recent US holiday dates that fall within the last 100 working days

[ChemistB]

It should be fairly simple to pull the last 100 days. However, looking at your example on CalWorksheet, I am not sure what you are representing on each row (under ED Generic and DATES)? Do you want what is in Row 1 (Source) to be moved to column A and what is in Row 2 (Source) to be moved to column B?

[pgp007]

I like simple...Please ignore the ED generic text it just a numbering sequence.

I want what is in row 177(source) to be moved to column C(CalWorksheet) actually cell C7 etc.

Please remember that I add a new row daily to source data so I want this row always to be in cell C7 (CalWorksheet)

I updated the spreadsheet with an example.

Thanks!

Please let me know if you need further clarification

[ChemistB]

First, need to get your source data into dynamic ranges so when you add rows or columns, the defined name changes. I created the following Defined names

RowCt = =MATCH(9E+99, 'Source Data'!$A$4:$A$5000) (assumes you will never have more than 5000 rows of data)

AllDates ='Source Data'!$A$4:INDEX('Source Data'!$A$4:$A$5000, MATCH(9E+99, 'Source Data'!$A$4:$A$5000))

DataSet ='Source Data'!$B$4:INDEX('Source Data'!$B$4:$ZZ$5000, RowCt, MATCH("zzzz", 'Source Data'!$B$2:$ZZ$2))

Then in CalWorksheet cell C7 copied across

=INDEX(AllDates, RowCt-COLUMNS($C$7:C$7)+1)

In C8 copied down and across

=INDEX(DataSet, RowCt-COLUMNS($C$7:C$7)+1, ROWS($C$7:$C7))
See example