Parameter To Reference Certain Cell To Input Date Into URL?
Apr 3, 2014
I have a workbook that is pulling data for every hour of the day from an internal website. The macro is built to pull data 24 times (each hour)
ex.(http://url/[""2014/04/01""]/starthour0/endhour1/)
(http://url/[""2014/04/01""]/starthour1/endhour2/)
(http://url/[""2014/04/01""]/starthour2/endhour3/) and so on.
What I am trying to do is set up a parameter that will reference a certain cell (Master!K5) which will contain the date I need to pull. I want to be able to have that cell referenced automatically and input the date for each URL in the macro.
View 2 Replies
ADVERTISEMENT
Apr 26, 2008
How do I adjust the VB code below so when the user clicks the command button they are prompted to set a limit.
i.e the code only runs til it hits the limit and stop (instead of running until it there are no more x in the columns)
Row 2, Col 9 stores the column numbers (listed as 1, 2,.....100) so thought this could be used in some way.
1. how do i set up the code so a user can enter a parameter value e.g 50 (which is the limit but also includes the values in column number "50")
2. how do i adjust the code so it take the parameter value and stop when it reaches column number "51"...
View 9 Replies
View Related
Apr 2, 2009
I'm trying to use Solver to determine an input parameter of the BINOMDIST() formula that provides the exact answer I want. It works for some inputs and outputs but not for all.
For example, I want 4 successes out of 7 trials to yield a 95% answer, meaning I need to itterate on the "P" input in the formula [=BINOMDIST(4,7,"P",TRUE) should result in .95]. Using solver it iterates successfully to .3413.
Now, change 4 to 6, and solver drives "P" to an answer that causes a #NUM result (P= 4.023), however I can manually input 0.6518 and I get the right answer.
I've tried adding constraints that won't let the input go outside the bounds of 0 and 1.0 but it does anyway.
View 9 Replies
View Related
Mar 21, 2014
I have a combobox in a excell sheet. It is possible to pass a parameter in the input range instead of Parm!$B$1:$B$10
View 2 Replies
View Related
Dec 4, 2009
I have a macro that adds a row with predefined formulas and formating. The macro is launched by clicking on a button. However, I would like to make it possible to add more than one row at a time. My plan to do this was to use the number of selected row as input to the current macro. If the user selects row 1,2,3 and 4 (or 15, 16, 17 and 18, and so on) four new rows should be added. I would just add;
View 3 Replies
View Related
Jan 27, 2014
I have a data table that looks like this:
RATE
Monthly
289.68
1.00%
262.81
2.00%
276.04
[Code]....
all the RATE numbers are manually entered.
now, if i reference the 3.00% number under the RATE column to the INPUT cell that I entered when I setup the data table, this happens:
RATE
Monthly
289.68
1.00%
262.81
[Code]....
this is obviously not correct, it is the value for the entry above.
and...
if i make ALL of the RATE cells then relative to the 3.00% one... (in other words 2.00% is actually =A5-0.01 and 4% is actually =A5+0.01)
i get...
RATE
Monthly
289.68
1.00%
262.81
2.00%
250.00
[Code]...
this is weird b/c the number for the first one and last one never change, but all the ones in-between change.
incidentally, the formula which is being solved is simply a 30k loan pmt for 10yrs ( =PMT(RATE/12,120,30000)*-1 )
View 5 Replies
View Related
Aug 31, 2012
I have a situation where I need a macro to reference another cell in the sheet if the cell is left blank. If the user wants to edit the value, they have the option to manually input a value but if they decide to leave it blank, it autopopulates the referenced cell.
View 1 Replies
View Related
Jul 31, 2013
I though I could do this with a nested IF statement but it is too cunfusing for me. What I am trying to accomplish is this:
Experiment
Is Steward
EU ID
Location
Data Quality
GE
Entry Order
[Code] ........
I want to have a screen pop-up asking me what my limit < would be for column "ESTCNT" so if I put in 25 or any other number that it would highlight all the rows that are less than 25, then look at the row above and below and if it matches the same number (that is in the cell "Range" of the highlighted column) in column "Range" then copy that row to a new sheet. Meaning all tha rows that match the "Range" would be in the same new sheet.
The rows might be different lengths and that there will not always be a number in cell "ESTCNT". Column headers will always be the same but might not be in the same column each time. And if it is not to hard once it is completed to find column "SPPLOT" in the new sheet created and asking what I want to autofil the column with.
View 2 Replies
View Related
Aug 14, 2012
I have a input box that prompts a user to enter a date of a new month - it has to be the 1st of a new month. I have validation that it is a date that has been entered but then i want to validate the date entered is a month ahead of a date in a cell range on a sheet.
It is a monthly reset so it has to roll on from the previous month.
Here is what i have currently but it isn't working.
Code:
' Get user to input the first day of the new month to populate all dates with
dNewMonth = InputBox(Prompt:="Enter first Day of the new Month. Must be the 1st of the Month e.g. 01/10/2012", _
Title:="Enter Date")
' Validates the entered date is a valid date
If (IsDate(dNewMonth) = False) Then
[Code]...
View 1 Replies
View Related
Nov 7, 2002
I am attempting to use MSQuery to extract records from an Oracle transaction table by passing user defined Date/Time field parameters.
So if the date range for the query is for Fiscal Period 10 of this year, my parameters would be:
[StartDate] = #2002/10/1#
[EndDate] = #2002/11/1#
Daily transaction activity is in the early morning hours ending at 6:00AM so my Criteria is:
Between [StartDate] + .25 and [EndDate] + .25
Note: The +.25 represents 1/4day = 6hrs (6:00AM)
If I use the hardcoded Start/End Dates, my query works fine, but if I try and
pass the dates as parameters I get "ORA-00932 inconsistent datatypes" error. And I can't seem to pass the Date/Time combination successfully....
If anyone has encountered a solution for how to pass a Date/Time Paremeter into MSQuery I would love to hear how you did it.
I'm starting to pull my few remaining hairs out!
[ This Message was edited by: Tuner on 2002-11-07 12:23 ]
[ This Message was edited by: Tuner on 2002-11-07 12:28 ]
[ This Message was edited by: Tuner on 2002-11-12 12:51 ]
View 6 Replies
View Related
Sep 22, 2009
I have the following info in a table:
name | doc date | doc value
a | 2009/01/01 | IN111
b | 2009/02/04 | IN222
c | 2009/02/05 | IN333
a | 2009/01/05 | CN111
d | 2009/03/01 | IN444
b | 2009/03/01 | CN222
a | 2009/04/01 | IN555
Firstly, I need to find the LAST DOCUMENT DATE for "a" where #doc_value starts with IN*** (invoice). Manually, I can see that it would be "2009/04/01", but my spreadsheet runs 6000+ entries. I need a function to do this.
Secondly, I need to find the corresponding #doc_value for that date (in this case, IN555).
View 9 Replies
View Related
Oct 20, 2012
I need to input a future date and time into one cell. For example, if I have a member ship that ends on October 20th at 3:00 pm exactly the kind of format you get when you enter the function "NOW". I would like to put that into one cell and then be able to add and subtract from it.
For example I would like to do something like this, if I had inputed the date and time in A1:
"=Now-A1", it would be cool if I can get the remaining hours or minutes along with how many days are left but not completely needed.
It would also be beneficial if I can drill down further and do it by time zone as well.
View 2 Replies
View Related
Apr 1, 2014
For example i input in cell A1 April 1, 2014 in cell A2 it must put Tuesday.
View 2 Replies
View Related
Mar 5, 2008
Cell I5 has the sum of cells A5:H5. Every time the value of cell I5 reaches the next million, I'd like for the date that it reaches the next million to be displayed in J5.
View 9 Replies
View Related
Apr 6, 2012
Basically I have a row of dates
so,
B1 - 01/04/2012
B2 - 02/04/2012
etc...
And I have tabs for each of those dates aswell.
Basically I want to pull some figures out of the tabs but use the dates in the reference.
So i need a formula that will look at the date in cell B1, use the date to find the tab and then say find the value of cell G12.
View 4 Replies
View Related
Jul 22, 2011
I have a pretty elaborate spreadsheet for my bills. One cell adds up all the bills due during a specific pay period using named ranges. The formula looks like this:
=SUMIFS(NextPmtAmt,NextPaymentDueDate,">=9/9/2011",NextPaymentDueDate,"<=9/22/11")
It works, but when I have to populate cells for upcoming pay periods, I have to go in an edit the dates manually. I'm lazy, and want to simply reference a cell with the date in it instead of typing the whole date out in the formula. What I'd like to do is this:
A2 contains 9/9/2011
B2 contains 9/22/2011
=SUMIFS(NextPmtAmt,NextPaymentDueDate,">=A2",NextPaymentDueDate,"<=B2")
but it doesn't work.
View 6 Replies
View Related
Aug 10, 2013
I'm using the following formula which is entered by using VBA on the worksheet change event but the cell reference 'A2' is not changing for each row, i.e. A3, A4 etc.....
Code:
=MAX(IF( 'Device Use - 4 month Period'!$A$2:$A$20000=A2, 'Device Use - 4 month Period'!$C$2:$C$20000))
View 4 Replies
View Related
Jan 16, 2008
Using cell contents in formula to Populating cells with values from closed workbooks
I am trying to create a summary table of data (e.g. orders) from previous weeks. In the table I am creating, the left hand column contains the Monday date of the week to be summarised along the row. I want the cells to show the values from other spreadsheets when those spreadsheets are closed, but I want the formula to pick up the date of the week that is in column A. I have attached a sample spreadsheet to try and make it clearer.
View 2 Replies
View Related
Nov 11, 2009
I'm trying to link two different budgets into a third spreadsheet. The final step I'm attempting to accomplish is have an if-and statement reference just the month from a date-formatted cell. One of my departments tracks their budget on a daily basis, i.e.
11/2/2009, 11/3/2009, etc. rather than just 11/2009. I want the if-and statement to return the budget from all days in a given month so i can apply the formula to the whole year, broken down monthly too. I saw a question similar to this one but didn't see any responses so I'm not sure if it can be done.
View 6 Replies
View Related
Jun 25, 2014
I have this formula counting down days until my next fire drill:
[Code].....
However... Every time the drill is done I need to change the date in the formula for the countdown. That I can have it reference a cell instead?
My initial idea was a formula like this:
[Code] .....
(however this code doesn't actually work ...).
View 5 Replies
View Related
Jun 11, 2009
The spreadsheet attached is a timesheet I use. The last column on the sheet (IV) keeps a running total of time spent on each project. I need a way that i can find out how much time was spent on projects in specific timeframes (eg 01/01/09 - 07/01/01, or 01/01/09 - 31/01/09). my initial thought is perhaps i can have a "from" cell and a "to" cell to input the dates i require and it will total the relevant cells in the rows below.
NOTE! This workbook uses VBA to auto hide any column which are dates previous to today's date so you may need to unhide them to view times logged.
View 2 Replies
View Related
Aug 29, 2013
I have the below code to get the difference between todays date and the date located on sheet "requests" "B3". The code returns the value: -28040498
Code:
Dim MyODateRng As String
Dim MyODate As String
MyODateRng = Sheets("Requests").Range("B3").Value
[Code]....
So i gues with the first code there is something wrong with the date formating. But i do not understand why the second code does not return simply return the value: 1.
View 7 Replies
View Related
Jun 1, 2014
I have my ms access and ms excel which is connected each other .
=> now in my ms access have a table name (tblMonth) has columns (year,MonthNum,StartDate,EndDate)
HERE: sample in january(1) and feb(2)
YearMonthNum StartDate EndDate
2014 130/12/201305/01/2014
2014 106/01/201412/01/2014
2014 1 13/01/201419/01/2014
2014 120/01/201426/01/2014
2014 127/01/201402/02/2014
2014 203/02/201409/02/2014
2014 2 10/02/2014 16/02/2014
2014 217/02/201423/02/2014
2014 224/02/201402/03/2014
=> and for my ms excel has its columns (year,month,date) in this if i input the date that is base in the database range from StartDate TO its EndDate will automatically put the month and year which base also in my database (year,MonthNum)
HERE:
A1 B1 C1
Year Month Date
A2=2014 B2=January C2=01/01/2014
View 3 Replies
View Related
Feb 19, 2009
I have attached my workbook. What I would like to do is to have an input box to where you input the Material, Gauge, and Size and then have it return the rest of the values on Sheet 1. The material is listed as the worksheet name and the other information is inside that sheet. For ex... I would enter in Stainless, 7, 48x120 and it would give me the rest on sheet 1. I am totally lost, I have tried several different things but am coming up with nothing.
View 14 Replies
View Related
Jun 19, 2014
I'm trying to make a vba that would save the file with the end date that will reference to a certain cell that user selects it. Working Sheet is a name of a File, and Current Sheet is a name of the panel where user will select the date.
ActiveWorkbook.SaveAs Filename:= WorkingSheet("Control Sheet").Range("D3").Value & Format(Date, "yyyymm") & ".xlsm", FileFormat:=51
But here i seem to missing the location
And I tried this one:
ActiveWorkbook.SaveAs Filename:="X:Work2014WorkingSheet& Format(Date,"yyyymm"). & ".xlsm" _, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
but it keeps popping up as 'expected end of statement' for yyymm?
View 8 Replies
View Related
Mar 2, 2007
I download & import CSV files/ sheets from bank, visa accts etc & I am trying to automate the processes I use including ensuring I (& others) will use the same naming conventions eg visa "yymm" = V0701, Direct Cedits = DC0701 etc. I am having trouble renaming or ensuring the active sheet is named according to the date in A3 eg in the example I want the sheet to be renamed V0702 as the date in the cell is 02/02/07 "dd/mm/yy" format ( imperial system). In BOLD is what I have & the renaming is messy becuase I searched the forum, Walkenbach's bible & help menu.
Sub VisaMonthlyAcct()
Dim shtName As String
Dim newName As String
ActiveSheet.Activate
'ActiveSheet.Name = shtName
newName = "V" & Format(DateValue("A3"), "yymm")
'Old name for future reference = Visa0701 (2)
ActiveSheet.Name = newName
Worksheets(shtName).Name = "V" & Format(Range("A3"), yymm)
'Worksheets("Visa0701 (2)").Name = "V" & (FormatDateTime(Range("A3"), yymm))
'To select Date column & format
Range("A3:A66").Selection.NumberFormat = "dd".............
View 2 Replies
View Related
Feb 20, 2008
i want to return a value from a cell in another workbook where part of the the workbook name is determined by a date set on the current open sheet.
so on my open sheet i have a cell where the user can change the date and i want the below formula to then use the date to look at the relevant file.
any ideas would be appreciated. know how to do this in VBA but no clue with formulas.
what i am trying to do is concatenate part of a file name and a cell value which contans a date
=VLOOKUP(A4,[accountsummary17022008.xls]Sheet1!$A:$B,2,FALSE)
View 3 Replies
View Related
May 13, 2009
I want to create a macro which asks some information like functions do. For example, I would like the macro to ask for some data to be filled by the user. For that purpose I thought about using the inputBox vba function. I wanna fill the field in the inputBox with a reference to a range from an excel sheet. So, I click on a cell and then I try to hold ctrl + down cursor to get all the range starting from that cell on until the last consecutive filled cell on the sheet. Nonetheless, the problem I have is that when I do ctrl+down cursor, I just do not get the reference I want, because it seems that the ctrl+down cursor combination does not work in the inputbox for filling a reference to a range of cells. Does anyone know some workaround to achieve this?
View 9 Replies
View Related
Oct 11, 2008
I need to create formulas that reference a single date and output date ranges. The objective is to have a person input a Monday date in any given month and receive a four weeks out worth of dates and ranges. For example: In a lone cell, the person inputs 10/13/08. Automatically, the sheet produces the next full week range: October 19 – October 25 in a single cell and also produces a cell for each date. Example: Sunday 19, Monday 20, Tuesday 21, etc…. It should look like:
Monday Date:
10/13/08
October 19 – October 25
Sunday 19
Monday 20
Tuesday 21
Wednesday 22
Thursday 23
Friday 24
Saturday 25
and then repeat for three more weeks. I thought I had it figured out until the month changed. The dates continued in October instead of adding a month. This report will be ran weekly, so simply adding a +1MONTH to some cells will not benefit me as I’ll have to change the formula every week. I want the formula to compute the data without any manipulation over the next several years. The only change will be the Monday date.
View 2 Replies
View Related
May 30, 2014
Sample Chart.xlsx
I need to enter data every month for the following month (i.e. month end close, for previous month). For this example, let's say I just entered (in May) data for April. Current year is 2014. The attached above is what I have currently, and what I want to achieve. All of the cells have formulas, but I basically don't want data and variances shown for time periods not yet uploaded, so in my example, I should only see filled-in cells for January-April, and not May-December (keep in mind that 2013 data should remain, as these are time periods in the past). I've thought about many scenarios including: modifying the formula but not changing what it is currently doing (essentially gathering and adding data from a different worksheet), conditional formatting to "white out" all cells for months where 2014 data is not available using NOW or similar function. how to do it most efficiently without disrupting what's currently in place. T
View 14 Replies
View Related
