Getting a celldata from another excel worksheet

[Jerry Manner]

Hi

I have an excel file with many worksheets. On the first sheets there
is a lot of data which will be used in cells in the other worksheets.
On the othersheets I have want to place formulas to get celldata from
the first worksheet. Lets say that I want to get the data from cell
C49 from the first worksheet, the formula would be =Sheet1!C49. But I
want to get the last number of the formula ( e.g. 49) from a cell on
the sheet where the formula is used. How should the formula look like?
For example on the second sheet I have a cell (A7) which has a number
( e.g. 47) and I want to place that number into the formula.
I tried =Sheet1!C&A7, but that gives me an error.

Can anyone help me? It looks very simple!

Regards

 

[Jon Peltier]

A few ways:

=index(Sheet1!$C:$C,A7)
=offset(Sheet1!$C$1,A7-1)
=indirect("Sheet1!$C:$"&A7)

In general I would prefer to use them in the order shown (i.e., index
first).

- Jon

 

[Jerry Manner]

A few ways:

=index(Sheet1!$C:$C,A7)
=offset(Sheet1!$C$1,A7-1)
=indirect("Sheet1!$C:$"&A7)

In general I would prefer to use them in the order shown (i.e., index
first).

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______









- Tekst uit oorspronkelijk bericht weergeven -

Hi

I have tried the first option and the second but I received an error
message saying there is an error in the formula.

Regards

 

[Jon Peltier]

Please post on top, like everyone else does. It makes reading the thread so
much easier.

The formulas should read

=index(Sheet1!$C:$C,A7)
=offset(Sheet1!$C$1,A7-1,0)
=indirect("Sheet1!$C"&A7)

Sorry, I was typing faster than I was thinking.

- Jon

 

[Jerry Manner]

Hi

Thank you for the reply. It works fine now.

Regards