| Save only new or changed cells |
|
 |
Index ‹ Excel ‹ Excel Programming
|
- Previous
- 1
- worksheet functions >> Showing Cummulative Top5 results with an array-formula...?Hi There,
Below are array-formulas, so use Ctrl+Shift+Enter:
I can show the Top5 of my range D5:D200 as follows:
=LARGE($D$5:$D$200,ROW(INDIRECT("1:5")))
Suppose this results in a range with output: {1000,500,250,125,0}
I can show the sum of the Top5 as follows:
=SUM(LARGE($D$5:$D$18,ROW(INDIRECT("1:5"))))
Results in {1875}
What I would like to achieve is an array-formula that shows:
SumTop5-values: Being: 1875
SumTop4-Values: Being: 1875
SumTop3-values: Being:1750
SumTop2-values: Being 1500
SumTop1-Value: Being 1000
Hope you cam help?
Thanks in advance,
jen
- 2
- Excel >> NORMDIST for reliability?If given MTBF and standard deviation can the reliabilty be predicted at a
defined operational range using Excels Normaldistribution?
say mtbf = 50, sd= 20, then plug in the operating duration.
NORMDIST(operating duration, 50, 20, true).
Is that a way to calculate reliability at a specifed duration?
thanks!
- 3
- Excel Programming >> UserForm QuestionI have a userform with a text box in it. I want the user to enter the month
and date in the format mm/yyyy. In the text box, I want to display mm/yyyy,
but when the user selects the TextBox1, I want it to clear out. What do I
need to do to accomplish this?
Thanks,
Barb Reinhardt
- 4
- worksheet functions >> Linking dates with a calendar...My hope is this...
column A are names of those I have scheduled meetings with.
column B is a date I enter with the next several columns auto filling using
a function.
I would like to link this to a worksheet with a calendar so it displays the
persons name on the calendar on the dates that the worksheet has listed (5 or
6 each person). Thank you for your help.
- 5
- 6
- worksheet functions >> Using the Mid function.Time for the next question. I know how to use the basic
mid(cell,start,amount). This always starts from the beginning of the
cell. How could I amke one of these so that it starts at the end of the
cell. In other words, so that the start part is spaces from the right
and not from the left. I have to do this because the text I am working
with is at the end of cell that are of unequal lenght.
Thanks.
--
brookdale
------------------------------------------------------------------------
brookdale's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24617
View this thread: http://www.excelforum.com/showthread.php?threadid=382555
- 7
- Excel Programming >> Cut Paste Undo FailedI have a cut paste problem in Excel. This is related to undoing paste in
certain excel worksheet region.
Excel produces two change events, one for a cut and one for paste.
I fill data related to every user action in another sheet in the same
workbook. So for every cut event is log a cut event and paste i log an
update event. Problem comes in cut paste on a region where i want to undo
all changes. when i get the paste event i have already made changes to the
log sheet and Undo fails to operate.
It there any solution or workaround for this issue?
- 8
- Excel Programming >> Easy MacroHey i have Created this Easy Macro-
Sub testmacro()
'
' testmacro Macro
' A Cell adds info to other cells
'
Range("A1").Select
ActiveCell.FormulaR1C1 = "1"
Range("B1").Select
ActiveCell.FormulaR1C1 = "YES"
Range("C1").Select
ActiveCell.FormulaR1C1 = "NO"
Range("D1").Select
ActiveCell.FormulaR1C1 = "NO"
Range("E1").Select
ActiveCell.FormulaR1C1 = "YES"
Range("A1").Select
End Sub
Now this Macro is a Button which i dont want. I want it so when i ente
"1" into the "A" cell it adds the YES-NO-NO-YES into the other cell
shown above. Any help people? Remember i dont want a button just cod
that works when enter "1" in cell A. Thank
--
joelbeveridg
-----------------------------------------------------------------------
joelbeveridge's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3704
View this thread: http://www.excelforum.com/showthread.php?threadid=56772
- 9
- Excel Programming >> Formatting dataHi
When formatting the same type of data frequently, I
design a template with three sheets, the first to paste
the raw data, the second a temporary for the
fuctions/formulas for fomatting the data in the former
sheet and the last sheet is to paste the temporary sheet
data as values. Please see example below.
In the second sheet I write the formulas for more than
the rows of expected data. Say somtimes 200 rows as the
rows of data varies from 10 to 150.
My questions are,
1. Is this the most efficient way of doing this task?
If not what is a better solution.
2. How can I adapt my function/formula sheet to only
write fuction/formulas only for the number of rows in the
data sheet?
Data sheet
ID CustName ProductCode
12 Joe Bloggs FEN
Temporary sheet
ID ProductName
12 Fulltime English (This name is derived from the
ProductCode in the Data Sheet and a lookup table using
the Vllookup function)
Final sheet
Just copies the Temporary sheet as values using macros.
Thanks.
- 10
- worksheet functions >> Matching data to return valueGood Evening and thanks in ADVANCE for looking at my
problem. I am looking for some of the Forum experts to
give me a hand please send me or reply with any
code/equation/idea that makes sense;
I have a workbook that contains 6 worksheets.
1=RegQList
2=289
3=Fisher
4=Grove
5=Sprauge
6=Mooney
(Exactly as typed)
I have in the RegQList hundreds and hundreds ,I think
2700 specific and unique Regulators that I need, no must
calculate, various equations for. What I have figured
out is that if I was smart enough to create a LOOKUP or
smart eqations to find
MAKE- Cell J2/MODEL-Cell K2/SIZE-Cell L2/ORIFACE-Cell M2
on each line in the RegQList then if you could help me
create the code to look at the 5 other worksheets find
the matching MAKE-Cell J2, then within that 1 of 5
sheets match-up MODEL/SIZE/ORIFACE (which on the 5 sheets
is always column A-MAKE/B-MODEL/C-SIZE/D-ORIFACE when
that match is located/solved I want the number known as
Cg which is already in COLUMN E of the same Cell rows as
the matching specific make regulator.
I would like that number returned, or so I wish to place
this lookup equation to place the Cg for each specific
Regulator in AA2 of RegQList. Then with that Cg factor I
can return to my basic Excel skills and create the needed
equation of (1.29) X ( SUM H2+14.7) X (SUM AA2) this
equals Q or Flow. The 1.29 and 14.7 are constant numbers
to calculate Flow.
I am going to play with this all night and morning. SO
if you think that you can help me figure this out I would
appreciate it so much I don't think you can even begin to
understand.
- 11
- Excel >> cvs to xls and add columsI have a database in .cvs and want to convert to .xls. From my experience
can easily been done through the 'Save as" function.
More important, need to adjust the columns of data. Example, in .cvs the
name (first and last) is in 1 column (A) separated by columns and the
city/state are also in 1 column.
Need to break these apart as want separate column for each - last, first,
address, city, state, zip for mail merge and printing envelopes properly.
Did this long time ago and forget the process. Something with copying data
in .cvs to a word doc, replacing all the commas (which in the .cvs separate
the name and city/state) with a period, save to Notepad and open in Excel.
But can't get it right this time. Plus the database is huge and won't open
into Word - have to do it bit at a time.
Any easy way to get this done? Much appreciated.
--
Paul
- 12
- Excel Programming >> Macro To Insert Row And Add Data.A forum user (GER) kindly wrote this macro for me yesterday to find the
cell that holds the text "homeDirectory:" , insert a row above the cell
and insert the text "replace: homeDirectory" into the new row (in the
first column).
I was hoping someone could alter this code for me to produce a new
macro which finds any cells that hold the text "homeDirectory:
\\server1" and inserts a row below the cell and inserts a hyphen "-" in
the first column of the new row.
The data is from an active directory export and only populates 1
column, however the column has 5000 rows.
=================================
Public Sub test()
Dim C As Variant
Dim FirstRow As Integer
With Worksheets(1).Range("a1:a5500")
Set C = .Find("homeDirectory:", LookIn:=xlValues)
If Not C Is Nothing Then
FirstRow = C.Row + 1
Do
C.Insert Shift:=xlDown
C.Offset(-1, 0).Value = "replace: homeDirectory"
Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Row <> FirstRow
End If
End With
End Sub
======================
Thanks for any help
Andy
--
quinla01
------------------------------------------------------------------------
quinla01's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=31583
View this thread: http://www.excelforum.com/showthread.php?threadid=513152
- 13
- worksheet functions >> Returning matches from mutiple rowsI use a nursing schedule that shows all nursing staff's schedule for a whole
month. This is referred to as the master schedule.
From this schedule I need to be able to identify specific staff scheduled to
work on a specific day during a specific shift on a daily basis in a separate
worksheet.
So if the master schedule is:
A1 A2 A3 A4
April 1, 2008 April 2, 2008 April 3, 2008
NAME SHIFT SHIFT SHIFT
Debbie 7-3 7-3 Off
Trina 3-11 7-3 7-3
Sherry 7-3 Off 7-3
Lisa 3-11 3-11 3-11
Lewis 7-3 3-11 3-11
Paula Off 7-3 3-11
Sharon 11-7 11-7 11-7
I need a daily schedule for April 1. If I put in that date, it will return
everyone working that day and segregate it by shift. So everyone who is
working 7-3 on April 1 in the first 3 rows, 3-11 the next 3 and ll-7.
- 14
- Excel Programming >> Date Transfer to userformHi,
On a User From I have a text box where the user enters a date. Two queries
that I have not yet been able to sort out / find an answer - I would be
grateful if someone could assist me.
1. On a user form text box - Is it possible to restrict the user to a
particular date format? i.e. 27/05/05
2. On a userform, Once a user enters the date of their order/activity, how
do I have that date transferred to 4 text boxes to display in 4 textnoxes
the day of week, month, year and date. i.e. user enters 15/05/05 and the
text boxes are populated automatically with Monday - 15 - May - 2005
Any help would be fantastic,
Thanks,
Mickey
- 15
|
| Author |
Message |
alenka78us
|
 Posted: Mon Dec 15 11:14:26 CST 2003 |
Top |
Excel Programming >> Save only new or changed cells
Is there a method that would allow me to save ONLY cell
values that have changed OR a specific set of cells?
AND bring them back in later ...
to do what if analysis.
Don't particularly want to write macros / VBA, but can do
if that is the methodology.
Excel523
|
| |
|
| |
|
Tom
|
| Posted: Mon Dec 15 11:14:26 CST 2003 |
Top |
Excel Programming >> Save only new or changed cells
Possibly this could be handled by the scenarios functionality under the
tools menu. I have never used it, but the description sounds similar to
what you want to do.
--
Regards,
Tom Ogilvy
"JR" <EMail@HideDomain.com> wrote in message
news:084801c3c326$f8b76d10$EMail@HideDomain.com...
> Is there a method that would allow me to save ONLY cell
> values that have changed OR a specific set of cells?
>
> AND bring them back in later ...
>
> to do what if analysis.
>
> Don't particularly want to write macros / VBA, but can do
> if that is the methodology.
|
| |
|
| |
|
| |
|
Index ‹ Excel ‹ Excel Programming |
- Next
- 1
- Excel Misc >> List folder names within a directoryHello Gurus and newsgroup users.
Your kind assistance please.
For some time now I have been using Chip Pearsons code to
return a list of file names within a specified location. I
would like to now return a list of folder names.
I cannot find any objects releated to folders.
Is it possible?
Any help would be appreciated.
Kind Regards
Paul
- 2
- Excel Misc >> please replyhi i have a proble
i want to format a cell such that it can accept only 13 digits before the decimal and 9 digits after the decima
kindly repl
srikanth
- 3
- Excel Programming >> Combo box background colourI have a combo box and it is linked to a cell a2.
When I select a value from the combo box drop down list this obviously
changes the value in cell A2. Is it possible to have the background of the
como box automatically change colour depending on the value of cell a2?
e.g. Cell A2 = 1, combo box background = red
Cell A2 = 2, combo box background = green
Cell A2 = 3, combo box background = blue
Thanks for any help
- 4
- worksheet functions >> Search column for value and return TRUE or FALSEHi!
I function that would search through column of text values (range with 1
column) and return TRUE if column contains value given as criteria or FALSE
if there is no such value in given column
For example i have range A1:A10 and i want to find out whether this range
contains value x or - x
- 5
- Excel Misc >> Net profitHi
I have a purchase price in A1 0f 1.12 and a selling price in A2 of 1.95
how do i get the net profit please
RegRDS
mIKE
- 6
- worksheet functions >> A calendar calculator template that reflects remaing weeks in a yeHello! :-)
I am trying to create a calendar calc tool that, when I work on a sheet, it:
1) Automatically loads today's date
2) Automatically loads number of pay periods remaining in the plan year
(using a pop down to allow selection of weekly or bi-weekly).
3) Accepts a deduction amount per pay period.
4) Calculates a total annual payroll contribution during the plan year
(based on number of pay periods remaining in the plan year, multiplied by the
deduction).
For Example:
1) Our Plan year: 08./01/05 - 7/31/06
2) Automatically load of today's date: 9/15/05
3) Drop down prompting choice of weekly or bi-weekly pay period: Weekly
3) Remaining pay periods between 9/15/05 and 7/31/05: 46
4) Weekly deduction: $28.00
5) Estimated Maximum Contribution: $1288.00 ($20 * 46 pay periods).
----------------------------
Along the same lines...let's say the goal of the employee to make an annual
maximum of $5000 a year. He wants to know how much he must contribute a pay
period to reach his goal of $5000 within the plan year. Can I create a small
spreadsheet tool that will automatically tell the employee how much he/she
needs to have deducted from his/her paycheck by 7/31/05 in order to reach the
goal of $5000, beginning with payroll of 9/16/05?
This seems like it should be easy, but I cannot get it to work? If you have
an Excel spreadsheet you can send as a template, or if you can describe to
process of creating this, I would be so appreciative!
- 7
- 8
- Excel Programming >> Problems with copying and pasting rangeHi,
I have developed the macro below to take a number from a dataset (in
"Sheet1"), copy and paste it into a model (in "Sheet2") and then copy an
paste the model output back into a different column in the original dataset
(in "Sheet1").
Sub Macro()
Dim cell As Range
For Each cell In Worksheets("Sheet1").Range("Range1")
cell.Resize(1, 1).Copy
With Worksheets("Sheet1")
.Range("Cell1").PasteSpecial Paste:=xlValues, Transpose:=True
cell.Offset(0, 9).Value = .Range("Cell2").Value
End With
Next
End Sub
Here is my issue, rather than simply pasting one value from "Cell2" back
into "Sheet1", I would like to paste a "Range2" back into "Sheet1",
offsetting this value [0,9] from "Range1". However, when I put in a range
rather than a cell-reference in "Cell2", the macro stops working. Does anyone
have any suggestions as to what I am doing wrong?
In advance, thank you very much!
- 9
- Excel Misc >> Large Pivot Refresh problem Excel 2003I have a large spreadsheet which contains a large pivot
table (183R * 19C) -based on a largish data set (2730R *
11C).
I am unable to refresh this pivot table in Excel 2003 - I
get error message "There are too many records to complete
this operation." The spreadsheet refreshes fine under
Excel 2002 - why not on 2003?
Is this a known problem / is there a workaround (I cannot
reduce the size of the table).
Jon.
- 10
- worksheet functions >> Sheets don't always calculate automaticallyHi all
When I enter data Excel doesn't always calculate automatically - If I hit F9
when prompted at the bottom of the screen the worksheet updates OK.
There doesn't seem to be a specific pattern - it's not always the same cells
that do it.
I have calculate automatically checked on the options tab.
Thanks in advance
Andy
- 11
- worksheet functions >> Force "+" signI was wondering if there was a way to create a number format that displays the
plus sign on positive numbers (and the minus sign on negative numbers).
Thanx,
-plh
--
I keep hitting "Esc" -- but I'm still here!
- 12
- Excel Programming >> Createobject for FTPI am trying to send files generated in an excel application to a FTP site
I know how to create an InternetExplorer.application object
Is there documentation on the parameters needed to create a FTP object
(user,password, input directory, output directory...)
Thanks
AG
- 13
- Excel Misc >> Need formula Help soonB C E G
2 SubtotalRetail SubtotalCost SubtotalComsn SubtotalGrossProfit
3 =Formula(j7:j10) =Formula(K7:K10) =Formula(l7:l10) =Forml(m7:m10)
A B C D I J
K L M
6 Date PrdoctID QtyOut QtyNow Descpn Retail Cost CmsnGainLoss
7 26.11.07 200700105 2 8 Stonefgr 1300.00 500.00
01600.00
8 26.11.07 200700106 11 0 Stonefgr 3000.00 600.00
26400.00
9 26.11.07 200700068 2 22 Textiles 600.00 320.00
00560.00
1026.11.07 200700249 1 0 Paintings 21000.00 7000.00
14000.00
Sir, just same format of my worksheet. I need a formula in B3, C3, E3 and ...
I want to see what is the exact Gprofit on particular date after data>Filter
of Date Column
I hope I now cleared to you all thing and you will better understand.
Regards
RRS
- 14
- Excel Programming >> Rerieve Images from xls files and save themI have many files contain image in each sheet, (there are 3 sheets each
file) and I want to retrieve the images and save them to jgp files? how
can i do that? please my friend.. i need it immediatelly. due to the due
date..
Thank's
--
blackpassword
------------------------------------------------------------------------
blackpassword's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29357
View this thread: http://www.excelforum.com/showthread.php?threadid=490657
- 15
- Excel Programming >> Page Protection interfering with Hiding Rowshave a sheet that hides groups of rows based on a cell's information.
I'd prefer this to happen automatically through VBA, but currently I'm
stuck using a Toggle Button to hide/unhide these rows.
The problem I run into though is that that toggle button works
brilliantly until I turn on the page protection. Once I turn on the
protection I keep getting errors that the page is protected and can't
be updated.
And due to the fact that the people who will be using this know
absolutely no Excel at all and break formulas on the old sheet
regularly... well, the sheet has to be write-protected.
The code I'm currently using is this:
Sub DENYPASS()
Application.ScreenUpdating = False
Application.EnableEvents = False
If Range("B2") = 1 Then
Rows("11:41").EntireRow.Hidden = True
Rows("42:72").EntireRow.Hidden = False
Else
Rows("11:41").EntireRow.Hidden = False
Rows("42:72").EntireRow.Hidden = True
End If
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
with a private sub to connect it to the Toggle Button
Private Sub passdeny_Click()
DENYPASS
End Sub
|
|
|