|
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 |
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 |
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 |
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 |
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 - |
| All times are GMT +1. The time now is 03:06 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com