How do I copy the same cell from several worksheets into a column on a summary worksheet

**cosereann** · 07-05-2013, 11:55 PM

Hi,
I have about 50-60 worksheets with data and formulas. I need a summary spreadsheet that will list in a column the name of each sheet (I found a macro that works for this part) and the values from cell A2 and O2 from all sheets.
Please help!
Thank you,
Nico

**billstpierre79** · 07-06-2013, 12:03 AM

Can you post a sample workbook?

**cosereann** · 07-06-2013, 12:54 AM

Here is a sample file. I actually have 50-60 sheets and it is very time consuming to do it by hand.
I did not use the macro that lists the spreadsheet names as I was hoping we can do it all in the same code.

**FDibbins** · 07-06-2013, 01:07 AM

Hi and welcome to the forum

Based on the sample file you supplied, copy this down and across....

=INDIRECT($A2&"!"&CHAR(64+COLUMN(A$1))&2)

**cosereann** · 07-06-2013, 01:25 AM

Hi FDibbins,
I tried the formula but it does not work. I get a Ref error. Where did u want me to paste it? And can u explain the formula to me?

Thanks,
Nico

**AB33** · 07-06-2013, 05:55 AM

Try the attached.

**FDibbins** · 07-06-2013, 04:03 PM

You put it in B2, and copy down and across

**cosereann** · 07-08-2013, 10:49 PM

This works! Thank you so much!

**cosereann** · 07-08-2013, 10:53 PM

FDbbins,
The formula you gave me works as well but I don't understand it enough to make it work for the other columns besides B.
Thank you!

**FDibbins** · 07-09-2013, 12:09 AM

the breakdown of that formula...
=INDIRECT($A2&"!"&CHAR(64+COLUMN(A$1))&2)

is as follows...
starting in the middle and working out,
CHAR(number) will return a letter that is the ASCII equivalent of that number
CHAR(64.........will return the character just before the letter A, - to make the formula work, I took the character before A, so I could add 1, and the copy/paste would work, and return A
COLUMN(A$1) gives 1 as an answer, which I add to the above to make it 65, to give me 3.
I used the COLUMN() function to incrementally advance the column. 64=A, 65=B, 66=C etc
I then combine the column letter with 2 CHAR(64+COLUMN(A$1))&2) to give me A2, B2, C2 etc
Finally I add in the sheet name from A2.
All this gives me an answer of ="aff1!A2" but this is still text and cannot be used as a reference in a formula, so this is where INDIRECT() comes in
INDIRECT() converts a text string into a reference that can be used in a formula
=INDIRECT($A2&"!"&CHAR(64+COLUMN(A$1))&2)

How do I copy the same cell from several worksheets into a column on a summary worksheet

LinkBack

Thread Tools

Rate This Thread

Display

How do I copy the same cell from several worksheets into a column on a summary worksheet

Re: How do I copy the same cell from several worksheets into a column on a summary workshe

Re: How do I copy the same cell from several worksheets into a column on a summary workshe

Re: How do I copy the same cell from several worksheets into a column on a summary workshe

Re: How do I copy the same cell from several worksheets into a column on a summary workshe

Re: How do I copy the same cell from several worksheets into a column on a summary workshe

Re: How do I copy the same cell from several worksheets into a column on a summary workshe

Re: How do I copy the same cell from several worksheets into a column on a summary workshe

Re: How do I copy the same cell from several worksheets into a column on a summary workshe

Re: How do I copy the same cell from several worksheets into a column on a summary workshe

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions