Copy specific cells from one worksheet to another!

Question

I have anworkbook on which the sheet ranges to upto 140 sheets. A number of cells from each sheet should be reflected in a consolidated sheet. I understand that there is a way by giving '=' and then going to the cell and pressing 'Enter' but this is difficult as there are lots of data to be reflected in the consolidated sheet. Can you guide how to do it in an easier method please. If need be, I can send you an example of the same so that you get a better idea of what I am seeking your valuable help of. Kind Ranju

Answer

ranju,

You can always just type in the link

=sheet1!A1

then if I wanted to link to A1:B2 I would just select that cell and copy it down and across to fill a 4 cell rectangle in the same shape as the source data.

If you have a list of sheet names in column A starting in row 2, then in b2 you could put in the formula

=indirect(address(1,1,,,A2)

this would create a link to cell a1 (row = 1, column = 1) in the sheet named in A2

So there are several ways you might be able to 'calculate' your addresses if they follow some type of pattern and depending on what information you already have in the sheet

of course another easy way would be to write a macro to do it

Sub ABC() Dim sh as worksheet, sh1 as worksheet, rw as long set sh1 = worksheets("Summary") rw = 2 for each sh in worksheets

 if sh.Name <> sh1.name then
  if instr(1,sh.Name," ",vbTextcompare) Then
    for i = 1 to 10
       sh1.cells(rw,i).formula = "='" & sh.name & "'!B" & i + 1
    Next i
  else
    for i = 1 to 10
       sh1.cells(rw,i).formula = "=" & sh.name & "!B" & i + 1
    Next i
  End if

    rw = rw + 1
 end if

Next End Sub

that would put in links to cells B2:B11 on each sheet in columns A:J of the summary sheet

If you want to sent a small workbook with the summary sheet and a couple of other sheets with a clear explanation of what you want, I could take a look