OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Dynamic references to 2nd file

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
patrickgamer
General User
General User


Joined: 19 Oct 2005
Posts: 7

PostPosted: Thu Apr 13, 2006 5:57 am    Post subject: Dynamic references to 2nd file Reply with quote

I've got a set of calc files that have very specific daily data tables. I've organized my files so that one file is a full month, each month has a seperate sheet (tab) for each day, and the daily info is stored in the same cells in every tab.

Now i have a wrapper file that does some stat calculations by importing the data from the files.

I know how to reference a cell from another file. Here's the problem:

One of the calculations requires me to import the same cell accross the different days, (i.e. E1 from tabs 1-30). When I drag the formula I either change the row or the column of the reference, but not the sheet! I've poured over the documentation, but I can't find any specifics on this. I could really use some help on this. It's mission critical for a set of reports I'm working on.

Thanks,
Patrick
Back to top
View user's profile Send private message Visit poster's website
Thelonous
Newbie
Newbie


Joined: 13 Apr 2006
Posts: 1

PostPosted: Thu Apr 13, 2006 7:20 am    Post subject: Hm, I have same problem. Reply with quote

I am creating a multi-sheet file and need to be able to reference a cell from one sheet to another.

IE: Sheet1 has a total of $100 in cell A1. In another sheet, "Totals" I want to be able show the amount in A1 of "Sheet1." I have been searching all of the tutorials for an hour now, plus their in-program help system didn't work...

I know in Excel, the formula is this:


=sheetname!cellname


What is the formula in Calc?
Back to top
View user's profile Send private message
patrickgamer
General User
General User


Joined: 19 Oct 2005
Posts: 7

PostPosted: Thu Apr 13, 2006 8:02 am    Post subject: Re: Hm, I have same problem. Reply with quote

Thelonous wrote:
...What is the formula in Calc?

There's something in one of the help file articles on it.

From outside the file it's:
Code:
='/file.name'#$'Sheet1'.A1

From within the same file it's just
Code:
=Sheet1.A1


Anyone have anything on my problem?
Back to top
View user's profile Send private message Visit poster's website
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu Apr 13, 2006 10:13 am    Post subject: Reply with quote

1. DDE-method: Copy source-cells into target-doc using the "Paste Special"-command with "Link" option set.
2. URL-Method: Start a formula (=), then point select source-cell(s) and hit return.
Notice the differences bewtwwen both:
http://www.oooforum.org/forum/viewtopic.phtml?t=34212&highlight=dde
Back to top
View user's profile Send private message
patrickgamer
General User
General User


Joined: 19 Oct 2005
Posts: 7

PostPosted: Mon Apr 17, 2006 10:42 am    Post subject: Reply with quote

Thanks, but that's not exactly what I'm looking for.

When I said populate, I meant the formula, not the content. I'll take another shot at explaining this:
I have a file that has 2-dimensional data on a sheet. This sheet represents the data for a day. There is a spereate sheet for each day of that month, (so each file is a months' worth of data). Let's say the file is called March.sxc, and the sheets are named 1, 2, etc.

Now I have a seperate file for generating reports, (reports.sxc). I have a sheet for collecting data of one metric from every day of the month and calculates the average. So for reports.sxc sheet one:
Code:
A1 ='file:///Users/patrick/Documents/Analysis/referral numbers/march.sxc'#$'1'.E1

Now A2 should point to:
Code:
='file:///Users/patrick/Documents/Analysis/referral numbers/march.sxc'#$'2'.E1
and A3 should point to sheet 3 and so on.

I don't want to have to make all these entries manually, (it'd take forever, and I have number of reports against several months).

How can I dynamically increment the sheet being referenced?
Back to top
View user's profile Send private message Visit poster's website
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon Apr 17, 2006 11:14 am    Post subject: Reply with quote

Splitting data across many sheets and files is common (but bad) practice.
Keep everything in one single unsorted list and let the program do the separation by time-intervals. Use filters, pivot-tables(data pilot), sheet-functions (YEAR,QUARTER,WEEK,MONTH,DSUM,DMAX,DCOUNT,D...) consolidation and subtotals. (A database can do even better).
Anyway, take some free range (i assume a blank sheet, starting at A1):
A1: January (in your language)
Drag down until A12
Name A1:A12 as "MonthList" or alike
B1: =MOD(ROW($A1);12)+1
drag down a few dozends of rows
returnes a number between 1 and 12.
So you can use:
INDIRECT("'file:///Users/patrick/Documents/Analysis/referral numbers/" & INDEX(MonthList;MOD(ROW($A2)-2;12)+1) & "'#$'1'.E1"
If you get a wrong index, adjust $A2 To $Axx or ROW()-offset. I prefer ROW(cellref) because it adjusts when you move the list.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group