Calling all Excel Experts!

Lifeisbutadream

I have set up a spreadsheet which I need to update periodically.

There are several of the same spreadsheets, each for every employee - there are up to 5 fields that need updating.

On a few occasions I have forgotten to update one or two of the fields, which means that the employee has the wrong information. When I realised this I replaced the wrong sheet, but is there any way that I can set up excel to 'flag' which areas I need to update? I am happy to do something in VBA (I have basic knowledge) but would love it if there were an expert who had a basic programme that I could use?? or any other suggestions would be great...

Thanks in anticipation...

millwalll

There are a few ways you could do it using vba

you could do somthinkg like

if range.A5.value = ""
msgbox"add data"

or if there was text in the field you could change the colour of the text

not sure if that 100% correct as I am leaving java at the moment so head all over place but sure it somthing like that

a good site for help is www.mrexcel.com

GunJack

How about conditionally formatting the cells you need to update, or how about using a vlookup to your main source data sheet ??

isofa

I assume you mean the cells contain older data, rather than being just empty?

If they are empty until updated, then conditional formatting will be the easiest.

Else I'd probably add a column called Date Updated, and then when the value is changed in the cell(s) in question, trigger a VBA script to update the Date Updated cell with today.

Then you can use Conditional formatting to flag rows that haven't been updated for x days, or those that have dates older then older cells Date Updated values.

Lifeisbutadream

isofa wrote: »

I assume you mean the cells contain older data, rather than being just empty?

If they are empty until updated, then conditional formatting will be the easiest.

Else I'd probably add a column called Date Updated, and then when the value is changed in the cell(s) in question, trigger a VBA script to update the Date Updated cell with today.

Then you can use Conditional formatting to flag rows that haven't been updated for x days, or those that have dates older then older cells Date Updated values.

Yes I just copy the last form and then update the four or five cells.

sandra_nz

Once thing I'd done in the past is use the Protection function (Format > Cells > Protection tab).

So I make all the cells 'Locked' except the cells I want to change. I then select Protect Worksheet from the Tools > Protection menu.

Once you've done this, you can use the Tab key to go through each of the unprotected cells. This makes it much easier to check which cells need to be updated, especially if it's only a few cells compared to the size of the worksheet.

espresso

You could create a macro to copy the last form and advance though the cells requiring input to be updated.