Another Excel question - re. references other files...

[mankymummymoo]

Am setting up a load of invoices which will be named sequentially 01.xls, 02.xls etc.

I need to set up another xls sheet which basically summarises the invoices, displaying the number and then info from a specific cell from each invoice. The numbers will run sequentially.

So like this...

ColA ColB
01 =[01.xls]sheet1!F36
02 =[02.xls]sheet1!F36

Can I set the formulae in column B to generate automatically from what is displayed in Col A (Column A I want to put basically the first invoice number (depending which one Im starting from, so not necessarily 01) then in row 2 do A1+1 to sequence the numbers down.

Thank you for reading this far !

pleeeease?

err

I think you need to specify filnename seperately as it doesn't like you using formulae within file references

ah poo ! I trust to your infinite wisdom Mrs Badger - thank you for replying though.

You can do this using INDIRECT(), I'll post an example later today.

[watches fascinated]

Oh I won't keep you in suspense then: here is the formula for cell B1:

=INDIRECT("["&TEXT(A1,"00")&".xls]sheet1!F36")

Put 1 in A1, A1+1 in A2 and copy down.

Enjoy

oh brilliant, only just logged on. Will try this tomorrow. thank you.

hmm... get a return of #REF! [thick emoticon]

Would it work if the invoices were separate pages (worksheets) within a single file?

The #REF! is probably because the sheets are not named exactly right to suit the formula I gave, or perhaps they are not in the same directory as the linking file (you can get this to work too, but it gets very complicated). I have a couple of ideas:

Try opening 01.xls to see if it works now.

Do you have this working with manually entered links? If so, can you post exactly what is entered in the cell.

It is not very easy to sort this out at a distance: I could post a zip file with a working set of workbooks on my site for you to download if you want.

Katymac's suggestion would work of course, but I assume that you don't want to do this. Separate files is best for invoices IMHO.

sorry DS has sick bug and am juggling cleaning up vomit with working !

yes have separate xls files for each invoice - there will be too many to combine in a worksheet.

MrA... I tried it exactly as your formula with a test naming them as you named them and also tried adapted your code for my actual files and references.

The files are in the same directory. I will post exactly what is in each cell (when vomit duties done!) - thank you so much for your input, i really appreciate it.

input? actually i think i meant to say lifeline!

Oops, hold on, I'm really sorry. The formula I gave only works when the linked spreadsheets are open.

The solution you need is a bit more complicated - you need to put the directory information in the INDIRECT reference as well.

Fortunately there is a solution for this too - watch this space...

Er, this is from the Microsoft support site:

"The INDIRECT function will only return the result of a reference to an open file. If a workbook that the INDIRECT function is indirectly referencing is closed (not open in memory) the function will return a #REF! error."

Now I know I have cracked this problem before, but I don't have access to the spreadsheet.

This may take a little time...

MrAnchovy - I do love they way you really care about this stuff

I like a nice solution but I struggle with finding it - you seem to like finding it (at the moment I am searching for a laptop & it is driving me wild)

you are a god. i cannot possibly have all the files open at once.

i am in awe of your problem solving ability and determination [japanese respect bowing emoticon]

Ah, found it quicker than I thought.

The solution is not completely automatic though because of the problem I mentioned (that MrsBadger must have had in mind all along).

You need to put this formula in cell B1, and copy A1 and B1 down as far as you need, making sure A2, A3 etc. are showing the right sequence.

=("='"&LEFT(CELL("filename"),SEARCH("[",CELL("filename")))&"["&TEXT(L14,"00")&".xls]sh eet1'!A1")

You now need to select the whole of column B and do Copy. Now select the whole of column C and do Paste Special -> Values.

Hit F9 to recalculate and as if by magic...

If you have problems, check the name of the spreadsheet is being calculated correctly. This is the bit between the brackets in

["&TEXT(L14,"00")&".xls]

... which generates 01.xls to 99.xls. If instead (for example) your sheets are in a subdirectory called invoices and are named Inv980001.xls etc like mine were (no wonder my memory was a bit rusty) you will need to replace the above line with

invoices[Inv"&TEXT(L14,"0000")&".xls]

Also check sheet1 and A1 refer to the right sheet and cell of course.

Good luck!

Thanks mankymummymoo and Katymac for your kind comments.

Some people do crosswords or sudoku, I like solving problems with spreadsheets . Sometimes it helps my work, so it is good to practice techniques so I don't slip up (like I did here ) when I am being paid for it.

I have one.....do you want to fix mine??

It's tricky

pops into thread to look around and realises its all in a language i cant decipher

Haha, might do Katy, unless it involves macros: there is no fun in macro programming, and hacking someone else's macros I wouldn't even do for money.

Damn

How about taking data & producing a new table (while not changing what is already there..............)?

Sorry didnt get back last night...

I've tried what you suggested and it is returning this on every line...

='C:\work\In the Pipeline\formulae testing[00.xls]sheet1'!A1

so basically finding spreadsheet 00.xls each time, although i started the numbering in the A column at 16. The files are not in a subdirectory or anything.

could it be because I have spaces in the folder name?

I appreciate all your time and thought churning - im even dreaming about this flipping spreadsheet !

Spaces shouldn't be a problem.

If it is turning 16 into 00 it is probably seeing the 16 as text not a number. Have you put a ' in front?