I need capture a cumulative estimate as it moves from left to right across the page with each passing month. The estimate is pre-filled in for all the months. My task is to select from October to the current month and sum. Is there a way to do that in VBA?
View 9 RepliesI have a number result in a cell (d1) from a calcuting formula elswhere on the spreadsheet.
As the worksheet is constantly calculating the result in d1 keeps increasing and decreasing
I would like a formula in cell f3 that will give the result 1 if the d1 has reached the number 45. If cell d1 has not had the number 45 appear f3 will be 0
I have one excel file sheet in which we enter client data and another page in the same file that formats the data. Each new row is another client.
It is a simple formula in the data formatting sheet ='data sheet'A23 for example.
The problem is when we add client data above A23, say 2 rows, the format data page will follow the original cell and now say ='data sheet'A25.
How do I keep the cell as ='data sheet'A23 no matter what I do the original page?
Capture an average from the Grand Totals in a Pivot Table? If so what are the steps.
View 5 Replies View RelatedI was given a spread sheet with a number of payments on it, I was asked to take the "four" potential payments and only show one total payment. There are 2900 lines in my file and as you can see from the example the scenario repeats it self with the "four" payments all the way to the bottom. I would like to be able to add H2 to H5, total that number in J2 and delete row 3,4 and 5. This then has to be continued all the way down to line 2878 where I could then run a macro I have to delete blank rows cleaning my spreadsheet up. I would take a looping or user controlled macro (ctrl +) so I don't have to do this manually.
Sample_total.xls
i am needing to issue a dos command in excel? basically i need to send a target link to a file. i cant use a hyper link for several reasons, and this is the only way i know how to go about this.
View 2 Replies View RelatedI have main worksheet (target a) that I am trying to populate data from target(worksheet) b. The data I am trying to get from target b changes every month,(declining balance) based on a new month. So how can I get financial data from different cell each month from "b" into same cell in "a"? (so "A" # would be overwritten in same cell based on new # from "B". I have tried VLookup but can't be doing something correct.
View 2 Replies View RelatedI am using a piece of track changes VBA code mentioned on this site, which among other things creates a new column that reports the number of any cell that has been changed (e.g., $K$32). What I would like to do is, next to that cell, report the title of the row in which that cell appears. In other words, if someone changes cell $K$32, for easier reference I'd like others to be able to see that this cell appears in a row titled "New Sales". I'll be happy to clarify with more specifics if need be.
View 2 Replies View RelatedI have an excel work book with 6 tabs. I would like to have Excel move an entire row from one tab to another tab (removing the row and inserting it in the other tab). I.e. Example I have a tab with items that are marked as "Open Actions" so if I were to change the drop down to close. Excel would move that entire row of actions to the tab with the "closed actions" and insert into the next available row. Now if someone were to come back at a later date say no it should be reopened than I would change the drop down to open and excel would move that row back to the open actions tab into next available row. I tried a PIVOT table and no good I played with few macro and not.
View 5 Replies View RelatedI have a report which has a list of customers, each customer has 24 columns which represent the payment history over 24 months. If a payment has been made for that month the date and time (formatted correctly) will be populated in this cell.
Each customer has a product name attached to it so a product can appear several times. I need is a formula that shows the total payments recieved for a particular month for a particular product. For example.
I have managed to create the following flag which works a treat, it picks up a date an account was set up but looks at 1 column.
This may not be the best way to do this, but I don't know Macros or Pivot Tables.
I am looking for a way with formulas to do the following:
Within a workbook the 1st sheet is the data entry.
In another sheet that will total data from the data sheet is where I want to be able to total columns of data, depending on what is entered in one specific column:
Example:
Data Sheet, E2:E2999 is a unit number selcted by pull down tab entry.
G2:G2999 in the same sheet is where the data is.
Q: What formula would allow to total the data on the Total Sheet depending on what unit number is selected in column E on the Data Sheet and the data amount in column D from Data Sheet?
I need to set up an easy to use spread sheet for my office. It needs to be able to calculate the running total spent of fuel, as well as include any discounts we get and then calculate our total savings.So basically, total spent and total saved.
View 3 Replies View RelatedI'm sure this can be done but I don't think I have the formula correct. What I am trying to do is add each day's total while displaying a positive number if the total exceeds 6000.
View 5 Replies View Relatednumber for item detail level from category standpoint. For example, in the category sheet has total number per category and per month and in the item detail sheet has a list of item number with category. I want a formula to recognize which item belongs to which category then use the total number of category by month to multiply with % of total in column D from Item Detail Spreadsheet.
View 2 Replies View RelatedI am trying to create a very basic workbook that has 2 worksheets. one is a daily input for tonnes, that then just gets cut and pasted to a different program, and the other worksheet is the running total. i.e., it adds up every time you update it.
been trying to figure out a macro so when you press the update button it then just updates the monthly total.
Colac Production.xls
I have a workbook where data is constantly changing
If two cell values become equal I want to capture and keep the value that was in another constantly changing cell at that time
The IF argument will only caapture the moment and then return to the IF False command
I have excel formula which let for different numbers appear in the same cell,
=IF(OR(F7={10,10.5,11,11.512,12.5,13,13.5,14,14.5,15}),5,IF(OR(F7={15.5,16,16.5,17,17.5,18,18.5,19,19.5,20}),11,IF(OR(F7>=20.5),15,"")))
for example we have AS1 cell in which appear first number 15, then it change to 5, then it change to 15, then it change to 11 etc. What i need is to capture and lock just the first shown number (in this ex. it has to be 15) and put it to another cell (for ex. cell AT1) Maybe there is any formula ?
I have a lot of workbooks with a date as a name (eg. 2004-08.xls or
1999-03.xls). I would like to capture the date portion of this name
with a formula in sheet 'Date' cell 'A4'.
I have streaming data coming into my excel spreadsheet. The data is in 1 cell and its a number that changes almost every second. How can I capture and store this data.
View 1 Replies View Relatedi have a long text in A1 field and i just need to capture some characters in between. Below is the example.
Raw Data:
Cell A1 (r1,c1) = Target: ABC, CustomerOrder, Results: BDE, LastUpdate: 12Dec08
I want to get the Result as below:
Cell A2 = Target: ABC Cell B2 = Results: BDE
Can this be done in Excel Cell format? Or do i need to do it in MS Access?
I am preparing a template on the con-call done with various states. What I want to do is to capture their log in time to the call.
1) As soon as participants log in, we need to input the time of that moment. Click or double click with the cell should give me the current time.
Then;
2) Need to calculate how many participants, logged in to the call On-time by considering the log in time captured.
3) Similarly, we need to count the participants joined within 10 minutes of call and who are late comers.
I would like a formula to capture all values >=50 to <=99 in a row.
View 14 Replies View RelatedI want to extract just the last name from a cell that contains the full name and put just the last into a seperate cell.
WHAT I AM DOING: i copy the contents of a internal screen onto a spreadsheet that i use to check various peices of info and calculations. when i save I like to save my sheet with the last name and then acct number. The internal system screen combines the customers names into a single cell. Right now i have to type the last name into a new cell and have a simple save macro that concatenates that last name with the acct number and saves it into the appropriate folder.
The field i am pulling from is always formatted with FIRST NAME then MI (IF PROVIDED) and then LAST NAME. So when i dump the screen contents into excel A20 may be MIKE SMITH, or MIKE T SMITH. so i need something that looks backwards in the cell and stops at the first space and dumps SMITH into another cell of my choosing (B1 in this case)
In my sheet, i have a list of machines in cells A2 to A5 & list of Plants in cells B2 to B5 as below
Col A..........Col B
Mach.........Plnt
A...............1
B...............2
C...............3
D...............4
What i want to capture is the last machine selected by clicking and also the value of the last plant selected by clicking
I want the machine last selected (clicked) to be in captured in cell D2 & the Plant last selected (clicked) to be in captured in cell D3.
i.e if i click Machine C in column A then go the column B and click Plant 4
Then D2 cell should show C and D3 should show 4
I would like an input box that asks users a select a range of cells....After this certain range of cells is selected....I would than like that range to be copied to a different sheet.
View 9 Replies View RelatedI have a cell (for example, "A1") which is inserted with a WINROS formula to retrieve some data into my spreadsheet. And it is running live at all time.
Actually, I can't think of any formulas to capture the value from cell "A1" to "B1". Because I do not want the value that I captured into cell "B1" running live. I just want the value "B1" fixed after captured.
I have a userform with a 3 refedit controls. Each needs to capture a range from a different workbook (already open - only not active).
Is it possible to configure the refedit to capture a range from another workbook (either thru window menu or ALT+TAB)?
I notice that InputBox type:=8 allows it - I'm a little suprised that default refedit doesn't.
I need to find a term "aa" and upon finding this term, the term on the right must be "bb" before we capture the number. Output will be 15 for this case.
Note that we do not know which cell "aa" will occur in, but the term "bb" will always be on the right of "aa". Can anybody help me with this? I have attached an example for your reference. Hopfully by modifying the below code, i can get the value i desired.
Sub test2()
Dim xx As Variant
Set rng = Cells.Find(What:="aa" , After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
True)
If Not rng Is Nothing Then xx = rng.Offset(0, 3).Value
MsgBox xx
If rng Is Nothing Then xx = 0
MsgBox xx
End Sub
How to modify this code so that I select only one column triggers the time stamp update? For e.g. if i make any changes in column A, the date stamp is updated in the corresponding cell in column B. Basically, I am trying to narrow down to only one cell in the row, but it should work for any row in the sheet.
Refer to the below post: [URL] ....
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row > 1 Then Cells(Target.Row, "B") = Now()
End Sub
I have a moving average spreadsheet that I am having trouble with. In column F of 'Data for Pivot' Tab, I have a set of data that doesn't begin populating until 101 data points (cell C8 in 'Mov_Avg_Chart' is the input; in this cell F138 of 'Data for Pivot' tab) after data begins populating in Column E of 'Data for Pivot' Tab (in this case data begins populating in Column E in row 38).
What I would like to do is have the data in cells F38 to F137 = the value in cell F138. From there on forward (cells F138 and onwards), I would like the formula to calculate data in the same way as it is currently calculating it. That is, I would like the values in cells F38 to F137 be 0.00110 (in the current example). However, the data will be constantly changing. Thus, sometimes the data in column F will begin at a different cell than cell F138. In essence, I want the formula: when column E begins populating data but column F has not, I want those column F values to equal the value of the first populated value in column F (which is usually numerous cells below this value).
I just can't seem to get this one right after spending considerable time on it. I would appreciate any help you can give on this.