one worksheet that automatically transfers its values to another. Need Help.

[snipeski]

I need to make a worksheet that will automatically update numbers in another worksheet regardless of what column the numbers in the new work are in. For example, Report A gets numbers for column 1 from worksheet B, column 3. If I use another worksheet similar to worksheet B but not identical I need the values to still transfer to worksheet A. So essentially, even if the numbers in worksheet B are in a different column (not column 3) i still need them to show up in worksheet A.

any help would be great and ill be around if anyone needs clarification.

[aydeegee]

Hi snipeski


Welcome to the forum.

I am sure that this can be done. Perhaps if you will upload a sample (all sensitive data removed), you will get an answer tailored to your needs.

Regards
Alastair

[sheena798]

This can be achieved using Get External Data (note, in spite of the name, this can be used to self-reference in a workbook)

This answer is based on Excel 2010. If you are using a different version it's still possible, but menu's to access these features may be different.

Steps:

In the Master worksheet, from Data tab, select Get External Data / From Other Sources
Select From Microsoft Query
From the Select Data Source dialog, select Excel Files*
From the Select Workbook diaog, select the file you are working in
Microsoft Query should now be open, displaying the Add Tables dialog
Add Securities and Tangible Assets sheets
From Tangible Assets, double click Name and Value to add them to the query
Select SQL from the menu bar
Edit the displayed query to this, and accept:

(SELECT `'Tangible Assets$'`.Name, `'Tangible Assets$'`.Value
FROM `Securities$` `Securities$`, `'Tangible Assets$'` `'Tangible Assets$'`)
UNION
(SELECT `Securities$`.Name, `Securities$`.Value
FROM `Securities$` `Securities$`, `'Tangible Assets$'` `'Tangible Assets$'`)
You will get a warning, SQL query can't be displayed graphically. Select OK

Select Return Data to Excel from File menu.
Select where to place the query
It's Done!
Now, whenever your source data sheets are updated, refresh the Master query to get the latest data.
http://ggi.ac.in/
Sheena Bajaj