Ask Your Question
0

calc - filename from cell

asked 2016-11-06 14:05:54 +0200

DiesNuts gravatar image

updated 2017-12-10 17:05:46 +0200

erAck gravatar image

Let's say I have sheets "myStuff_10", "myStuff_30", ... imported

on the A column, I have

10
30
50
70
...

and in the C column, I want to have

='myStuff_10'.$B$5
='myStuff_30'.$B$5
...

How can I take that value from the A column, though?

I.e.

='myStuff_<take from A column>'.$B$5
='myStuff_<take from A column>'.$B$5
...

something like (doesn't work, ERR:501):

 =INDIRECT("myStuff_"&A3).$B$5

such that I can drag it down rather than manually adjust a thousand cells?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2016-11-06 14:46:11 +0200

karolus gravatar image

updated 2016-11-06 19:41:48 +0200

Hallo

=INDIRECT("MyStuff_" & A3 & ".B5" )

But I'll give a strongly advice not to do this kind of "data-import" for huge amounts of data.

edit flag offensive delete link more

Comments

I love you. Would upvote, if I could, but need >5 points to do that, so ... But why do you advice against doing this kind of thing and what alternatives ARE there?

DiesNuts gravatar imageDiesNuts ( 2016-11-06 15:17:51 +0200 )edit

The alternative is, not to distribute data into numerous sheets if you need this data in one place.

karolus gravatar imagekarolus ( 2016-11-06 19:46:22 +0200 )edit

That's not really an alternative, that's just repeating "don't do that". I have some 200 .csv files - each of which represents an independent measurement - that I need to summarise and plot. So ... what else am I going to do than to import each .csv into a sheet of its own, summarise it there, then copy the summarised values over to a new sheet for plotting?

DiesNuts gravatar imageDiesNuts ( 2016-11-07 10:56:44 +0200 )edit

In that case you should first merge these files into one* and then Import the ´merged.csv` into Calc.

*For Example from LinuxTerminal:

>> cd /path/to/csv/folder
>> cat $(ls *.csv) >> merged.csv
karolus gravatar imagekarolus ( 2016-11-07 12:51:22 +0200 )edit

I think that's a really bad idea. ~200 files, 93 columns each, good luck trying to find something if you toss it all into the same file.

DiesNuts gravatar imageDiesNuts ( 2016-11-07 15:29:40 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-11-06 14:05:54 +0200

Seen: 447 times

Last updated: Nov 06 '16