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 ...Not able to sort whole rows
I have a protected sheet with password say "1122"
In the protection window I have checked the box of "Sort"
I selected whole lines from 14 to 40 and tried the: Data|Sort and had the
"The cell or chart you are trying to change is protected and therefore
Any sugestion how to solve this using a macro or VBA code for a button
I need to sort according to column F (which is hidden).
What code do I need to hide 2 groups of columns:
DR:FQ and G:AB
Unprotect, do the sort then reprotect.
ActiveSheet.Unprot...find which column has the maximum value
I have to check each row for the maximum value in that row. But instead of
writing the maximum value of that row, I have to write the column number of
that maximum value. The very first row of my dataset goes from 1 to 100,
indicating 100 columns, and is there only to number the columns. So, the
column number has to be picked from that row.
For instance, in row 20, the maximum value is 10, and it is at column 56.
How can I as output of a formula (or conditional formatting) get as answer 56?
(remove nothere from email addr...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...insert a row on ALL sheets
I have a spreadsheet with 12 sheets in it, one for each
month of the year. Is there a way to insert a row on all
12 sheets at once or do I have to go into each sheet and
insert the row one at a time? In other words, do I have to
do it 12 times or is there a way to do it only once and
have it affect all 12 sheets?
"bw" <email@example.com> wrote in message
> I have a spreadsheet with 12 sheets in it, one for each
> month of the year. Is there a way to insert a row on all
> 12 sheets at onc...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...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)....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...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...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.
>...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 ...Holding the view of column one
I want to hold the view of column one while working on other columns. What
is the best approach to my need?
Thank you, Mark
Microsoft MVP - Excel
"Mark S." <Mark S.@discussions.microsoft.com> wrote in message
> Good morning,
> I want to hold the view of column one while working on other columns. What
> is the best approach to my need?
> Thank you, Mark
...How to access siebel data
How to access siebel data from vc++
i would like to sum a series of numbers in columnB and put in cell C1.
which rows to sum depends on hard inputs in cells A1 (13) and A2 (29). in
other words, i want to sum the numbers from B13 to B29 in this case. i know
i can do this by setting C1 to: =sum(b13:b29), but the rows to sum will be
changing frequently. next time A1 and A2 might be 9 and 36, respectively,
thus summing cells B9:B36. i do not want to manually change the formula in
C1 every time i change A1 and A2. i also do not want to create a macro. any
thoughts? thanks, mike allen
A couple of ways
=SUM(INDEX(B:B,A1):IND...Store row number after a find
Following a find command I want to store the row number to a variablke and
use that to move to a columb on that row
what column? same column? different column?
> Following a find command I want to store the row number to a variablke and
> use that to move to a columb on that row
Any column on that row. Real problem is storing the 'found' row number to a
> what column? same column? different column?
> "Alan" wrote:
> > ...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" <firstname.lastname@example.org> 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...freezing columns labels while sorting columns
... I need to sort names in columns, but while doing that my columns labels
are also being sorted. How to avoid that?
In Sort Dialogue Box you can see an option button "Header Row" under "My
Data Range As" click it.
In Sort Dialogue Box you can see a check box "My data has headers" in the
Right corner near to Close Button in the Sort Dialogue box.
Remember to Click Yes, if this post helps!
> .. I need to sort names in columns, ...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
There isa command to allow a formula to refer to a number in a cell to
adjust the column that is used in the formula. BUT I CAN"T FIND IT!
If I want the formula to be using cell A11, I want to have 11 in a cell
and have it refer to whatever to A11. If I have 12 in the cell then I
want it to refer to A12 instead. Sorry, I am not explaining the
scenario very well.
mdalby's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=7055
View this thread: http://www.excelfor...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 ...Excel Column References
My spread sheets have spontaneously switched from giving
column refs as letters to numbers. Instead of A, B, C etc
they now show 1,2,3..........
Why has this happened and, more to the point, how can I
Thanks in advance for advice
<Tools> <Options> <General> tab,
*Uncheck* R1C1 reference style.
Please keep all correspondence within the Group, so all may benefit!
"Nic Siddle" <NicSiddle@nsiddle.freeserve.co.uk> wrote in message
news...Excell, why oh why can't you just move rows up and down !!!!!
You know, back before excell, when it was multiplan on the original Mac's,
you could easily move rows and columns, inserting them INBETWEEEEEN other
rows and colums. Twenty years later you still havn't corrected the idiotic
proceedure required to move rows and columns in Excel. When you drag or
paste a row or column it should default to slipping in INBETWEEEEEN the
others. You could even have a cute little bar appear to indicate the
difference between inserting INBETWEEEEEEN things and overwriting them (like
Multiplan had). I have looked and looked for years and can'...