Simple questions: '[Final.XLS]07.01.04'! and using cells

[poopdepants]

Hey guys

I'm calling data from other sheets.

I autofilled the address and got this:

'[Final.XLS]07.01.04'! (And then the cell number)

Where does the 07.01.04 come from? It is not a folder name.

Also, I was wondering if I can use a cell on the sheet to call up data in the other cells.

Cell A1 contains the filename.

I've tried '[A1]'! with no success. I'm a n00b. What do I do?

 

[poopdepants]

No one here knows anything about this?

 

[poopdepants]

mrexcel, more like uhhhhhh..... mr doesn't-know-the-answer-to-my-question

 

[BJungheim]

Your question has been answered in many other threads. A simple search for INDIRECT.EXT will give you the results you need.

 

[poopdepants]

Listen, I've searched for that thanks to previous recommendations, but you see, I have no idea how to implement it in my own work.

I'm already using SUMIF. How do I nest INDIRECT.EXT into that?

I have no idea!

 

[Tazguy37]

How about something like this?:

Book1
	A	B	C	D
1	indirect_test.xls
2
3	21
4
Sheet1

And the sheet where the data is:

indirect_test.xls
	A	B	C	D
1	a	1
2	b	2
3	c	3
4	d	4
5	a	5
6	b	6
7	a	7
8	a	8
9
Sheet1

Formula in Book1, Cell A3 is:

=SUMIF([indirect_test.xls]Sheet1!$A$1:$A$8,"a",[indirect_test.xls]Sheet1!$B$1:$B$8)

That any help? indirect_test.xls was open when I did this....

 

[poopdepants]

yeah man, I can do that, but I want to know how to make it point to a source according to cell A1, so I will have a variable sheet name in the SUMIF.

 

[poopdepants]

I want to use a sumif where the target criteria and sum range are in a different file.

 

[XLGibbs]

SUMIF('[=INDIRECT("&C11&".xls)]job costings'!$D15:G732,"1.1",'[=INDIRECT("&C11&".xls)]job costings'!$F15:F1564)


Your syntax is off, the indirect function won't work the way you want it in this case, which is why someone mentioned the INDIRECT.EXT which is part of the morefunc add in (i think) downloadable from this site.

There must be an easier way for to you link to the data in the other workbooks, rather than relying on the cell for the workbook name...does the workbook name change frequently?

You can also check out the help file on INDIRECT for guidance on using that function.

 

[poopdepants]

Yeah.

I'm trying to create a template that can be used over and over for jobs with different ID numbers.

A different job ID means different filenames all round.

 

[XLGibbs]

Well, you "could" consider an alternative...such as, simply linking your template to one of the file names, and when you want to update with a new filename, go to EDIT> LINKS and point to the new file. Your formulas will update automatically.

That is the easiest way to change the source file. Provided your source files of all these jobs are identical (which they would have to be, or your existing attempt would be futile anyway!)