Protecting Structure of cells
I need to protect the structure of an Excel sheet so that unexperienced
users can enter data but cannot change the structure of the document
such as the width, height, font size etc?
Is this possible, and if so how do I do it?
keithtrumble's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24375
View this thread: http://www.excelforum.com/showthread.php?threadid=379681
Tools - Protection - Protect worksheet. You have first to have unlocked
cells whose val...Average every seven cells, then copy formula
On my Averages worksheet, I have the following formulas:
In Cell A2: ="Week " & WEEKNUM(Sheet1!E2,1)& " - " & TEXT(Sheet1!
In Cell B2: =AVERAGE(Sheet1!F2:F8) 'compute values based on blocks
of 7 successive rows
I'd like to be able to COPY these formulas to row 3 so that the
formulas in Row 3 begin on Row 9 of Sheet1, Like this:
Cell A3: ="Week " & WEEKNUM(Sheet1!E9,1)& " - " & TEXT(Sheet1!E9,"mm/
Cell B3: =AVERAGE(Sheet1!F9:F15) 'compute values based on blocks
of...If cell equals, give total of another cell #2
I have a formula that looks likes this:
If true, it returns "Historical!A10"
How do I write this formula to give me the value of A10 and not the name of
Vaya con Dios,
"Lori Burton" wrote:
> I have a formula that looks likes this:
> If true, it returns "Historical!A10"
> How do I writ...Remove Box or Squre Characte from Cells
After doing an export from Outlook/Contacts to Excel I notice a square or box
like character in all my cells.
Is there an easy way to remove it, find and replace does not work.
Chip Pearson has a very nice addin that will help determine what that
If you do see a box, then you can either fix it via a helper cell or a macro:
or as a macro (after using Chip's CellView addin):
Dim myBadChars As Variant
...Link to a cell in another workbook by name rather than absolute c.
I need to get data from a total cell in one worksheet into a cell in another
worksheet. Because the source worksheet will have lines added to it, I wanted
to reference the cell by its row and column label (e.g. "Jan Sales"), rather
than by an absolute cell reference. I tried several variations and keep
getting the #REF and #NAME errors.
Does anyone know how to do this?
Name the actual cell and then reference the cell's name.
Cutter's Profile: http://www.excelforum.com/membe...Microsoft Excel Merge Cells
I work for a Marketing company and every week i am inundated with data to
present in a clear and concise way to board members. I have cells that are
presented like this:
10 11 12 13 14
10 11 12 13 14
10 11 12 13 14
10 11 12 13 14
20 21 22 23 24
20 21 22 23 24
20 21 22 23 24
20 21 22 23 24
So what I have been doing is spending about 2hours a week merging cells. I
would highlight the four 10's and merge; highlight the four 11's and merge.
I want to know if the...Macro to move to specific cell after hitting enter or tab
I would like a macro that moves the active field to Column A and the next row down after the enter key or tab key is hit on the last colunm of entry.
You don't need a macro
If you start in A1 for example and use the tab key to go to
B1,C1.....Z1 then if Z1 is the last cell and you press Enter you are in A2
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
"K McGee" <firstname.lastname@example.org> wrote in message news:294FA54C-C3A6-4C99-8EEA-75A0768FB40A@microsoft.com...
> I would like a macro that moves the active field to Column A an...how do you remove a selected cell that is not saved on a clipboard
I need to know how to remove a selected cell that is not saved on a clipboard
and cannot be posted elsewhere in a table
Are we talking about Access or Excel because Access has fields and you
posted your question to the Microsoft Access newsgroup. Scroll a wee bit
further down to get to the Excel newsgroup.
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" - Tremors
"Dee haskins" <Dee email@example.com>...Copy cells based on conditions in one workbook to another workbook
I hope someone could help me on this. I need a macro that will enable user
to do the following
1. load sheet3("Template") in workbook1
2. copy and save value and format of Template as new workbook say wb2
3. opens wb3 and checks for each cell in column A2 to A"lastrow" that has
value greater than 2 and count them
4. insert the number of counted rows in Sheets("Template"), after eg row 4
5. now copy cell(i,2), cell(i,5), cell(i,7), cell(i,12 to 15) into the
inserted rows in Sheets(Template)
any help will be appreciated.
...Cell Text in File Name
Does anyone know of a way to generate a Save As <file name> from the text
in a cell?
This would be really useful for all sorts of things.
(replace somewhere in email address with gmail if mailing direct)
"BT Connect" <firstname.lastname@example.org> wrote in message
> Does anyone know of a way to generate a Save As <file name> from the
> in a cell?
> This would be really useful for all sorts of things.
&...drag and drop cells to move
Why is Excel.exe saying it has generated errors and needs
to close just because I drag and drop a cell from one
location to another?
For that matter, if I copy out of QPW, and then paste into
Excel, it does the same thing.
Why? And how do I fix this?
You could try getting Excel to re-register all it's files, reg. keys etc.
Type this at the command prompt:
You'll have to navigate to the folder which contains the excel.exe. This
will require a bit of knowledge of DOS if you haven't already got it.
This is a bit of a long shot. I suggest ...clearing data in a cell but keeping the formula
I am wanting to erase the data from all fields, but keep the formualas in the
cells. How do I do that! Thanks.
'Clear User Lists on Control Sheet
Sheets("Control").Cells(5, 1) = UCase(Sheets("Control").Cells(5, 1))
Access doesn't have cells. Therefore I think that you are in the wrong
discussion group. Try Excel.
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder....List Box or Combo Box selection to populate cells
I have excel 2002 and I would like to have a list box or combo box to be
able to populate cells based on the selection. ie Choose company from drop
down .. will populate the address and phone number cells for that customer.
Not quite sure how to do this.. Just need some direction and I should be
able to figure it out from there. The name, address and phone number will be
on another worksheet. I figured I could put the whole name and address in
one cell and just make it big enough to fit once its dropped down.. but that
process is kind of clunky for someone else who may need to add ...hide formulas but can edit on the same cells
How to I hide and lock the formulas that appear on A1 to A10 let's say,
without locking them? I need to be able to copy and paste the results so I
need to be able to click on them. I also need to share the document, but do
not want people to see the password nor manipulate them.
You can hide the column, but if the sheet is not protected, people could
simply unhide the column to see its contents.
If the information is on a protected worksheet, people will be able to
see it, either by copying and pasting the data to another sheet, or by
using a password cracker to...Empty Cells Causing Huge File Size
I do not know what I did, but one of my excel sheet seem to have a lot of "empty" cells which doesn't seem to go away, ie, it is causing the scrollbar to become very thin vertically. I tried doing a Ctrl+Down Arrow to go to the last row of excel then select all rows beneath my data, then I tried delete rows, clear format, clear contents and all don't work
1) What caused this
2) How do I salvage this
Most of the time it is caused by (temporarely) entering something in a cell
"far away" from your real datacells or formatting such a cell.
Go...Auto populate cells based on 2 cells division.
I have a file with two sheets one has a bid template each section of the job
has a total cost and total days. I want to be able to chart the totals by
day. I need the cells on the sheet with the information for the chart to
auto fill. Example:
Cell A2 Description Blah Blah B2 Days 10 C2 Total Cost 100
Column A Column
B Days Cost 1 10 2 10 3 10 4 10 5 10 6 10
I want it to auto populate the days in coulmn A based on the total days from
"B2" on Sheet1.
Also divide the daily charge based...sum of only green cells
How do I sum e.g. only green cells in my worksheet, when I color the cells in
two different colors, green and red?
not possible without VBA
Jan Caesar wrote:
> How do I sum e.g. only green cells in my worksheet, when I color the
> cells in two different colors, green and red?
For Each c In Selection
If c.Interior.ColorIndex = 4 Then ms = ms + c
donald...Why won't my conditional formatting display in the cell
In Excel, I create a conditional formatting to highlite a duplicate
refference. The formatting applyied, as indicated when I click conditional
formatting again, but does not display in the cell. I am useing Office 2003.
I applied a red patern with white bold, but the cell does not display any
You need to post your conditional format formula.
"Cashius War eagle" <Cashius War email@example.com> wrote in
> In Excel, I create a conditional formatting to highlite a duplicate
> reffe...counting cells with color
This is a multi-part message in MIME format.
I have a large spreadsheet of which many cells have been formatted with =
different colors. How do I create a formula to 'count' the red cells, =
or any color for that matter.
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN&...Can I count the number of cells that contain data?
I am creating a spreadsheet that is a chart and each day I track an item and
enter a date. Can I just count the number of cells contain dates...I am not
interested in calculation of contents or averages. All that I care is that
excel can see that I have entered data in that cell or row and it counts it
as one and keeps a running total. So, if I have data entered in 5 cells in a
particular row, it will give me a running count of how many cells have the
data, in this case 5, and show that number at the top of my sheet somewhere
that I can see it easily.
Thanks in advance!
=CO...how do I create a formula to seperate name into two cells (last, .
Is there a formula to seperate a name in one cell (last, first) and make them
into 2 cells?
You could use tht Text To Columns feature.
With the name cells selected, try:
Data | Text To Columns | Delimited | Next | Select appropriate
Next | Next | Finish
"jobby55" <firstname.lastname@example.org> wrote in message
> Is there a formula to seperate a name in one cell (last, first) and make
> into 2 cells?
The most direct way is to use &quo...Forced new line in a cell: Excel
I wanted to start a forced new line after typing down the texts which
wraps down automatically in a given excel cell. Pressing [Enter] just
like any other Microsoft Office documents would take me to the next
cell as selected!
Googling some online help provided a [Alt]+[Enter] keystroke would
serve the purpose. But, that's not happening, is that a bug for excel?
But, it works in my coworker's pc!
So, it's not an Excel bug - more likely to be something to do with
your PC. Try swapping the keyboard with your co-worker to check that.
On J...Need help pulling data from cells in different workbooks
Hi, let me try and explain this the best I can:
say i have a workbook with 2 pages, on the first sheet, in cells C27
C47, C67 and C87
on the Sheet2, I want to make a list. in that list contains data fro
cells C27, C47, C67 and C87 from Sheet1 . on sheet2, the list wil
Is there any easy way to pull that data without doing each lin
individually. I know i can put in the = and pick the cell on the othe
sheet, but I have 100's of cells I need to get data from. If i try an
copy paste each line, it does...Where does the default width and height of the cells come from =) ?
Just wondering why the default width of the cell is 8,43 and width
and how come height is larger value even it's smaller =) ?
Row heights are measured in points or pixels. There are 72 points to an inch
and "maybe" 96 pixels to the inch.
The number that appears in the Standard column width box is the average number
of digits 0-9 of the standard font that fit in a cell.
For an interesting and enlightening discussion on this subject see
Gord Dibben MS Excel MVP
On 26 Jun 2006 23:56:18 -0700, "Sirritys" <aki.koi...Formatting a cell for color that has a formula also
I am using this formula =If ($A$1="","",A1+1) so that when i put a date in it
adds the next date to it. I want to be able to get Saturday and Sunday to
change to a yellow color to note that its a weekend date can this be done? I
can do it manually but would like to be able to do it automatically if
possible. Thanks Karl
Select your range (all at once)
With A1 the activecell, do:
formula is: =weekday(a1,2)>5
give it a nice format.
I like to give date cells a custom format of:
> I am using this...