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 !

Oops, forget that - I just looked back at my post and I can see that I have left the reference from my test spreadsheet in - it is looking at cell L14 not A1 for the spreadsheet name!

Just change the L14 in the formula to A1 and copy down.

Ok, thats cool - that returns a text string the sequences down.

I am embarrassed at how thick I am, but how do i get it to display the value in a set cell from each of the files referenced....

i.e. in the c column i need it to display the contents of the cell F36 for each of the invoices that are listed in column B (and C) cos I pasted them there (does that make sense?).

So sorry to take up so much of your time...

Column B should be displaying a text string.

If you do Paste Special -> Values into column C, column C should now contain a formula which returns the value of F36 from the appropriate spreadsheet. If it contains the same string as Column B, I think you just did 'Paste' instead of 'Paste Special -> Values'.

Email me the spreadsheet at mr dot anchovy at mranchovy dot com and I'll see if I can patch it up.

Katy that goes for you too, send it to me and I'll take a look, but no promises .

Have emailed you the files - thank you sooooooo much, this is incredibly generous of you.

Thank you - I have

I think mine is mainly about suppressing blanks

Sorry Katy, supressing blank rows or columns or worst of all just part of a row or column and moving the remainder into the gap is something that spreadsheets just weren't designed for. You can write macros to do it but they are a nightmare to maintain.

Hi, thanks & I emailed you

Thank you Mr Anchovy. You are the amazing. Mr Anchovy has solved my problem with a click of his most genius fingers ! [gri n]

If anyone else is interested you can download the solution here.