I just want to ask the user to enter the date in a mm/dd/yy format, however, the date received is some how changing.
For example I execute the following code and get 8/01/14 instead of 8/21/14
[Code].....
I have even tried pulling the date from a formatted cell with no luck.
I am trying to find out if it possible to directly "access" (no play on words here) data stored on MS ACCESS by using Excel's formulas.
I have a set of tables stored on a Microsoft ACCESS 2010 database, and I want to use Excel to analyze that data, using formulas, such as SUMIF.
Is it possible, once I have established an Jet OLEDB connection from Excel to Access, to then directly exploit the data stored on Access without having to copy the data to Excel, and then use it.
I would like Excel to solely act as a tool to analyze, not to store data.
I am using VBA to export an access query into excel, the query works fine in access but when importing the data into an excel spreadsheet, it doesn't display the date column headers in the correct format.
The problem I have is that the dates 1 to 12 are displayed the wrong way round eg:
In Access date column headers from query are:
09/01/2008, 11/01/2008, 12/01/2008, 14/01/2008, 15/01/2008 etc.
But when exporting to excel, the above dates are shown as:
01/09/2008, 01/11/2008, 01/12/2008, 14/01/2008, 15/01/2008 etc
It seems to be changing round the dd/mm when I export, but only upto 12 when the day is 13 or more it is displayed correctly.
Public Sub bttnDMA_Click()
'DMA Figures in Excel format
'Creating the Recordset
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
Dim MyRecordset As New ADODB.Recordset
MyRecordset.ActiveConnection = cnn
Dim MySQL As String ....
I have made a Rota of sorts using Excel 2013 Desktop Edition for my charities volunteers (and stored it as a shared file via office 365 server that they can download and edit) and this rota is populated by our volunteers manually. Each day our controller needs to check the file to see who is on duty at that time.
How the Rota is populated.(the bit i managed to do myself)
The volunteer (Person A in this example) would open the excel file and go to the month they wish to choose a shift for (ref worksheet: FEB in this example). They would then pick a shift that suits them and click on the cell (ref: F32) that shows a vehicle available, then from the drop down list they select their name and then save and close the sheet.
Rather than our volunteer controller going through the sheet for the current month (ref worksheet: Sheets JAN to DEC) I would like them to use the first sheet in the workbook (ref worksheet: DC Info Page) to get an instant view of which volunteer is currently on shift.
My current problem
I don’t know how to make the excel file do the following
Search sheets JAN to DEC (ref cells: C4:I58 on each sheet) inclusive for the cell that contains today's (current actual) dateCopy the 8 (eight) cells below the cell that contains today's datePaste the copied cells in to the relative cells (ref: C8 to C15) in sheet one (ref worksheet: DC Info page)
I would also like this to be done automatically so the controller does not have to click on anything after they open the file. But if it needs a button to process the request, one could be added to the worksheet (ref: DC Info Page)
MS Query can only hold 16 joins and some other restrictive requirements. Has utilized Stored Procedures to execute a query and have the results returned to Excel? Query in Excel seems very limited.
View 2 Replies View RelatedI have a Excel 2010 spreadsheet that contains 3 columns of (£) values. I am wanting to highlight a cell if one of these values do not match the other 2 columns. e.g below I would need the middle column to be highlighted as it does not match the other 2 cells.
25,449.47
25,451.65
25449.47
The thing to point out is that it can be any of the cells that do not match, not necessarily the middle column and occasionally all cells will not match and all need to be highlighted.
I am sure I need to use the Conditional Formatting, but cannot figure the formula.
Trying to use Excel Data List to create a database style report. IE. Originally blank sheet, which is only populated by data containing data matching "filters" input into cells ( say A1 & A2 )
I.e. A1 = Delivery week to be filtered by, and B1 Manufacturer Name
So if I type week "1" into A1 & Manufacturer "Microsoft" into A2, it will show a table only containing data Microsoft, Week 1, and associated data for those lines across the screen.
Week 1
Microsoft
PO number : Date Ordered: Address 1, 2 3 etc....
0011 01/01/13 Somewhere
0015 02/01/13 Anywhere
0213 05/01/13 Nowhere
I know this is much easier with a database, however my manager insists a database cannot be used, and it must be in a spreadsheet format !
I received one excel spreadsheet office 2010 every week from one on the vendor. it has over 55000 rows. I got columns N, O, P & Q. these columns has some value which I use for my pivot tables. However the values in this columns are stores as a text. I select the little small box (information box ) appears on the right side of the cell and right click on that where I get an option to change it to number. This is working well.
However I have over 55000 rows , it takes a lot of times to change all these cells in four columns ( almost over 220,000 cells). some times more than an hours.
I use excel 2010.
Using Excel 2007.
I setup a named formula: Spread1 =Sheet1!$E$2:$BE$2 where I entered % values to spread a an annualized figure over 53 weeks
On Sheet 2 I entered the amount to spread in $D$2 and in $E.. I entered my formula: =Spread1 * $D$2. Everything appeared great!
But then I deleted a few columns on sheet2 so first calculation is now in $G$2
Spread1: =Sheet1!$E$2:$BE$2
Calculations: Sheet2! $G2:$BG$2 (the values in I,J,k while summing to the total are in the incorrect weeks based on the spread on Sheet1, BF and BG have #VALUE! errors)
Seems odd, if I highlight "Spread1" in the formula bar and [F9] I see an array of 53 possible values and the 3 values I expect are in the place in the array (elements 5,6,7).
How I can defeat it with the possibility that a user may delete a column?
Surely it's user error, but it honestly seems like Excel is not calculating 2 numbers correctly. At work, we have office 2010 where it is not calc'ing correctly; at home I have 2007 and it's fine, but anyway, here's my situation:
Cell A1: 0.003529
Cell B1: 0.48
If i put in Cell C1: "=B1/A1" i get a value of 136.000000 But that's not correct, it should be 136.01587... But if in Cell C1 i put "=0.48/.003529" then i get 136.01587...which is correct.
Why does using cell references in my formula give me the incorrect value?
I am having trouble creating a hyperlink to a place in the current document for a file stored on a network drive. My workbook has a lot of sheets (50 or so), so I created a 'Navigation' sheet that contains hyperlinks to all the worksheets located therein, and pasted a link back to the Navigation sheet in cell A1 on every tab.
Setting up these Navigation sheets has never a problem until I started posting the file on a network drive. As soon as I did that, I noticed that the internal hyperlinks on all sheets in the workbook would stop working after I closed the file in Excel. Note that this network drive is not set as a drive letter on my computer, and is only accessible by entering the server ip address in Windows Explorer.
An example address the hyper is trying (and failing) to open is: "file:///\ IP ADDRESS sub-folders activefile.xlsm"
I've tried a few options, using the HYPERLINK formula, using the HYPERLINK feature in the Insert ribbon, etc, but all to no avail.
I am an excel user and know only some basic functions of excel. My query is how do i save data inputted in an excel sheet to another sheet in database form (tabulated). I will try to explain what my present input and outputs are and what I would like to achieve. I think this would make my requirement more understandable.
Present Input:
My Excel Sheet (has only one record per sheet). This after being filled up, is printed. This sheet gets overwritten when a new record is filled in as it replaces the previous data.
Present Output:
Hard Copy of the form, which is printed.
Required Input:
Excel Sheet (has only one record per sheet). This after being filled up, is printed. This sheet gets overwritten when a new record is filled in as it replaces the previous data.
Required Processing:
As the sheet is printed, it should save the data in tabulated form in the same file on a different sheet.
Required Output:
1. Hard copy of the form, which is printed.
2. Saving the data to another sheet in database format i.e. in tabulated form so that analysis or comparisons may be done.
In column H I have a list of numbers seperated by a space, the number of lines can change. In column L I have a list of numbers which can change either expand or retract.
I would like to check each cell in column H and if any numbers are not listed in column L then it/they should be shown in column G.
Example1 H2 shows 6 11, therefore cell G2 should show 11.
Example 2 H6 shows 5 6 9 11 therefore G6 should show 9 11
Sheet1 HIJKL1Container ID26 11 135 8 11 245 7 11 355 7 565 6 9 11 675 6 9 Excel tables to the web >> Excel Jeanie HTML 4
I wish to use a formula to grab data out of cells A1 & B1 and use that data to complete a hyperlink URL in cell A3. The base URL never changes but the last two variables do. For instance:
URL Format: http://www.test.com/XXXX&page=XXX
Example data:
Cell A1 = 1234 (always 4 digits)
Cell A2 = 567 (always 3 digits)
Desired Final Result, hyperlink: [URL] .........
I cannot seem to figure it out.... my formula i have gives errors:
=HYPERLINK("http://www.test.com/"(A1)"&page="(A2), "Linked")
This is Excel 2010
I've been playing about with this for ages, I have row 'U' which I manually link to local JPG files. Basically it has the word "HERE" and linked to a JPG that opens when you click it.
I'm really looking to just RIGHT CLICK a cell in column U, it opens a file browser, when I select the file it places HERE in the cell and links it to the file I've selected.
I am using Excel 2013 and would like to input data into my spreadsheet using a barcode scanner. I've created some barcodes using an online barcode generator (Code 128-B). My barcodes are text as opposed to numeric. I initially tested it out using Access and it scanned the information just fine. However, when I try to scan in a code into Excel nothing happens, no error, no data, no nothing. Is there something I need to set in the options of Excel to recognize the barcode scanner as the input device? Or is there some other reason why the scanner might not be pulling in the data?
View 3 Replies View RelatedI just have a basic data validation list. I used "List" and checked the right boxes and the file has been working before. Now the same file does not stop the user from typing in anything.
I re-did the same data validation in a new file and it works. Was there something in the file that prevents it from working?
I have Excel 2007. I saved in both xlsx and xlsm formats
In the spreadsheet shown below I would like the user to select a project via a dropdown list in cell B2 which is from the data set shown in row 7 downwards. Then based on the project they select, they need to be able to see all of the locations associated with that project and choose in B3. Finally they then need to be able to select a team which is associated with the project & location combination chosen in cells B2 and B3.
Excel 2007
ABC2
Project3Location4Team56ProjectLocationTeam7Project 1PerthTeam 18Project 1MelbourneTeam 29
Project 1SydneyTeam 110Project 1Brisbane Team 111Project 1Brisbane Team 312Project 1DarwinTeam 413
Project 1DarwinTeam 514Project 2PerthTeam 315Project 2PerthTeam 416Project 2MelbourneTeam 117
Project 2MelbourneTeam 218Project 2SydneyTeam 419Project 2Brisbane Team 6
I have a spreadsheet with data and I need to find text with spaces in one of the columns. How do I do that?
View 9 Replies View RelatedI have some code, in one part of it I am storing a number (between 1 and 8) as Integer. Is it possible to reference this variable to draw some rectangles on a sheet, sort of
"If MonitorDetail (this is the name of the variable) .value = 2 then
Here I place the code to draw the rectangle on the page. The macro currently stops at Monitordetail with a error telling me its a "Invalid Qualifier".
I setup this function to pull my staff's schedule an auto generate the forms i need however the lookup function works properly sometimes and improperly others i checked the syntax and it is correct how ever the wrong data is continually returned. I have attached the file if anyone can help. the VISUAL PLAN tab is the one contaning the formula and it pulls data from PLAN, CURENT SCHEDULE, AND CATAGORY SUMMARY however the only formulas hat i have a problem with are the ones referencing CURENT SCHEDULE.
View 9 Replies View RelatedI am trying to write a macro to transfer patient personal data onto a database.
On sheet 1 the user inputs the patient surname, forename, date of birth, date of test and the test results.
On sheet 2 is the database, where patient surname, forename and date of birth is stored in columns A, B and C respectively. The remainder of the columns on each row is available for scoring test results.
What I would like the macro to do is as follows:
•Read the patient surname as input on sheet 1
•Check on sheet 2 if that surname already exists
•If it does, check if the forename and date of birth also match to sheet 1 for that particular entry.
•If a match is found then copy the test results into a column on that particular row. E.g. could be copied into column D.
•If no match is found, then all the data is entered as a new row on the database (I’ve written the code for this bit but thought I should add it in for clarity)
This will then allow all test results for a particular patient to be read off the same row in the database.
I was using SUMIFS on a large amount of data, but wasn't getting back the exact sum that I needed.
It turns out that I had cells with values similar to the following
Code:
000123
0123
Basically, text fields with the same numerical values, except for the number of leading zeros. Due to the nature of the table, the number of leading zeros is important.When I did SUMIFS, it counted the sum of the two values together. For example with the following:
Item
Batch
Qty
ABC
123
1
It appears that even if a numeric value is stored as text, SUMIFS treats it like a number.
I have 2 different sets of data: temperature data recorded at meteo-stations defined by latitude / longitude, and major world cities also defined by latitude / longitude.
After the user chooses the station's coordinates, temperature data is pulled out from the first data set; this works! However, I would like to be able to provide information about the closest city to the chosen meteo-station from the second data set.
File attached with sample data sets.
I did a data table yesterday and it worked. I tried again today and the results are incorrect. They are coming out as a constant (the same result as the original formula). Has anyone had this happen before and figured out how to fix? My spreadsheet is fairly complex. Does the data table formula need to refer to the "base" cells? For example, if the formula refers to cell F15, but in F15 the formula is +C15, does my data table formula need to use C15?
View 3 Replies View RelatedI have a form that I want to input or embed into excel. Im trying to make a training aid and I m stuck on trying to input the form in Excel/ I have seen different forms in excel.
View 3 Replies View RelatedI am trying to auto fill one cell with one of several names using different values from another cell. Ex: when one of six numbers (100, 101, 102, 103, 104, 105) in input in A2, I want B2 to display a name (Joe, Tom, Steve, Fred, Tyler, Todd) that corresponds to that number. Cells in columns A and B will be formatted as text. Using Excel 2007.
View 9 Replies View RelatedTried to open the formulation area of the forum but get an error so posting here and when that is working I can move it.
I need to develop a formula that is used in our microbiology laboratory and the formula on paper is attached as I needed scientific symbols.
total plate count formula.JPG
I need to count how many times an input in given on a same cell, and doing so on the hole column. Like when I enter "x" on cell A1 and it adds 1 on B1. If the input on A1 changes then comes back to "x" it adds once again 1 to B1.
For one Row I'm using this code but I really can't reproduce it on the others.
[Code] .....
I'm using Office 2007.
Excel 2010. I have a macro, stored in a workbook.
The macro is: collecting data from another opened workbook,processing the data,saving the processed data in yet another workbook.
The workbooks' names it is operating on are hardcoded in the macro. I'm trying to make it more flexible, that is to allow user to specify both source and target workbooks along with the data columns.
The workflow follows:
User opens both source and target workbooks,User opens the workbook with the macro,User runs the macro (with a button for example),Macro prompts the user to select range in the source workbook,Macro "memorizes" the range and the source workbook's name from user's selection,Macro prompts the user to select range in the target workbook,Macro "memorizes" the range and the target workbook's name from user's selection,Macro processes the data.
I found out that this can be achieved with Application.InputBox("Select cell(s)", Type:=8) method. However there are some issues that I was not able to solve playing with the returned value's methods:
The .InputBox defaults to active workbook. I was able to select another opened workbook only with Ctrl+Tab combination, but it will not work with the end users - they are too poor with these tricks. Is there a better, more intuitive way to allow user selecting a workbook?I was not able to get the workbook's name from the .InputBox returned value property. I was trying with .application.caption and .application.activeworkbook.name - none of these worked. They returned name of the workbook with macro, not the one with selected range. How to get the workbook's name from given cells range?
