How to include cell value in file pointer string

English
#1

I have a spread sheet I have been did 2 years ago. It works, but currently I have to rename the file it gets cell values from.
I would like to automate this and produce a pointer like the following, where the year is taken from a cell in the current sheet, which is the current year subtract 1 and put that value in the string that points to the cell from which I wish to get the cell value for the current sheet.

='file:///d:/rainfall chart/rainfall previous year/rainfall chart 2024.ods'#$Sheet1.M34

I have tried to create this using concatenate with “some text” cell-1 “some more text” but I can’t get it to subtract the 1 from the current year, also I get different errors with all the configurations I have tried.
The closest I get is to split the text into different cells do the math manipulation into a cell then use & , but this for some reason only gave me a text string, but did not retrieve the contents.
here is my attempt using &.

=D11&" "&D9&" "&C1-1&E11&E4

I would like to be able to do this in a one line command without splitting into multiple cells first if possible.
Any help will be appreciated.

#2

Have you tried wrapping it in INDIRECT function? Spreadsheet Functions

#3

I did look at the INDIRECT function help page I could not make any sense out of it, I have had no training in programming I just hack my way to what I am trying to achieve. I did NOTE that Earnest is just some AI.

#4

Can you use your clairvoyance to differ between Al and AI.
(Hint: copy and convert to lowercase.)

1 Like
#6

:slight_smile: Point taken.
My apologies to EarnestAl

#10

Use it inside INDIRECT function
=INDIRECT(“D11&” “&D9&” "&C1-1&E11&E4)

BTW spaces in file/folder names is always a trouble search.

1 Like
#11

31805_2 mariosv
Thank you for that.
I applied your solution to Wanderer’s solution and that works!

=INDIRECT("'file:///d:/rainfall chart/rainfall previous year/rainfall chart "&C1-1&".ods'#$sheet1.M34")

Hmm replied to soon it worked in my test sheet but giving #Ref! in the operational sheet, I will investigate further and let you know what transpires.

#12

I am confused I have checked everything I can think that may cause this, even made sure the format of the cells were the same in the two spreadsheets. I renamed the file and directories putting a hyphen “-” in place of spaces that made no difference.
I pasted the formula into a blank cell in the working operational spreadsheet that also gives a #Ref! error.
Here is the changed formula.

=INDIRECT("'file:///d:/rainfall-chart/rainfall-previous-year/rainfall-chart-"&C1-1&".ods'#$sheet1.M34")

This works on the test spreadsheet in a different Workbook..

#5

="file:///d:/rainfall chart/rainfall previous year/rainfall chart "& C1-1 & ".ods'#$Sheet1.M34"

1 Like
#7

Thank you, this produces correct date , but only produced text (of the pointer) in the cell not the contents of the cell.
I think you have a typo, missing single quote inside first double quote I included this in my second attempt but that still did not return the value. Just the text string in the cell.
I then removed the double quotes from the start and end, that produced a #Ref! error.
I know this is wordy but I just wanted to make it as clear as possible what my results are.

#9

The formula, pasted again here for clarity, is on the same sheet I have been doing these tests always shows the correct result, so the contents of the target cell have not changed.

='file:///d:/rainfall chart/rainfall previous year/rainfall chart 2024.ods'#$Sheet1.M34

The following text is what finishes up being displayed in the cell. Using Wanderer’s formula.
When I don’t get an error message.

‘file:///d:/rainfall chart/rainfall previous year/rainfall chart 2024.ods’#$sheet1.M34

#13

If the date in the form 2024 is entered in cell B2 then this should return the contents of cell M34 in Sheet1 for the spreadsheet named the same way, =INDIRECT("'file:///d:/rainfall chart/rainfall previous year/rainfall chart "&B1&".ods'#$Sheet1.M34")

#14

I used cell D1 in my operational spreadsheet, here is the command line, note I have removed spaces from folder and file names.

=INDIRECT("'file:///d:/rainfall-chart/rainfall-previous-year/rainfall-chart-"&D1&".ods'#$Sheet1.M34")

This still returns a #Ref! error on the operational spreadsheet, but works in the test spreadsheet,
There must be a setting in the options of my operational spreadsheet that is preventing this from working.

#15

I can’t notice anything that is not standard setting.
yet if I plug the following formulae in it works.

='file:///D:/rainfall-chart/rainfall-previous-year/rainfall-chart-2024.ods'#$Sheet1.D34

This is from one of the 12 cells where I need this formulae.
------------------------------
Here is a modification of the command which also works on the test spreadsheet but gives a #Ref! error on the operational spreadsheet.

=INDIRECT("'file:///D:/rainfall-chart/rainfall-previous-year/rainfall-chart-2024.ods'#$Sheet1.M34")

**I just realised my terminology has probably confused.
MY test work sheet is in a different workbook
I am hoping that someone can now help fix the problem, where it works in the “test” workbook but not the “operational” workbook **
I also created a new spreadsheet in the operational workbook the above command also gives a #Ref! error on that sheet

#16

I do have template (.ots) file of the workbook I just did a test and this workbook also produces a #Ref! error with the simplified command.

`=INDIRECT("'file:///d:/rainfall-chart/rainfall-previous-year/rainfall-chart-2024.ods'#$Sheet1.D34")`
#17

Thank you .
EarnestAL
Wanderer
mariosv

With your help and a work around I now have a working solution.
The work around was to create a new template workbook , copy the old template workbbook to the new one, and then apply the following formulae to each cell.

=INDIRECT("'file:///d:/rainfall-chart/rainfall-previous-year/rainfall-chart-"&C1-1&".ods'#$Sheet1.B34")

Just for interest, this returns the total value of rainfall for each month of the previous year, so that I can graph this year and last year’s monthly rainfall.
Last year I recorded for March 2024, 262.7 ml this March up to today 30th March I have recorded 369 ml.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.