protecting a group of cells not a whole worksheet
I would like to protect a group of cells and not a whole worksheet but I haven't been able to figure out how to do this. I think this would be the best way for what I am wanting to do. I have a spreadsheet that I send to 13 people. They make changes and send back to me and then I have to up-date the changes. If I could protect all the cells except for the one they enter in - then I could save the file on a shared drive and it would eliminate me up-dating. Is there a way to do this?
select the cells for which you want to allow entries. After this goto
'Format - cells - Protection&...Data Consolidation
I have 20+ worksheets in a workbook. Each sheet is a timesheet for an
individual and each sheet can have up to eight cost codes which will be the
same on some sheets but will not be in the same position on all sheets. How
do I label or set up a range so I can consolidate all of these cost codes on
a seperate sheet to give me the total hours for each cost code?
Could you for example do the following:
Set up your new sheet with a table with all the cost codes in a column on
the left, and all the people at the top. Then put a sumif, or sumproduct
formulae so that it adds up all the hours ...Duplicate Data
One main form with two subforms (linked by ProjectID), first subform is for
data entry, the second just allows the user to view what has been saved from
the previous subform.
The subform allows users to report on a month basis by selecting a month in
a combo box [PeriodID]. I would like to add some code in the Befire Update
event that checks the PeriodID for a match before it is saved.
Can someone help me with the code please, I have tried various DLookup
snippets of code from this site, but nothing is working as the code does not
really suit my purpose.
Maybe t...Synchronizing Outlook Data
I'm reposting my question; hopefully someone will have
a response that will work. I'm trying to synch my
notebook PC with my desktop PC to share Outlook
information. Is it possible to do this from within
Outlook without buying a third-party addon? I have two
copies of Outlook 2002, one on my desktop and the other
on my notebook PC. I need to be able to use the calander,
tasks, contacts, etc on both PCs and I cannot find any
information on how to do this. Did I waste my money in
buying another copy of Outlook 2002 to run on my desktop?
Any help would be greatly appre...Automatically moving data #2
I was at a halloween party dressed up as a 'killer bunny'
One of the guests there told me to imbed "if statements within vlookup
but not to use more than 8 if statments.
Make any sense to you guys and gals? :confused
multiplan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1591
View this thread: http://www.excelforum.com/showthread.php?threadid=27391
> I was at a halloween party dressed up as a 'killer bunny'
hey, I was at the party too, cloaked as t...Go to a cell automatically
How can I make the active cell the cell after the statement:
"End("B1").xlDown.offset(1,0)" to start a paste operation.
Thanks in advance.
"Mercury" <firstname.lastname@example.org> wrote in message
> How can I make the active cell the cell after the statement:
> "End("B1").xlDown.offset(1,0)" to start a paste operation.
> Thanks in advance.
Range("d1").Copy Range("b1")....data duplication check ?
I have this code which runs a macro to add a complete list of all data for
'today' to be entered into a sepeate worksheet which is called the database.
Is there a way, if so how, that the code can be changed so that if the
'same' data is entered twice a pop up message box is shown to alert the user
of this and stop this happening.
The code I have is
Sheets("Log").Range("A" & Rows.Count).End(xlUp).Offset( _
1, 0).Resize(.Rows.Count, .Columns.Count)....adding trendline into data-column
After adding a trendline (moving average), I woudl like
to add the values of the trendline into a column..How can
I do that??
The chart trendline option is for a simple moving average, e.g. with
period 3, the plotted point is the average of the current and previous
two points. Just use the average function and copy the formula down.
For an exponentially weighted moving average (does not seem to be among
the chart options) see
Inge Jonckheere wrote:
> After adding a trendline (moving average), I woudl like...Placing a Date Range on a Report
I have several reports that have been created in Microsoft Access that
require a date range in order to pull the requested data. The date
range was set up in the query that was built to run the report. Is
there a way to get that date range displayed on the report?
...Display cells with data validation
Using 2003 - Is there a way of displaying cells that contains data validation
restrictions? I've received a spreadsheet from someone with lots of
restrictions on it and want to see which cells are affected.
"Anita" <Anita@discussions.microsoft.com> wrote in message
> Using 2003 - Is there a way of displaying cells that contains data
> restrictions? I've received a spreadsheet from someone with lots of
> restrictions on it and want to see which cells are affected.
>...Run a macro when cell value changes
I have a macro that I would like to run, onlly when a particular cel
value reaches a pre-defined value.
I really do not know how to do this - I have looked on help features
but to no avail. any ideas
Message posted from http://www.ExcelForum.com
right click sheet tab>view code>copy/paste this>modify to suit>save
Now when cell c1 calculates to >32 your macro will fire
Private Sub Worksheet_Calculate()
If Range("c1") > 32 Then call yourmacro ' MsgBox "Hi"
"Emea training >&quo...Data file conversion M05 to M04
I am trying to go back from M05 to using M04. Is there
utility that converts my M05 data file to M04 format?
When you converted to M2005 it made a backup of your M2004 files as *.m12
and told you where it put it (usually same place as .mny). Rename this as
*.mny and open with M2004. You will of course have to catch up all
transactions since you converted.
Microsoft MVP - Money
For UK tips & fixes see
For wishes or suggestions see
or for UK wishes ...Receiving Transaction Entry
Can anyone tell me what the column RCTINVCERRORS is for in the table
SOP60100? I get values in there for linked POP to SOP that seem not to work
i.e. (we have received an item on a linked PO, but the committed sales line
says nothing was received yet - still says purchased - and the committed
quantities now say 0 instead of the sales order quantity like it should say).
I'm guessing values in this column/field are to specify what went wrong
during receiving to cause this error.
...How to access siebel data
How to access siebel data from vc++
...Data validation #23
I am using the Data Validation feature. I have specified a
short list, but cannot select a blank cell entry even
though I have the 'ignore blank' box selected??
Press the Delete key to make a "blank" entry. That should not be blocked by
"Don Niall" <email@example.com> wrote in message
| I am using the Data Validation feature. I have specified a
| short list, but cannot select a blank cell entry even
| though I have the 'ignore blank...Replace every non empty cell
I want to replace every cell with is not empty in a column with th
word "DEMO". Can someone help me?
serge's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=416
View this thread: http://www.excelforum.com/showthread.php?threadid=27555
To fill the blank cells in the used range of a column:
Select the column
Choose Edit>Go to
Click the Special button
Select Blanks, click OK
> I want to replace every cell wit...Sort a Range #2
Sorry Everyone, I know this one has been on here a dozen times, but I cant
find any relating posts.
Say I have 2 columns and 4 rows of data such as:
1 2800 $2000
2 2700 $1952
3 2940 $1700
4 2852 $2100
I want the next 3 rows to sort this from smallest to largest according to
value in column A. I have a fixed # of data points (3 in this example) and
would like to use formulas rather than the sort command so that I can have a
x-y scatter plot of the sorted data that updates automatically when ever new
data...remove rows that have any cells values strikethrough to a blank Sheet2
I am looking for help with a code that is able to cut or delete al
rows that has any cell values strikethrough in any column in th
currently open worksheet, and paste those rows in the blank Sheet2 fro
row 2 downwards. Sheet2 must also have the Header names being copie
over to Row 1.
The open worksheet has cells strikethrough in all the characters, no
just some characters.
After the rows have been removed, the open worksheet should
not contain blank rows in between. The Header names in row 1 must be i
Any suggestions is much appreciated.
Thanks in advance
------------------...Comparing data within 2 lists
I have been asked to compare lists from my 2 main systems. They believe that
the information of them is not consistent. How would I compare the 2 lists in
Excel since there are over 30 thousand records on them. Information:
List 1 List 2
Building no. Color Building no. Color
Building 1 Blue Building 2 Red
Building 2 Red Building 4 Yellow
Building 3 Yellow Building 5 Orange
Building 4 ...Matching data from one table and copy them to another
I have two Excel tables. Table 1 contains list of animals and their
description in two columns. Table 2 contains list of animals and results of a
test. Not all animals from Table 1 mach the animals in Table 2, but most of
them do. I have to link those two tables in a way that test result from table
2 appears in Table 1 Column 3 but of course I must use animal ID column as a
reference to match data. How can I do this?
It's always a good idea to reveal ranges... That said...
Table1 = A2:B100
Table2 = F2:G200
In D2 enter & copy down:
In C2 enter &am...How to combine data from 2 separate workbooks onto 1 worksheet
I am planning a meeting for 100 attendees. Our database contains a unique ID
for each attendee as well as their name, mailing address, phone number, etc.
I have another database that I received from our Travel Department containing
the airline information (arrival date, arrival time, flight number, arrival
How can I merge the 2 databases together into 1 database without having to
copy/paste each attendee's information individually. Both databases have the
unique ID for each attendee - is there a way to have Excel "find" the unique
ID and then ad...eliminating unwanted data
Column a has data, column b has data.
I want ap1 to equal q1 if a1 equals b1.
Actually I am looking for a way to eliminate rows if data in one column
a second column.
Your question is confusing. Do you simply want to delete rows where the value
in column A equals value in column B?
I don't see the relevance of making ap1 to equal q1 if you are deleting the
row anyway where you have 2 equal values. Might as well compare A and B and
Anyway the following is a sample of deleting rows based on a condition. It
firstly sets the interior ...Multicurrency entries out of balance
I ran the "Multi-Currency Debit/Credit Not Matching" automated solution for
GP10 and several journal entries were reported as unbalanced, for both open
and closed periods in two companies. (See end of posting for sample).
The other automated solution, "Trial Balance out of Balance" for functional
amounts, came out fine.
I followed Mariano Gomez's suggestion in a related posting and executed
"Maintenance > Checklinks" option for all the tables. Seven pages came out,
most of them CM related (journals, distribution, receipts, deposit w...Highlighting cells if a value is obtained
I have an IF equation and when it fails/brings up a certain value i would
like it to highlight the cell as a color, specifically red. Is this possible?
If so how?
The equation is
=IF(G11>130000, "No Bolt Specification For Carbon Steel", "0")
In the true instance I need that cell to be highlighted to ensure the
operator knows what happened.
Thank you for your help
Select Format | Conditional Formatting...
Formula is =G11>130000
Choose a Format
> I have an IF equation ...Hidden source data
Why does my graphs go blank when I hide the cells containing the source
Is there a way to omit this annoying occurrence?
when you go Tools|Options|Charts is there a tick in the "Plot visible
cells only" box?
If there is then deselect that option.
Thank you, Ken!
That did it!
I fumbled around a little until I found out that I had to mark an graph
prior to follow this procedure.
I'd say my spreadsheets looking much nicer when a lot of number collecting
cells are hidden