Hi everyone, I am new here and quite new to Open Office.
Can anyone tell me if there is a way to create a filename within a formula or a way to refer to a cell which contains a formula producing a filename from within a formula that referenced data in another file.
I have individual files containing data for each week eg "week 01 week 02" etc. I would like to be able to gather elements of this data relating to specific items into another file but I am struggling to find a way to do it without having to make a great many changes in the summary sheet. I need a way to be able to refer to the a selection of files implicitly rather than explicitly.
Any help would be much appreciated.
Thanks
Tom
[Solved] Creating filenames with formulae
-
- Posts: 1
- Joined: Wed Sep 14, 2022 1:23 pm
[Solved] Creating filenames with formulae
Last edited by MrProgrammer on Mon Sep 26, 2022 2:50 pm, edited 1 time in total.
Reason: Tagged ✓ [Solved] -- MrProgrammer, forum moderator
Reason: Tagged ✓ [Solved] -- MrProgrammer, forum moderator
OpenOffice 3 / Windows 7
- MrProgrammer
- Moderator
- Posts: 4903
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Creating filenames with formulae
Construct in a cell, say A1, using formulas, text in this format:Madmongoose wrote: ↑Mon Sep 19, 2022 1:20 pm Can anyone tell me if there is a way to create a filename within a formula or a way to refer to a cell which contains a formula producing a filename from within a formula that referenced data in another file.
'file:///file'#$sheet.cell
where file, sheet, and cell specify the data of interest. The single quotes are mandatory. Example:
'file:///C:/Documents and Settings/Admin/My Documents/Source.ods'#$Sheet1.D5
Then refer to the external cell described by A1 using formula =INDIRECT(A1). Read about INDIRECT in Help → Index or in User Guides (PDF) or searching for topics about it in the Calc Forum. Be sure to use the correct file name format for Calc, replacing \ in Windows path names with /.
Putting data to be analyzed in separate file or even in the same file but different sheets makes the task difficult. You will find your task easier if you put all the data into a single sheet. Pivot tables are a very useful feature but you can't use them to analyze multiple sheets. Use Data → Filter if you want to view subsets, say a specific month or year, of your data.Madmongoose wrote: ↑Mon Sep 19, 2022 1:20 pm I have individual files containing data for each week eg "week 01 week 02" etc. I would like to be able to gather elements of this data relating to specific items into another file but I am struggling to find a way to do it without having to make a great many changes in the summary sheet.
Calc Guide Chapter 8 - Using a Pivot Table (formerly called Data Pilot)
If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.
[Tutorial] Ten concepts that every Calc user should know
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).