Assigning cells from one worksheet to another

[Al3990]

Hello MrExcul Forum Community,

This is my first post . I'm having a bit of trouble trying to copy cells from one worksheet onto another in a sequenced order.

I have data of 3, 6, 9, and 12 month returns for all the sectors in the S&P, but I'm trying to compile a much easier format to view all the returns month-by-month.

In the excel file, I have multiple spreadsheets: each sector with returns, and the first spreadsheet tab contains the format I want. I'm having trouble assigning the cell value from multiple spreadsheets onto the formatted worksheet.

It's a bit hard to describe...it's better if you guys can see the file to get a sense of what I'm trying to do...

I'm not sure how to attach an excel file onto this post (if someone could kindly instruct me on how to do this it would be greatly appreciated).

 

[VoG]

Hello. You cannot attach a file but you can provide a screenshot http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

 

[Al3990]

I couldn't figure out how to use that...so I uploaded the excel file onto mediafire. Please take a look.


http://www.mediafire.com/?b1l7rrrg7669gr1

 

[Al3990]

bump

 

[Al3990]

Can anyone help me with this problem? or am I not clear in my explanation and need further details? it's a bit hard to describe, but it's easier to understand if you look at the file.

 

[Haseeb Avarakkan]

Hello, Try

On Ranks sheet B4 across & down.

=VLOOKUP(LOOKUP(1E+100,$A$3:$A4),INDIRECT("'."&LEFT($A4,FIND(" ",$A4)-1)&"'!A:G"),COLUMNS($B4:B4)+3,FALSE)

INDIRECT will make your file volatile. If you can make a drop down list of date, whenever select a date from the list will update the formula.

You can also use SUMIF to get the monthly data.

 

[Al3990]

I think there is a bit of confusion.

What I want to do is take the values from other spreadsheets and have it reference it back to the "Rank" spreadsheet in that format under each corresponding sector and month.

So for instance, the first set of data: The date is 1/1/2003, I want to set the value under SPSU and 3 mo. (B4) with the value from the spreadsheet named ".SPSU".

The formula bar should show "='.SPSU'!D18" on cell B4 on the "Ranks" spreadsheet. I'm able to drag across the cells and the values automatically populate itself because it knows the next corresponding value under the spreadsheet ".SPSU", BUT i can't drag down in order to set the right values for each individual sector. Instead, once I have referenced the first cell (B4 in this case), if I drag downward the values will appear to be the values below D18 from the ".SPSU" spreadsheet and onward rather from the spreadsheets of the corresponding sectors. In the first 4 months that I've already done, I had to set the first value under each sector for the 3 month column under the "Ranks" spreadsheet before dragging across the other cells under the next 3 columns to get the values.

I was wondering if there is a more efficient way of doing this rather than having to assign each cell under the "Ranks" spreadsheet manually, because that would take forever to do. I want to be able to do this for every month from 2003 to 2011. I have a lot of data and I'd greatly appreciate a time saving solution!

Please help!

 

[Haseeb Avarakkan]

If you have huge data using INDIRECT is not a good formula. Any way try this.

on Rank sheet A3 you have a date 1/1/2003. Copy the below formula to B4, then copy across & down until E13.

=VLOOKUP(LOOKUP(1E+100,$A$1:$A4),INDIRECT("'."&LEFT($A4,FIND(" ",$A4)-1)&"'!A:G"),COLUMNS($B4:B4)+3,FALSE)

A15,

=EDATE(LOOKUP(1E+100,$A$1:$A14),1)

Then, copy A4:E13 & paste to A16. After copy A15:E25 & paste down to as you necessary.

Some one would help you for a VBA code.