| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting a celldata from another excel worksheet
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Getting a celldata from another excel worksheet
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 _______ "Jerry Manner" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Getting a celldata from another excel worksheet
On 29 nov, 14:57, "Jon Peltier"
wrote: 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 _______ "Jerry Manner" wrote in message ... 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- Tekst uit oorspronkelijk bericht niet weergeven - - 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Getting a celldata from another excel worksheet
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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Jerry Manner" wrote in message ... On 29 nov, 14:57, "Jon Peltier" wrote: 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 _______ "Jerry Manner" wrote in message ... 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- Tekst uit oorspronkelijk bericht niet weergeven - - 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Getting a celldata from another excel worksheet
Hi
Thank you for the reply. It works fine now. Regards On 30 nov, 14:40, "Jon Peltier" wrote: 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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. -http://PeltierTech.com _______ "JerryManner" wrote in message ... On 29 nov, 14:57, "Jon Peltier" wrote: 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 _______ "JerryManner" wrote in message ... 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- Tekst uit oorspronkelijk bericht niet weergeven - - 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- Tekst uit oorspronkelijk bericht niet weergeven - - Tekst uit oorspronkelijk bericht weergeven - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Unable to Insert Worksheet/Move and copy worksheet Excel 2003 | Excel Worksheet Functions | |||
| Upload multiple text files into 1 excel worksheet + put the filename as the first column in the worksheet | Excel Worksheet Functions | |||
| keeping formulas static when cutitng and pasting associated celldata | New Users to Excel | |||
| excel 97: copy and paste values from one worksheet to another worksheet | Excel Programming | |||
| Attaching a JET database to an Excel Worksheet OR storing large binary data in a worksheet | Excel Programming | |||
Linear Mode
