I need an Excel guru really urgently!

[misshardbroom]

I have an Excel workbook for preschool which is structured as follows:

First sheet is called 'Individuals' and lists all the children we have on roll in Column A, and then has further columns to show their total attendance across each day of the week for that term.

Subsequent sheets in the workbook are called 'Monday', 'Tuesday' etc., and list the children who attend that day in Column A, and then has columns for each week of the term, and then total columns for the number of days present, absent, or unauthorised absences.

There's a bit of complex VBA which pulls the totals from the Monday - Friday sheets through onto the front sheet.

I didn't write it .

Anyway, because the Spring term next year is quite short, I've had to remove some columns from the Monday - Friday sheets, and suddenly lots (but not all) of my cells on the front 'Individuals' sheet say REF! in them, and I don't know how to fix it.

Please help me. Fear my job may be on the line. Children to feed, mortgage to pay...

Do you have a saved copy of the workbook from before you started dicking around with editing it?

yes I've got last year's

do you want to email me the spreadsheet and i'll have a look for you?
my email is miamla at hotmail.com

Look in the formula for one of the cells that says REF

It will say dfg+kfhg+sjfh+REF!+REF!

Delete the +REF!+REF!

See if the result is what you expect

Copy new formula wher appropriate

Or me -- [email protected]

Or both of us and we can race...

May be best to replace actual children's names with dummy data first, though.

Ref! means these cells originally had either a formula or were otherwise referenced to other cells. You can try the undo button and can use it several times. You can also try closing it out, it will ask if you want to save changes and you will say NO which will take you back to the original document. You will lose any of your work but the file should revert back to the way it was when you opened it.

Good luck!

port and lemon... you're on! but i may have to withdraw gracefully from the race if DS wakes up!

The cells say 'ref' because you've deleted some columns that contain part of the formula which calculates the totals.

Just amend the formula in the offending cell(s) so it doesn't include the missing columns & it should be sorted.

The formula should show in the bar near the top of the screen & you can edit it there if you prefer, by deleting '#ref', then pressing enter.

Hope I've explained that ok!

OK, I'm going to try some of the suggested approaches but in the mean time I've sent it to port&lemon and Miamla to play with.

(apologies that the email hasn't got any text, just the attachment, but it's running painfully slowly so I thought I would just send it!)

Thank you all so much. Off to take my contact lenses out before my eyes shrivel up but will be back shortly!

hi, got your email, DS just woken though so i'll have to do this tomorrow, sorry

portandlemon...if you sort it in the meantime can you post on here

Right -- short answer is that in the long formulae where they say 18 (in cols B, E, H, K and N), 19 (in cols (C, F, I, L and O) and 20 (in cols D, G, J, M, and P) you need to change those to 15, 16 and 17.

But I'll fix it for you and send it back (and possibly make it more resilient to this kind of thing as well...

PortAndLemon, I think I'd like to marry you.

Miamla - hope you get DS back off to sleep easily so you can get some sleep yourself!

Thank you thank you thank you

MissH, don't want to appear unkind, but that was not a guru question.

Would really recommend you learn Excel properly yourself and then you would realise that particualr spreadsheet should be very straightforward to set up from scratch and a piece of cake to edit.

The VBA thing sounds offputting and most people would think it is utterly ridiculous in a present day Excel spreadsheet designed to do what yours is doing, ie nothing much.

yes, I quite agree... could seriously do with a course of some description.

I do also suspect that whoever set this particular spreadsheet up has made it rather more complicated than it needed to be.

Look if you're slaving over a crapola spreadsheet on a Sat night, you have really got to go in angry on Monday and tell them that you have to have proper training. Not least because if you have one dogeared spreadsheet that's been there since Lotus 1-2-3 was a lad your preschool is going to have a dozen similar, all of which are unusable.

Yes it does sound terribly overcomplicated, using the old fashioned data manager trick of putting in lots of nonsensical stuff so only one person (keep me in a job, chaps!) could update it, and the lady data monkeys could use their secretarial and typing skills to key in some figures. Insulting.

Excel doesn't work like that - if you are straightforward, your spreadsheets will be,also you can and should do your own

Please get some training, you will understand what I'm on about.

Spreadsheets are a feminist issue. Discuss.

I should send you my waiting list spreadsheet, you'll be apoplectic!

I do agree though, once upon a time I worked amongst mainframe programmers who would code things into knots so as to make themselves indispensible.

Ah, Lotus 1-2-3...

those were the days ..... (have to say it is MUCH better now )

Miss H.. very pleased to hear all is sorted