Support Board
Date/Time: Sun, 24 Nov 2024 14:58:00 +0000
[User Discussion] - Excel cross spreadsheet referencing
View Count: 2337
| [2013-10-28 23:06:46] |
| 100 - Posts: 147 |
|
I know this is not your realm of expertise. I apologize if this wastes your time! Does anyone know how to cross reference data from one excel spreadsheet into another, So that changes to one will be reflected, in real time, to the other? Any help is much appreciated. |
| [2013-10-29 00:23:42] |
| 100 - Posts: 147 |
|
I found this post here discussing data transfer using Visual Basic... Shouldnt Excel have a way to transfer data between workbooks w/out the need for visual basic? http://www.mrexcel.com/forum/excel-questions/388441-data-transfer-between-two-workbooks.html |
| [2013-10-29 00:33:24] |
| vegasfoster - Posts: 444 |
|
Just open both spreadsheets and reference to the second spreadsheet. For example, if the spreadsheets are name Spreadsheet1 and Spreadsheet2, then in Spreadsheet1, sheet1, cell A1, type "=" and then click on Spreadsheet2, sheet1, cell A1 and hit "enter". The syntax will be =[Spreadsheet2.scwbf]Sheet1!$A$1. It is my understanding in the new in-house spreadsheet that you will not be able to reference between files, but you will need to output the data for each chart to a separate sheet in the same file and reference between those sheets. Someone correct me if that is wrong.
|
| [2013-10-29 00:56:13] |
| 100 - Posts: 147 |
|
I do not know if this makes a difference; I am trying to use Excel spreadsheets. Not SierraChart spreadsheets. & I am trying to transfer a whole row of data, not single cells. Date Time Of Last Edit: 2013-10-29 00:56:37
|
| [2013-10-29 01:09:02] |
| vegasfoster - Posts: 444 |
|
Same initial procedure, then if you want to copy a row/column then delete the "$", which create an absolute reference, and then copy the formula.
|
| [2013-11-04 15:03:11] |
| 100 - Posts: 147 |
|
So I should delete one of the $ characters? What would it/should it look like, if I want to reference all of column a? I truly do not understand what you mean... (=[Spreadsheet2.scwbf]Sheet1!$A$1) |
| [2013-11-04 16:02:20] |
| vegasfoster - Posts: 444 |
|
The $ sign locks the column and the row reference so they don't change as you copy. So if you leave $A and copy right, then it will still reference column A. If you delete the $ sign and copy right, then the A becomes B, C, D, etc. as you copy right. Same goes for the row reference and copying up or down. If you leave the $1, then as you copy down it will stay $1. If you want the 1 to become 2, 3, etc. then delete the $ sign. In your case, for sure you want to delete the $ sign before the row reference since you will be copying down. You may or may not want to delete the $ sign before the column reference, depending on what you are doing.
|
| [2013-11-04 17:33:36] |
| 100 - Posts: 147 |
|
Thank you vry much
|
To post a message in this thread, you need to log in with your Sierra Chart account: