How would Excel experts link cells in 2 workbooks?

[PP3321]

*Background

1. I am using Excel 2010.

2. I have Workbook A (RawData) and WorkBook B (Visualization) in same Shared Folder.

3. 1 column with 31 rows(Daily Revenue) in Workbook A,
needs to be linked in Workbook B

************************************************************

I think there are 3 ways? (or more?) to link cells.

1. Enter Normal Formula
ie (= [File Name] SheetName! Range)

2. Index & Match?

3. Import External Data

Could you please tell me the best practice for linking cells...?

Also, what are the pitfalls for linking 2 workbooks?
Is it recommended anyway...?

Thanks...

 

[Chihiro]

Depends on few things.
- Is data range fixed in RawData or does it change?
- What structure is Data Table in RawData
- Is data appended or should it overwrite existing data in Visualization?

If it's only one column with 31 rows, and overwrites existing data, just use straight link via formula.

Depending on amount of data or type of info I want to import. I typically use one of following.
1. Advanced Filter (Database like table and when I need only particular set from multiple workbooks)
2. MS Query (Database like table, when I want to do quick analysis data by grouping)
3. PivotTable (in conjunction with VBA & External Data source when analyzing single data source).
4. ADODB (Database like table and when I don't want to open source book and importing from multiple sheets)

Additionally, I sometimes play with PowerPivot & PowerQuery.

 

[PP3321]

Thank you so much!! I will look into these tools.
This is what Raw Data looks like.

Daily Revenue per Day...

 

[PP3321]

This is what Dashboard looks like

I link the RawData to the Revenue Column.

Problem is that this dashboard is pre-defined by my superiors.
I cannot change the design.

My job is to fill this column most accurately and efficiently...

 

[bobhc]

Could you not use a simple cell reference.

 

[PP3321]

bobhc yes but I do not know what I do not know...I wanted to ask experts if I am missing better ways to do it...if simple cell reference is the best way be it...