mankymummymoo
Wed 25-Nov-09 11:05:13
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 !
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?
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.
mankymummymoo
Sat 28-Nov-09 12:59:06
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 
.
mankymummymoo
Sat 28-Nov-09 15:27:50
Have emailed you the files - thank you sooooooo much, this is incredibly generous of you.
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
mankymummymoo
Sun 29-Nov-09 11:50:15
If anyone else is interested you can download the solution here.
