Mumsnet Moonwatch

Mumsnet Talk

"The country's most popular meeting point for parents" The Times
  Topics | Active | Search  

Mumsnet latest

discountpartnersnew MEMBER DISCOUNTS Get a 10% discount from Boden (inc free delivery and returns). To see all member discounts, click here. Not a member yet? Join Mumsnet for free here. discountpartnersnew

Useful tools

Ovulation calculator icon detailOvulation calculator
 Due date calculator icon detailDue date calculator
 Meal planner icon detailFamily meal planner
Baby name finder icon detailBaby name finder

Recipe of the week

smithy's roasted beetroot: low-fat, dairy-free, gluten-free, nut-free and generally nutritious and virtuous. This one-dish wonder is delicious and uses up some seasonal beets. What's not to like?

MN Local

Please login or join Mumsnet first.

Follow mumsnet on...

TwitterFacebookYoutube

Start new thread within this topic | Watch this thread | Flip this thread | Refresh the display
Show all messages Add a message
This is page 2 of 2 (This thread has 36 messages.) First | Previous | Next | Last Go to page

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

(36 Posts)
Add message | Report | Contact poster
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 !
Add message | Report | Contact poster
MrAnchovy Sat 28-Nov-09 12:26:58
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?
Add message | Report | Contact poster
MrAnchovy Sat 28-Nov-09 12:28:57
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.
Add message | Report | Contact poster
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...
Add message | Report | Contact poster
MrAnchovy Sat 28-Nov-09 15:10:24
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 grin.
Add message | Report | Contact poster
mankymummymoo Sat 28-Nov-09 15:27:50
Have emailed you the files - thank you sooooooo much, this is incredibly generous of you. grin
Add message | Report | Contact poster
Katymac Sat 28-Nov-09 15:38:28
Thank you - I have
Add message | Report | Contact poster
Katymac Sat 28-Nov-09 16:37:35
I think mine is mainly about suppressing blanks
Add message | Report | Contact poster
MrAnchovy Sun 29-Nov-09 00:36:50
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.
Add message | Report | Contact poster
Katymac Sun 29-Nov-09 09:59:53
Hi, thanks & I emailed you
Add message | Report | Contact poster
mankymummymoo Sun 29-Nov-09 11:50:15
Thank you Mr Anchovy. You are the amazing. Mr Anchovy has solved my problem with a click of his most genius fingers ! grin gringringringringringringringringringringringringringringringrin[gri n]gringringringringrin
Add message | Report | Contact poster
MrAnchovy Sun 29-Nov-09 14:11:56
If anyone else is interested you can download the solution here.
This is page 2 of 2 (This thread has 36 messages.) First | Previous | Next | Last Go to page
Add your message here (See our Talk guidelines)
Nickname:
Password:
To post a message you need a valid mumsnet nickname and password. If you have forgotten your nickname, click here for a reminder. If you are not yet a member of mumsnet, you can join here.

Emphasis: To bold a word, surround it with asterisks, so *hello* will display hello. For underline use _ , so _hello_ gives hello. For italics use ^, so ^hello^ gives hello. To strike out a word, surround it with two hyphens either side, so --dog-- gives dog

Links and smileys: To insert a smiley face, , type [smile] or :)
For a big grin, , type [grin] or :o
For a wink, , type [wink]
For a shocked face, , type [shock]
For a sceptical face, , type [hmm]
For a confused face, , type [confused]
For an embarrassed face, , type [blush]
For an angry face, , type [angry]
For a sad face, , type [sad] or :(
For an envious face, , type [envy]
For a no comment face, , type [biscuit]

Links The simplest way to insert a link is to enter the web address (URL) surrounded by [[ and ]]. So type [[http://www.mumsnet.com]] to display a link like www.mumsnet.com. If you want your link to display text other than the web address itself, leave a space after the address then add the text before the ]]. So "Look at [[www.mumsnet.com this page]]", would display "Look at this page."

Shortcuts

Popular forums

Product reviews

Tools


Advice

Family recipes

Membership


About us


Other stuff