Copy cell value between two worksheets and paste in column of another worksheet

[excelintermediate]

Dear Forum,
I am using Excel 2010.
I have a workbook with several worksheets that are between the Worksheet "Start" and the Worksheet "End".
All the worksheets in between have similar arrangement, so that in the Worksheet named "Statistics" (which is not in between the two mentioned worksheets), there are statistics formulas, such as: = AVERAGE(Start:End!K2).

I would like a formula in worksheet Statistics that will copy the cell values of cells H72 in worksheets between Start:End and paste them in Column AC, starting in lets say Cell AC1, and continue below.

I would like to use this formula in several other workbooks that have similar arrangement.
The number of worksheets betwee Start:End varies and all their names are different as well.
Hope it can be done with a formula, instead of vba.

I found in the forum a worksheet with the formula that uses INDEX and MATCH, looks at Sheet2, finds a name and copies its valuein Sheet1. I am sorry I can't find again the post where I found this Excel doc in the forum.

Their formula is: =INDEX(START:END!$B$2:$B$10,MATCH(COMPILATION!A2,START:END!$A$2:$A$10,0)) and they copied it and pasted to cells below.

I tried to adapt it: =INDEX(START:END!$B$2:$B$10,MATCH(Statistics!A2,START:END!$A$2:$A$10,0))
but I get error: #VALUE

Maybe this is not the right path to solve it.

I have attached their worksheet with their example in Sheet1 and 2, and what I tried in the Statistics Worksheet and Start and End.


If someone could help me or direct me to the right path I will appreciate it very much

[martindwilson]

your "their" formula is
=INDEX(Sheet2!$B$2:$B$10,MATCH(Sheet1!A2,Sheet2!$A$2:$A$10,0))

and to be honest even if it included start:end i cant see how a 3d index match would work like that

[FDibbins]

if all your data on each sheet is in the same sequence, try the attached...

[excelintermediate]

Hi FDibbins and Martindwilson,


I think I used/applied the example wrong. Sorry for this. I actually wanted a formula that will copy the value that is in one cell, let's say Cell C5, from all the worksheets that are between the worksheet Start and End and paste them in a Compilation worksheet (in the same workbook).

I managed to use a vba (I didn't want to use this) that will write the Names of the worksheets in Compilation worksheet, Cell B2 and below, and then I combined the results with function =INDIRECT in the adjacent column that gives values from t cells C5.

I used:

=INDIRECT("'"&B2&"'!C5")

What I noticed is that every time I go back to the Compilation worksheet, the vba automatically adds the names of the worksheets below again (they get duplicated).

If someone could help me use a formula, that would be great. Or if I could get advice on how to stop the vba from running every time I access the worksheet, please help.


I don't know if I can just copy the vba and paste it here so everybody can see it (I am new at the forum). Otherwise, it is in the attached file .xlsm

[oeldere]

Below the VBA code your using.

Maybe it's easier for the forum helpers.

I suppose it's in the line of "private sub".

On the other hand i'm a VBA noob so rely on the answers of the college-forummembers

Please Login or Register  to view this content.

[oeldere]

And if you wish the answers of all individual sheets, on the statistics sheet, you can use the system of the added file.

the blue cells are handly added.

the yellow cells contains a formula.

the cells in row 1 are dynamic (you can copy them to the right).

Please reply if this helped you.

[excelintermediate]

Hi Oeldere,

I actually needed a formula that will write all of Jacks’ values below his name, let’s say below D1. Then all of Bobs’ below E1, and so on. I didn’t do a right table in the first place. My mistake.

And having names of worksheets in a column, that would be a bonus.

The Number and Names of sheets between Start and End vary. I tested your formula, but if I change the name of a worksheet, it shows “#REF!”.

I have several workbooks that I would like to just copy paste the formulas.

Honestly I don’t know how to solve this; if it is feasible without vba. I have attached a table with better clarification.


Thanks a million!

[oeldere]

Same list with other names (in table).

For your question i will look later on.

[oeldere]

And the way your asking for.

Do you have questions, just ask.

[excelintermediate]

This is perfect, Oeldere, thank you very, very much!!
Have a great day!

[oeldere]

Glad i could help you.

Thanks for the reply.

Please mark your questions as solved.