Reading and populating a worksheet

[Guest]

Hi All

I need a function or a program that reads the same column and row from multiple worksheets and popultes it in a new worksheet.

I can ahcieve reading multiple rows and columns from the same worksheet, which is very simple and excel already provides the functionality but I can not figure out the above situation.

I hope somebody can help with this

Thanks in advance

Regards

Tofigh

 

[GB]

Hi All

I need a function or a program that reads the same column and row from

multiple worksheets and popultes it in a new worksheet.

I can ahcieve reading multiple rows and columns from the same worksheet,

which is very simple and excel already provides the functionality but I can
not figure out the above situation.

I hope somebody can help with this

Thanks in advance

Regards

Tofigh

Hi. The following looks through all the worksheets in your workbook and
extracts the value in B6 (as an example). The results are then stored on
Sheet 3 in column C. I think it is pretty obvious how to adapt it to
whatever you want, but feel free to ask if there are any probs.

Public Sub Test()

For i = 1 To Worksheets.Count
Sheets(3).Range("C1").Offset(i - 1, 0) = Sheets(i).Range("B6")
Next

End Sub

Regards

Geoff

 

[Kevin Stecyk]

Tofigh,

Here are a couple of alternatives:

A) 3-D Reference

Here is an example: =SUM(Sheet2:Sheet6!A2:A5)

You can see more information in XL's help. I will copy a portion below.

Refer to the same cell or range on multiple sheets by using a 3-D reference

The workbook must contain more than one worksheet. For more guidelines for
using 3-D references, click .

1) Click the cell where you want to enter the function.

2) Type = (an equal sign), enter the name of the function, and then type an
opening parenthesis.

3) Click the tab for the first worksheet to be referenced.

4) Hold down SHIFT and click the tab for the last worksheet to be
referenced.

5) Select the cell or range of cells to be referenced.

6) Complete the formula.


B) Look at Data | Consolidate

This is a handy command. You might wish to investigate XL's help.


Hope this helps.

Regards,
Kevin

Hi All

I need a function or a program that reads the same column and row from

multiple worksheets and popultes it in a new worksheet.

I can ahcieve reading multiple rows and columns from the same worksheet,

which is very simple and excel already provides the functionality but I can
not figure out the above situation.