Automating copying of data from different worksheets.

If i have a database table with the Column Headings as the names of different
sheets, in Sheet10.
A B C D E
1 Sales / Sheet1 / Sheet2 / Sheet3 / Sheet4 .....
2 Jan1
3 Jan2
4 Jan3
5 ..........
6 ..........

How do i retrieve the value of a particular cell in Sheet1, Sheet2, .....by
selecting the cell B1 in Sheet10 as the input for retrieving the name of the
Sheet1 and getting the output from a cell having some value in Sheet1.
or, if Sheet1!B2 = 10, then if i want this value in Sheet10 in cell B2, I
want to define the formula in the cell B2 of Sheet10 as "B1"!B2 - shd give me
10, where "B1" shd mean the value as "Sheet1" (as the column heading) and
hence Sheet1!B2 should give the value as 10. Pls. help me in defining the
correct way of defining the formula.
I want to copy this formula across the cells in sheet10 for automatically
retrieving the date from respective sheets as the column headings are exactly
the same as the name of the sheets, instead of selecting the cells from each
Sheet separately & manually. I would also want to apply this method in
hlookup for getting the data from different sheets.

Pls. help.

Use the indirect formula

=INDIRECT(B1 & "!B2")

This will retrieve the value from sheet name lying in B1 and in Cell B2 of
that sheet.
You can then copy the formula to other columns in the same row.

Alok Joshi

"sansk_23" wrote:

> If i have a database table with the Column Headings as the names of different
> sheets, in Sheet10.m
> A B C D E
> 1 Sales / Sheet1 / Sheet2 / Sheet3 / Sheet4 .....
> 2 Jan1
> 3 Jan2
> 4 Jan3
> 5 ..........
> 6 ..........
>
> How do i retrieve the value of a particular cell in Sheet1, Sheet2, .....by
> selecting the cell B1 in Sheet10 as the input for retrieving the name of the
> Sheet1 and getting the output from a cell having some value in Sheet1.
> or, if Sheet1!B2 = 10, then if i want this value in Sheet10 in cell B2, I
> want to define the formula in the cell B2 of Sheet10 as "B1"!B2 - shd give me
> 10, where "B1" shd mean the value as "Sheet1" (as the column heading) and
> hence Sheet1!B2 should give the value as 10. Pls. help me in defining the
> correct way of defining the formula.
> I want to copy this formula across the cells in sheet10 for automatically
> retrieving the date from respective sheets as the column headings are exactly
> the same as the name of the sheets, instead of selecting the cells from each
> Sheet separately & manually. I would also want to apply this method in
> hlookup for getting the data from different sheets.
>
> Pls. help.
>
>

thanks a tonne.

sanjay kapoor

"Alok" wrote:

> Use the indirect formula
>
> =INDIRECT(B1 & "!B2")
>
> This will retrieve the value from sheet name lying in B1 and in Cell B2 of
> that sheet.
> You can then copy the formula to other columns in the same row.
>
> Alok Joshi
>
> "sansk_23" wrote:
>
> > If i have a database table with the Column Headings as the names of different
> > sheets, in Sheet10.m
> > A B C D E
> > 1 Sales / Sheet1 / Sheet2 / Sheet3 / Sheet4 .....
> > 2 Jan1
> > 3 Jan2
> > 4 Jan3
> > 5 ..........
> > 6 ..........
> >
> > How do i retrieve the value of a particular cell in Sheet1, Sheet2, .....by
> > selecting the cell B1 in Sheet10 as the input for retrieving the name of the
> > Sheet1 and getting the output from a cell having some value in Sheet1.
> > or, if Sheet1!B2 = 10, then if i want this value in Sheet10 in cell B2, I
> > want to define the formula in the cell B2 of Sheet10 as "B1"!B2 - shd give me
> > 10, where "B1" shd mean the value as "Sheet1" (as the column heading) and
> > hence Sheet1!B2 should give the value as 10. Pls. help me in defining the
> > correct way of defining the formula.
> > I want to copy this formula across the cells in sheet10 for automatically
> > retrieving the date from respective sheets as the column headings are exactly
> > the same as the name of the sheets, instead of selecting the cells from each
> > Sheet separately & manually. I would also want to apply this method in
> > hlookup for getting the data from different sheets.
> >
> > Pls. help.
> >
> >