Data Consolidating Worksheets - Create Link to Source Data -??

[Guest]

When using data consolidation and clicking on the "create link to source
data", when returning to the summary sheet and clicking on the + sign - I see
the FILE name there instead of the SHEET tab the information came from (which
doesn't make sense). Here's an example: RAIL TOURS is the total (which has
the + next to it. When I click the +, I do not expect to see 2Travel1. I
EXPECT to see that the the sheet called AFRICA, the 2nd from INDIA, etc.
Instead, it's reflecting the name of the sheet. IF I click on 233,440 on my
sheet - I see in the formula that it's the africa sheet (Africa!B7). The way
this is working, I'd have to rename each 2Travel1 to the sheet name the
information came from. I don't think this is working properly- unless
something is 'turned off' somewhere.
Qtr1 Qtr2 Qtr3 Qtr4
2Travel1 $233,440 $266,335 $315,665 $241,890
2Travel1 $178,440 $154,600 $145,400 $168,905
2Travel1 $115,330 $86,355 $73,240 $98,900
Rail Tours $527,210 $507,290 $534,305 $509,695

 

[Guest]

It may be no consolation, but you've described exactly how Data Cosolidation
works. Perhaps someone can give you some VBA code that will help you out.

tj

 

[Guest]

I know how data consolidation works..... It doesn't seem right that I am
getting the FILE name instead of the SHEET name.... I believe in Excel 2000 -
I used to get the sheet names there.... How awful it would be if you had
several worksheets you were consolidated and they all referred to the SAME
FILE.... Doesn't make sense. It REALLY should be referring to the SHEET the
information came from.

 

[JulieD]

Hi Fran

if you'ld like to tell microsoft your views try
http://register.microsoft.com/mswish/suggestion.asp

Cheers
JulieD

 

[Guest]

Julie, I did as you suggested. I swear, in previous version of Excel - it
used to refer to the page and not the file. It could have been an oversight
as it doesn't make sense.

 

[JulieD]

Hi Fran

sorry, but i've been using the feature since ver 97 and it has always
refered to the workbook not the sheet - i find it annoying to as it means
you can basically only use it if you split the data up into separate
workbooks (not the most efficient method, IMHO).

i also added a suggestion on this topic as well - but i'm not holding my
breath

Cheers
JulieD

 

[Guest]

Well, I teach Excel lately and doing this exercise to prepare for class.....
I really don't like the way it works - I don't mind the consolidating part -
but find it's almost embarrasing that the file goes there instead of the
sheet - or you could have the file name in brackets and then the sheet - but
at least refer to the sheet. OK - at least I know it works (stupidly) the
way it's 'supposed to'..... It'll be a fix for Office Longhorn, I'm sure.

 

[JulieD]

Hi Fran

i'm a trainer to, and the way i approach it is i say it's okay for
consolidating within one file .. and we do it and discuss etc .. then i say,
however, its most useful to do across workbooks - and so we create a new
workbook from each sheet in the workbook (most have never done that either)
and then we consolidate it.

Cheers
JulieD