As part of a project, I need to come up with a meter reading calculator. I start of with 3 base meter readings (A2, F2, K2), which would be read from the meter. 3 reads are needed in case the consumption changes dramatically at some point. I then need Excel to work out the reading for dates in between. The formula needs to be dynamic enough so that I can change any of the grey dates to what I want.
The estimates need to be calculated using an average daily consumption. This figure needs to be displayed in D9.
It would be preferable if macros weren't used. If its possible to explain what to do without an attachment, that would be good too.
how to create a progress meter based on yes/no (completed/not completed) responses? If 2/10 responses are completed, then the progress meter would display 20% or 20% of the bar would be red. Is this even possible in excel? I've used conditional formatting to create a progress meter but can't figure out how to make a progress bar based on responses.
I tried to copy a date from Temperature & Humidity Reading file using the formula ='[Temperature & Humidity Reading.xlsx]Sensor 7'!$C$2 to Summary file
if the date format is like this 6/10/2013 12:00:00 AM the result is fine, but when i try to copy that formula for the succeeding dates the results is same from what i copied.
I'm building a calculator of sorts for dates. I have a start date in A1. Next to the start date I have a list:
10 days from -start date- is: 15 days from -start date- is: 20 days from -start date- is: and so on...
I need a formula to return a date that is however many days specified from the start date. If that date lands on a sat or sun I need it to return me the date for the monday after. For example, if my start date is 10/15/09 (thursday) and 10 days from that is 10/25/09 (sunday) I need it to return the date of 10/26/09 (monday). I also need it exclude a range of holidays i have listed.
I have a formula that reads the date in Colum B on 3 different sheets. The data on the seperate sheets is pulled from a SQL data base and formats the date wtih the time after it.
11-13-2006 10:47
My formula is looking at dates and subtracting them to give a total count of the occurance of the word "Yes" between dates.
I am trying to put together a spreadsheet that works out values based on a persons age or service length.
So in I have the following data in columns starting at B4 and ending at k4:
Date of birth; start date; text; text; text; text; Weekly Pay; Start age; Current Age; Years Service.
The formula I need to provide in l4, m4 and n4 comes from the following rules:
* 0.5 week's pay for each full year of service where age during year less than 22 * 1.0 week's pay for each full year of service where age during year is 22 or above, but less than 41 * 1.5 weeks' pay for each full year of service where age during year is 41+
i m making a very small calculator. It's a little hard to describe, but I need to look like the following Let's say the individual got here on 1 Jan
Phase 1 is for days 1-14 last for 14 days Phase 2 is for Day 15-35 last for 21 days Phase 3 is day 36+
This is what I need it to look like: All the phase information will compute automatically after inputting the date arrived. In addition I would also like to be able to change one of the phase dates and the remainder of the phases compensate according to the newly entered date.
Date Arrived = 01 Jan 09 Phase 1 = 01jan09 Phase 2 = 04Feb09 Phase 3 = 05 Feb09
I am trying to put together a userform based time card calculator. User inputs the time in the time out and how long of a lunch. Then the program will display total hours worked for the day. Ive attached what I have so far. The only thing that is not very clear is that I have one hidden textbox for the lunch. It is there only for calculating and the visible one is going to inc by :15 min.
I’m trying to add a shipping charge calculator based upon weight to a worksheet. I was trying to do with with VLOOKUP (my first attempted use of this) so I created a second worksheet called ‘Shipping’ which has the weights in column A (Weight) and the corresponding shipping cost in column B (Price). Up to 70 pounds, column A increases in two-pound increments (A2 through A36, 2 through 70 lbs); from 70 through 150 pounds, the increases are in five-pound increments (A36 through A52, 70 through 150 lbs). The total weight to be shipped is derived from SUM(D2:D30), located on the first worksheet called ‘Order’ in cell D32. In cell E32 I have entered this formula: VLOOKUP(D32,Shipping!A2:B52,2).
My problem is if Order!D32 returns something like 4.5, the value for four pounds is the result, while the six-pound charge should be the result. If I round this value up to 5.0, 5.5 or even 5.9, I get the same result.
I know I’m not supposed to guess at what I need to do, but these are my thoughts: 1) I need to modify the VLOOKUP formula to select the next higher value, i.e., so the 6 lb charge is selected for E32 when D32 contains 4.5 lbs, or the 85 lb charge is selected for E32 when D32 contains 82 lbs, 2) I need to add a ROUNDUP function to the SUM formula; this would need to round up to the next even number up to 70 lbs and then in increments of five above 70 lbs. I don’t know if either approach is correct, and in any case, I couldn’t figure out the syntax for either of these. I’ve attached a sample spreadsheet, Shipping_Charge.xls.
I have a code that I use to import a txt file, and the dimensions is being imported as millimeters. However I need them to be converted to meters (thus divided by 1000)How can I incorporate that?
I am trying to incorporate one of Andy's Progress Meters from the DN workbook.
There is so much code in the workbook that i don't have a clue where to start in terms of how to extract just what is needed for the XP bar and incorporate that into my code
I have a VBA code that copy data from multiple files into my workbook.
I find the first file, copy the ranges I need into my workbook and continue to the next file until all files are done.
The copy operation in each file is - row by row - because I need to ask questions about each row.
As you understand - what the user sees is a flickering window because I activate the source workbbok -> copy the data -> activate this workbook-> paste the data.
I want to avoid the flickering.
An acceptable solution could be to generate a pop up message box that will say "data is being copied. Pleasd wait while operation is completed". BUT - I don't know how to identify that the action is done and message box can be closed.
I'm trying to workout how to take a known initial date a repeating frequency and work out the next due date from today.
Example
Initial Date :- 1st of January 2014
Frequency :- every 5 weeks
Current Date :- 9th of March 2014
Next Due date should be :-12th of March 2014 (if I worked it out correctly from my paper calendar)
I want to use a cell formula to do this for different initial dates and varying frequency periods (the frequency will always be whole weeks i.e. 1,2,3,4,5,6,7,8,8,10)
I'm trying to write code that looks at the range W(rowid):Z(rowid) and lists each cell value in a listbox. The row id is coming from the listindex of combobox1. I know the rowid part is correct and working because other operations in my worksheet are using it fine. Here's what I've written so far:
With Worksheets("DATA") For I = 0 To 4 ListBox3.AddItem .Range("W" + I & rowid) Next I End With
In sheet 1 data 123. In sheet2 there is also data 123. In sheet3 will automatically data that is same 123 after reading sheet1 & sheet2. Any data with figure that is same in sheet1 and sheet2 will be reading in sheet3.
the attached workbook, I am looking for M19, N19 & O19 to read from different locations depending on how many cells are called on when info is entered in P8.
The following code reads text one character at a time, and each character is determined either to be a blank space or to be any other character. If it's determined to be any other character, then the character is added to the active cell. If it's determined to be a blank space, then the active cell becomes the cell one column over. The idea is to have different words written in columns next to each other.
The sample text file I'm reading from reads: "text file". The file has only those two words. One space between them. No space before the first word, and no space after the second word. The following code compiles and runs. BUT the result is that the word "text" is in column k, not A. And the word "file" is in column J, not b.
I have a drop down list in my excel sheet that contains names of manufacturers. What i would like to do (using vba) is display some details of each manufacturer that will be copied from a different sheet.
So my question is, how can i read the string from the drop down list on one sheet and then copy a cell from another sheet.
I am building a data base. I have a tab where you fill out of a form and then click an activeX command button to write to the data base. I am wondering if there is a way to record how many times to button is pushed. Each time the button is pushed, I want to record the data from the form into a single row in the data base without writing over old data but I do not know how to read how many rows of data are already there.
I am using the find or search function but I need to read my text string from right to left (from the end of the string, instead of from the beggining). Is there a way to do this?
I have a spreadsheet with about 200 lines or records and I have added a checkbox to the end of each record. I need to the code to check the checkbox on each line to make a decision on how to process the data, but I do not want to read in a True or False value for 200 check boxes in every step of the loop. I think I'm close but I can't get the following to work...
This is basically what I want:
For i = 1 to x Step 1
IF me.Shapes("Checkbox" & i).Value = True Then
'Perform Action
END IF
Next i I get an error when I hit the IF me.Shapes..... line
I have a table where sometimes cells are merged. To the right they are not merged, but I need to link to the value of the merged cell. How can I do it if I don't want to do it manually?
Example: Say A1:A4 is merged, A5 is just normal. I want a formula using those values, say in B1:B5. Now just filling down the formula would reference A1, A2 and so forth. This is wrong.
Is it possible for VBA to read data from a clipboard so that it knows what sheet to paste the data to? Currently we run a financial tool and 2 reports are ran and I put in a bottom so users could easily just paste special values into the correct place. Sometimes the users forget if they copied the balance sheet data or income statement data and currently its not a big deal because there are no columns to the right of where it is pasting so it isn't screwing anything up and they just delete the data and paste it to the correct sheet.
I have thought of 2 approaches I am going to take, but started to wonder if one could read the data from the clipboard before the paste.
My 2 options would be:
Option 1. Paste data to newly created sheet and check if it is balance sheet or income statement and then move from newly created sheet to correct sheet and delete newly created sheet. The user wouldn't see any of this because I would disable screen updating.
Option 2. Learn how to use Undo within my VBA or keep the Undo cache with items in it as it typically clears when VBA is executed. That way if they paste it and it is wrong I can just undo the VBA paste.
Option 1 I am confident I can handle. Option 2 I can research and feel I could do, but if it comes a time when a sheet has data I do not want to paste over then this option is no good.
I would like to hear any comments or feedback on this and if it is possible to read data off the clipboard so it knows where to paste.
I need a little bit of help here ... I can read and process a .txt file from my Excel VBA program, but I am having trouble as follows :
The file I am reading contains a number of heading lines, followed by a number of data lines.
All of the data lines end in a date, so I can use the code ...
If IsNumeric(Right(MyDSRec, 1)) Then ... to pick up the data lines & ignore the heading lines. This is fine and works perfectly.
However, the .txt file that I receive is out of my control, and is prone to error, in that the data line occasionally has what I assume is a "Tab" character after the last date, which means that the line above does not recognise the last character as being numeric.
If you double click "06" at the end of each line on the example below, you should be able to see what I mean (in the .txt document, if I place the cursor at the end and press backspace, all characters at the end are deleted, back to the 06, so they are not individual spaces) ...
I am running into one of the strangest issues I have seen in a long time. I have a user defined type that I utilize in VB6 and write out to a binary file....
I am have a 4x2 table and would like to use the values in the table in numerous procedures. i feel the best way do to this is to record the table as an array and be able to 'paste' or call the array anytime i need to. However, i am unable to read the table into an array.
1. how the array works, 2. how i can read values into an array and 3. how can i 'paste' or call the array at a later stage.
person is sick on 10/9/06 and on 10/10/06 . i want that to be read as one sick. now if the person is sick on 10/09/06 and then sick on 10/11/06 that would be read as 2 sicks. what i need is the consective dates to read then one to read the dates that are not consectutive . giving a total for each one.
