Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formulas
I was wondering if there is any formula so that I can type information in one
workbook and have that information also appear in another workbook? (So as that I don't have to type it in twice) and if so do I have to have both workbooks open to do this?? Thank you |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formulas
Do some search in this nice news group and youll find the answer of your
question easy. Here is one reliable answer posted by Tom Ogilvy: Do it with code I am sure (this is the fastest way to do it) say you wanted A1:A10 from sheet1 of the closed work in F1:F10 of the activesheet Sub GetData() With Range("F1:F10") .Formula = "='C:\Directory\[excelfile.xls]Sheet1'!A1" .Formula = .Value End With End Sub If you closed file is like a data base and you want to "pull the data", you can use ADO. http://www.erlandsendata.no/english/...php?t=envbadac Another way is to open the file after you turn screen updating off. Transfer the information, then close it. Application.ScreenUpdating = False ' open the file and get the info Application.ScreenUpdating = True no one is the wiser unless it takes a long time to open the workbook. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formulas
To do it manually, without any code, start out like this:
Open both workbooks. In the workbook that you want to 'echo' the information in, choose the cell where you want it to appear and type in an = symbol to start a formula. Choose the other workbook (the one you'll just be typing in to), then choose the sheet where the information is or will be, and choose the cell where it is or will be. Press the [Enter] key. Now there is a link in the first (echo) book to the primary copy. At this time it will look something like =[sourceWorkbook.xls]'Sheet name'!$A$1 if you close the main workbook, the path to it will be placed into that cell automatically by Excel. If you need a few more cells, you can repeat the process for each. If you would like to echo several cells, as on a single row, edit that first formula to remove the $ symbols from the cell address. Then you can fill (look in Excel Help for Fill Data) the formula across or down the sheet and it will create multiple links to nearby cells automatically also. Once you've set it up, you can close the source workbook, it does not have to be open to see the data in the linked book. You can even link several different pages from several different workbooks into the one book in this fashion. Any time you open the workbook, the current information in the source workbook will be available. You may have to allow it to "update links", but there's even a setting you can make that will bypass that prompt. The methods Tim offered are good alternatives under certain circumstances. "Man" wrote: I was wondering if there is any formula so that I can type information in one workbook and have that information also appear in another workbook? (So as that I don't have to type it in twice) and if so do I have to have both workbooks open to do this?? Thank you |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formulas
thank you so much I really appreciate it. I will give it a go and will let
you know how it went. Thanks again. "JLatham" wrote: To do it manually, without any code, start out like this: Open both workbooks. In the workbook that you want to 'echo' the information in, choose the cell where you want it to appear and type in an = symbol to start a formula. Choose the other workbook (the one you'll just be typing in to), then choose the sheet where the information is or will be, and choose the cell where it is or will be. Press the [Enter] key. Now there is a link in the first (echo) book to the primary copy. At this time it will look something like =[sourceWorkbook.xls]'Sheet name'!$A$1 if you close the main workbook, the path to it will be placed into that cell automatically by Excel. If you need a few more cells, you can repeat the process for each. If you would like to echo several cells, as on a single row, edit that first formula to remove the $ symbols from the cell address. Then you can fill (look in Excel Help for Fill Data) the formula across or down the sheet and it will create multiple links to nearby cells automatically also. Once you've set it up, you can close the source workbook, it does not have to be open to see the data in the linked book. You can even link several different pages from several different workbooks into the one book in this fashion. Any time you open the workbook, the current information in the source workbook will be available. You may have to allow it to "update links", but there's even a setting you can make that will bypass that prompt. The methods Tim offered are good alternatives under certain circumstances. "Man" wrote: I was wondering if there is any formula so that I can type information in one workbook and have that information also appear in another workbook? (So as that I don't have to type it in twice) and if so do I have to have both workbooks open to do this?? Thank you |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formulas
Should work for you.
In essence you can treat other worksheets and other workbooks just as if they were part of the same worksheet, in a fashion - at least while doing it manually like this and with all needed books open. It's just a matter of a few more clicks, with Excel making formula and address adjustments for you along the way. Think of it like this: you have a value in A1 on a worksheet, and you want to echo that value in cell X44 of that same sheet. Then in X44 of that sheet you put =A1 But if you wanted to echo A1 from a different sheet in the workbook, you'd start by typing = then click the other sheet and click cell A1 there and hit [enter] and Excel would build something like ='Other Sheet'!$A$1 All we're doing here is taking that one step further and echoing from another workbook, which means we have the extra 'click' of activating that other workbook after typing the = symbol. Just so you'll know, you can use these same methods inside of functions and formulas, anywhere you'd use any cell reference. There are some worksheet functions that will not work unless both workbooks are open, but for the most part, they work even when the source workbook is closed. "Man" wrote: thank you so much I really appreciate it. I will give it a go and will let you know how it went. Thanks again. "JLatham" wrote: To do it manually, without any code, start out like this: Open both workbooks. In the workbook that you want to 'echo' the information in, choose the cell where you want it to appear and type in an = symbol to start a formula. Choose the other workbook (the one you'll just be typing in to), then choose the sheet where the information is or will be, and choose the cell where it is or will be. Press the [Enter] key. Now there is a link in the first (echo) book to the primary copy. At this time it will look something like =[sourceWorkbook.xls]'Sheet name'!$A$1 if you close the main workbook, the path to it will be placed into that cell automatically by Excel. If you need a few more cells, you can repeat the process for each. If you would like to echo several cells, as on a single row, edit that first formula to remove the $ symbols from the cell address. Then you can fill (look in Excel Help for Fill Data) the formula across or down the sheet and it will create multiple links to nearby cells automatically also. Once you've set it up, you can close the source workbook, it does not have to be open to see the data in the linked book. You can even link several different pages from several different workbooks into the one book in this fashion. Any time you open the workbook, the current information in the source workbook will be available. You may have to allow it to "update links", but there's even a setting you can make that will bypass that prompt. The methods Tim offered are good alternatives under certain circumstances. "Man" wrote: I was wondering if there is any formula so that I can type information in one workbook and have that information also appear in another workbook? (So as that I don't have to type it in twice) and if so do I have to have both workbooks open to do this?? Thank you |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formulas
It worked perfectly thank you so much, I was wondering if I can also do the
same with colours? "JLatham" wrote: Should work for you. In essence you can treat other worksheets and other workbooks just as if they were part of the same worksheet, in a fashion - at least while doing it manually like this and with all needed books open. It's just a matter of a few more clicks, with Excel making formula and address adjustments for you along the way. Think of it like this: you have a value in A1 on a worksheet, and you want to echo that value in cell X44 of that same sheet. Then in X44 of that sheet you put =A1 But if you wanted to echo A1 from a different sheet in the workbook, you'd start by typing = then click the other sheet and click cell A1 there and hit [enter] and Excel would build something like ='Other Sheet'!$A$1 All we're doing here is taking that one step further and echoing from another workbook, which means we have the extra 'click' of activating that other workbook after typing the = symbol. Just so you'll know, you can use these same methods inside of functions and formulas, anywhere you'd use any cell reference. There are some worksheet functions that will not work unless both workbooks are open, but for the most part, they work even when the source workbook is closed. "Man" wrote: thank you so much I really appreciate it. I will give it a go and will let you know how it went. Thanks again. "JLatham" wrote: To do it manually, without any code, start out like this: Open both workbooks. In the workbook that you want to 'echo' the information in, choose the cell where you want it to appear and type in an = symbol to start a formula. Choose the other workbook (the one you'll just be typing in to), then choose the sheet where the information is or will be, and choose the cell where it is or will be. Press the [Enter] key. Now there is a link in the first (echo) book to the primary copy. At this time it will look something like =[sourceWorkbook.xls]'Sheet name'!$A$1 if you close the main workbook, the path to it will be placed into that cell automatically by Excel. If you need a few more cells, you can repeat the process for each. If you would like to echo several cells, as on a single row, edit that first formula to remove the $ symbols from the cell address. Then you can fill (look in Excel Help for Fill Data) the formula across or down the sheet and it will create multiple links to nearby cells automatically also. Once you've set it up, you can close the source workbook, it does not have to be open to see the data in the linked book. You can even link several different pages from several different workbooks into the one book in this fashion. Any time you open the workbook, the current information in the source workbook will be available. You may have to allow it to "update links", but there's even a setting you can make that will bypass that prompt. The methods Tim offered are good alternatives under certain circumstances. "Man" wrote: I was wondering if there is any formula so that I can type information in one workbook and have that information also appear in another workbook? (So as that I don't have to type it in twice) and if so do I have to have both workbooks open to do this?? Thank you |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formulas
Colors, which are just one aspect of 'properties' of cells work kind of
backwards. You go to the source cell FIRST and click it and copy it (Ctrl+C, or Edit | Copy or click the copy icon in the toolbar) then you go to the cell you wish to have the same appearance and paste it (ctrl+V, Edit | Paste, or click the paste icon). You can then use your newly formatted cell as the source for others. Have to watch out when copying - if the cell has formulas with, those get copied also and the cell references may change when you paste it. Sometimes this is a good thing, even a desirable thing, sometimes not what you wanted at all. There is also a very handy tool for applying the format of a cell that already looks the way you want - it's an icon that looks like a paint brush and it is called the 'Format Painter': click a cell that has the appearance you want, then click a cell where you want that appearance (no formulas or values get copied). If you click-hold-and-drag, then the appearance is applied to all cells you drag over. Another trick with the Format Painter: choose cell with desired appearance and double-click the Format Painter, now it becomes 'locked' until you click it again. Now you can click one or more cells in separated locations and have that original format applied to them, and the click-and-drag process also works. Sounds like you could probably use a good basic tutorial/reference for Excel. Microsoft publishes a "Step-by-Step" series that is good, and for Excel, Greg Harvey has published two that are good, depending on version of Excel you're using. If you're using 2007, then http://www.amazon.com/Excel-2007-Ref...102227-4267936 should do well, and if you're using 2003 or earlier then http://www.amazon.com/Excel-2003-All...102227-4267936 I usually recommend those because they cover the same ground that the same author's plain Excel 200# for Dummies does plus more, for only a few dollars more, making them a better value as I see it. There's also a fairly comprehensive list of books about Excel that are available he http://www.contextures.com/xlbooks.html "Man" wrote: It worked perfectly thank you so much, I was wondering if I can also do the same with colours? "JLatham" wrote: Should work for you. In essence you can treat other worksheets and other workbooks just as if they were part of the same worksheet, in a fashion - at least while doing it manually like this and with all needed books open. It's just a matter of a few more clicks, with Excel making formula and address adjustments for you along the way. Think of it like this: you have a value in A1 on a worksheet, and you want to echo that value in cell X44 of that same sheet. Then in X44 of that sheet you put =A1 But if you wanted to echo A1 from a different sheet in the workbook, you'd start by typing = then click the other sheet and click cell A1 there and hit [enter] and Excel would build something like ='Other Sheet'!$A$1 All we're doing here is taking that one step further and echoing from another workbook, which means we have the extra 'click' of activating that other workbook after typing the = symbol. Just so you'll know, you can use these same methods inside of functions and formulas, anywhere you'd use any cell reference. There are some worksheet functions that will not work unless both workbooks are open, but for the most part, they work even when the source workbook is closed. "Man" wrote: thank you so much I really appreciate it. I will give it a go and will let you know how it went. Thanks again. "JLatham" wrote: To do it manually, without any code, start out like this: Open both workbooks. In the workbook that you want to 'echo' the information in, choose the cell where you want it to appear and type in an = symbol to start a formula. Choose the other workbook (the one you'll just be typing in to), then choose the sheet where the information is or will be, and choose the cell where it is or will be. Press the [Enter] key. Now there is a link in the first (echo) book to the primary copy. At this time it will look something like =[sourceWorkbook.xls]'Sheet name'!$A$1 if you close the main workbook, the path to it will be placed into that cell automatically by Excel. If you need a few more cells, you can repeat the process for each. If you would like to echo several cells, as on a single row, edit that first formula to remove the $ symbols from the cell address. Then you can fill (look in Excel Help for Fill Data) the formula across or down the sheet and it will create multiple links to nearby cells automatically also. Once you've set it up, you can close the source workbook, it does not have to be open to see the data in the linked book. You can even link several different pages from several different workbooks into the one book in this fashion. Any time you open the workbook, the current information in the source workbook will be available. You may have to allow it to "update links", but there's even a setting you can make that will bypass that prompt. The methods Tim offered are good alternatives under certain circumstances. "Man" wrote: I was wondering if there is any formula so that I can type information in one workbook and have that information also appear in another workbook? (So as that I don't have to type it in twice) and if so do I have to have both workbooks open to do this?? Thank you |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CELLS NOT CALC FORMULAS - VALUES STAY SME FORMULAS CORRECT?? HELP | Excel Worksheet Functions | |||
lookup formulas dependent upon lookup formulas | Excel Worksheet Functions | |||
automatically copy formulas down columns or copy formulas all the | Excel Worksheet Functions | |||
Formulas not evaluated, Formulas treated as strings | Excel Discussion (Misc queries) | |||
formulas for changing formulas? | Excel Discussion (Misc queries) |