I know that I can import data from access into excel. What I am looking for is a way to have the data linked so that any time that a change is made in access, that change will be reflected on my Excel worksheet.
View 1 RepliesI am creating a Payroll Database. It is nearly complete except for the federal and state withholding tax data. I want my db to look up the data which I have saved in an excel workbook. I need the lookup to be for marital status and # of exemptions. There are a total of two variables for each table. 1. How much the gross pay is (ex. between $175-185) and # of exemptions (ex. 3 exemptions).
View 2 Replies View RelatedIs it possible to export information from excel into access without actually opening the access tables?
I ask this because we have a program which uses a very old version of Access (97 I think) the program is being used more frequently by the company but is in dire need of cleanup of the databases along with quite a bit of updating of information. The program has no import option and whenever I open the access databases directly and make changes the program can no longer recognise the database.
I think that if I can update the information without opening the database and thus changing it to a more recent version I will solve my problem.
So once again: Can excel export tables directly into Access?
I am trying to pull info from Access into Excel using Data, Import External Data, New Database Query. I follow the menu and when I get to the end for the info to be pulled to Excel I get an error.
Microsoft Query:
Too Few Parameters. Expected 1.
I have set up a spreadsheet that requires info to be inputted into cells. I have created drop down lists in the cells to only allow certain information to be inputted.
Allow user to use a drop down list and pick the appropriate entry, then have another drop down list in the adjacent cell that only contains items that correspond to what the first drop down entry was (i.e. company name in first drop down box, adjacent cell drop down list only contains parts made for that company). Then I would like the next column to contain the pricing of the item that was chosen in the previous cell's drop down box. Ideally, this cell would be populated automatically depending on the part chosen for the company. I am trying to create a log of parts shipped and total amount shipped.
I have an access 2000 database with many tables of data in it.
The tables have 12 fields containing customer information as follows:
1-vendor#, 2-bar code #, 3-store#, 4-department#, 5-style#, 6-item colour, 7-size, 8-active in basics stock, 9-sales, 10-stock on hand, 11-orders placed, 12-week end date
the pivot tables in excel 2000 track this data week by week, which are linked to tables in the access database.
Everything was working fine until I tried to perform a security wizard on my access database so users could not make any changes to the tables in access>>>>>>This corrupted my database some how!
As a possible solution I re-created an entirely new access database, thinking that I could continue to create pivot tables and everything would move smoothly as prior to the corruption, but ive been having problems.
PROBLEM:
After linking the data from access to excel to create my pivot tables the data from the table that is retrieved from access table is incorrect.
it come up with repetative figures eg....15 15 15 or 18 18 18 when the
Ive tried sources on the site and web vbut nothing helps tried manuals etc-
contacted my It dept at work but they have no clue on this issue.
Hope there is a guru expert out there some where... who can help!
please see below:
CORRECT DATA
store(All)
date13/10/2007
Sum of Stk on hand size
stylecolourLMSXLGrand
Total
HEDI 99 ASSORTED74486
JEDIASSORTED74553741207
KEDIASST1371557175438
M1018NAVY223
WHITE221
PEDIASSORTED79644947239
U1008BLACK1371278054398
GREY HEATHER107577861303
WHITE1071076864346
Ive also tried to compact repair the access database... didnt work- tried refreshing the pivot table didnt work....im fairly certain that the data in the access table is correct.....im beginning to think there is a problem with the pivot table although they used to work when i did them before so not quite sure why this is happening and how I can resolve it?
I have a list of countries in excel and a list of partner countries, the countries all have individual country codes.
Example:
country partner country exporter country code importer country code year value
Australia..........Belgium........................AUS.........................BEL..1999..1000
Canada............Belgium........................CAN.........................BEL..1999..2000
This for a long list of countries, I have filled in the country codes for the first year manually but the list goes on to 2008 and is a lot of work to do manually, (thousands of cells). is there a way to complete the list of exporter and importer codes linked to the country and partner country column and then complete the whole list?
I have an excel that gets updated monthly for reporting. The goal is to get the graphs to update automatically by linking them from excel into power point. This is relatively easy but what when I go to update the report for the next month the data for the old power point changes as well. Is there a way to prevent this?
View 1 Replies View RelatedI have two issues with an excel graph to automate it.
My first problem is that I have to manually select the source data when the data changes inside my spreadsheet. I have 35 slots that represent a 5 week period. Because the first day of the month does not always start on the first day of the week, I have to allow the extra 5 slots in my template. I have the cells set to show blank when there is no data entered in certain cells. Therefore, only days that the machine is up gets put into the 35 cell table. When I do this, the blank cells show up as 0 on the graph and it skews the line. I need some solution that will allow me to tell the graph to ignore zeros.
The second issue is the title in my graph. I have a set title, but need it to pull "Month Year" and "c81" from the worksheet it is on. I cannot seem to get it to populate automatically in the label. I would like it to look like the following:
614 Machine Uptime Percentage
%Month Year%
MTD PCT (%c81%)
where the %name% is the automatically populated item. I don't necessarily have a problem with hiding a cell and linking the label to that cell inside the graph, but I need it to change those two items automatically.
Im certain that a resolution to both of these items exist and I am certain it is an easy thing that I will be kicking myself in the rear over once I find out the resolution, but I need to get this done and im tired of wasting time. I hope this makes sense. If you need me to post an excel file as an example, I will be more than happy to.
How do i do the automatic linking on the excel sheet like the one done on the following example
Book1.xlsx
I am making a spreadsheet which would have the record of the audit that i am doing on my patients. Along with other details i want to give link of the routine followups of their disease picture in their specific spreadsheet cell. I have searched the internet about it where i have come to know about hyperlinking. However, my concern about hyperlinking is that i want the spreadsheet with pictures to be portable to other PCs which wouldn't be possible with hyperlinking. Also including the pictures in the spreadsheet would be very troublesome as the size would enormously increase once the data continues to grow.I want to include photos in column L, R, V, Z & AD.
View 3 Replies View RelatedI have a table in sheet1 in excel containing 10 rows. I want to copy the first column of this table into sheet2 in the same workbook but what I want is that in case I inserted a row or more in Sheet1, I wanted to be copied to sheet2 automatically. How can I do this?
View 1 Replies View RelatedI would like to fill in a a form on page /sheet one and have the same info on every sheet that follows is it possible?
View 3 Replies View RelatedI have attached a spreadsheet and I am trying to capture the info in lines 2,7,12,17 and return the info into column d,e,f,g
The info in these columns at present has been manually entered but I am sure it could be automated.
OOL Roster Final 18-31Aug14.xlsx
I have a name list on sheet2, column A that will have names added to and removed from. Everytime this list is modified it sorts in ascending order and creates a new sheet named after the name added. These items are added to an ActiveX combo box on sheet1. I need to know how to link the item in the combo box to the corresponding sheet(ex: item: "Jim", sheet name: "Jim").
View 2 Replies View RelatedI am using an excel spread sheet as a larger display on a huge projected screen with numbers at a large charity event. It is a reverse raffle, so as your name is called you are out of the raffle. I would like to link the cell to a specific powerpoint slide which has the name and town of the specific ticket buyer. When double clicking on the cell listing their number the ticket buyers name and town would appear as a powerpoint a window in the middlle of the excel screen for all to see and then disappear after a few seconds or disappear when the next cell is double clicked. putting excel data into powerpoint slldes but not the other way around!
View 7 Replies View RelatedI want to link a table to a form. Only, I noticed that when setting the input range in the form I'm unable to use OFFSET OR a table name. How can I link the form to a list which is going to expand?
Attached is an example. Two sheets. One for the list. One for the form. The list will keep getting added to.
Unrelated-but perhaps useful - Is it possible to link one cell to a list in another sheet so a user can type in a word and it will offer autocomplete options in excel 2007 ?
linklistoform.xlsx
I need a front worksheet with either buttons or tick boxes that will list different options for a machine
Once a tick or push button is activated a hidden block of text related to that specific tick box needs to be selected and placed onto a final print out sheet (allocation)
when futher boxes have been ticked I would like all the information blocks to build up on the final print out sheet.
There are many examples and aspects to compare these 2 products but I just want to point one little difference which is quite crucial and interesting.
Generally if you use small amount of data - 1 Worksheet / 5000 rows / 20 columns you can use Excel without bothering about the execution time, queries and work fast and convenient with it.
The point on Excel is that in 1 Column/Row you can differently Format the data(cells). For example - format as Number or Hour the cells in Column B depending on the data in other columns. That saves you from making 2 Columns - one for Numbers and another one for Hours. This helps you to save 1 of the columns when the data structure in other column is the same.
In Access (and generally all SQL DBs) this is not possible.
I have a number of separate worksheets in one spreadsheet all based on the same list (eg customers); the customer's name is the first column and hence the 'key' in each worksheet; when I insert or delete a line in the main worksheet the formulae in the first (customer name) column are amended in the other worksheets BUT I ideally need more than that; when I insert a new customer in the first (main worksheet) I need a new line with that customer name inserting into the corresponding place in the other worksheets; and when I move a line (eg delete a customer and move them to the bottom of the main worksheet list) I need the corresponding lines in the other worksheets moving as well. I guess what I really need is a drill-down function; a main customer list and sub-lists all linking back to the main lists like you would get in database tables.
View 5 Replies View RelatedI have two excel sheets, one has partial information (displayName, title, company, streetAddress, city, state, postalCode, Pager, homephone, fax) the other has the missing information that I need on the first sheet.
Sheet 1 (DB1, has partial info) and Sheet 2 (DB2, has the missing info). I need to somehow link these two, and what they both have in common is name. DB1 has "displayName" which is just First Name Last Name (e.g. John Smith). DB2 has First Name and Last Name, but i'll concatenate that to a new column named "displayName" ( which I assume will be needed to link? ).
The missing information in DB1 is title / streetAddress / city / postal code. DB2 has "Location" (which is a company code, and I want to replace the code with the address, city, postal code) and "Group" (which is title).
To make it easier, I could just do a find and replace on DB2 (e.g. A01-DFW-HWY67) and do it that way right? Or is there any easier way to do that?
Other than that though, how would I link DB1 and DB2, using displayName and fill out DB1 with the information from DB2?
I have an excel sheet (version 2010) that has a few sections that you can add in names (via drop down box). I also have a spot where i want these names to populate - on a second sheet.
Ex
Truck #1:
1. _____
2. _____
3. _____
4. _____
5.______
Truck #2:
1. _____
2. _____
3. _____
4. _____
5.______
Truck #3
1. _____
2. _____
3. _____
4. _____
5.______
Truck #4
1. _____
2. _____
3. _____
4. _____
5.______
6.______
7.______
8.______
My problem is when I refer to the first worksheet from the second worksheet it shows blanks where there are no names. For example i want the second sheet [ LOC ] to show the names that appear under Truck #1, #2, #3, #4 - but truck #1 may not always have names under it?
Sample Excel file attached - Help.xlsx
I am trying to link data from a pivot table we have in excel to a separate excel sheet.
The pivot table contains an up to date list of our inventory, however, we want to take that data and put it in a cleaner looking excel that we can e-mail to customers and publish on our website. Going individually, through every product takes too much time to have an up to date stock every day. Its even difficult to manage if we want to update just once a week.
When I link the cells of the pivot table to the new excel sheet they are constantly changing values as people are constantly editing the filters of the pivot table to look up various things.
I want the contents of the text box (which will be input by the user) to update a cell in another worksheet. I have found numerous examples of how to display the contents of a cell in the text box but I want to know how to display the text box contents in a cell.
View 9 Replies View Relatedis there a way to combine the info from several columns into 1 list?
All of my columns have information in rows 1-20, but I want it all to automatically combine into a list in column B on a new sheet.
This is Excel 2007
I have a 2003 Workbook that is around 7 mb. It used to be half of that but it jumped up in size which makes it harder to email, etc.
On the front page it would go out past the (known) data and I was able to reset it to the correct place but on the 2nd sheet it would go to IV 65536 without fail. I used VBA as well as physically deleting andor clearing rows and columns but to no avail.
I decided to start eliminating portions of the workbook to see if I could find where the size was coming from.
1. I deleted Regular formatting
2. I deleted Conditional formatting
3. Made everything the same font name and size, etc
4. I deleted all VBA Code.
5. Last but not least I deleted/cleared all data from all the sheets
Between each change I would save and close the workbook and check the size in Explorer.
Each deletion brought down the size, however after finishing step 5, I had a blank workbook with 3 sheets and it was still over 1.4 meg!
The next step was to remove the 2nd sheet. I did this and it went from 1.4 meg to 25 kb.
At this point I have been coping all the info into notepad and the from notepad into a new workbook.
I have an assignment for work where I am required to create a button within an excel sheet that will submit any text in a cell which in this case would be "A9" to a database to a certain field. I must create this using visual basic.
View 1 Replies View RelatedI have a spreadsheet with five cells (L3 - L7) that have dates entered into them in the format of 9/9/2013, 9/10/2013, 10/20/2013, 10/21/2013, 10/22/2013. In another cell, cell n2, I have the formula:
=IF(L2=0,"",IF(L2=1,L3,IF(L2=2,CONCATENATE(L3,L4),IF(L2=3,CONCATENATE(L3,L4,L5),IF(L2=4,CONCATENATE(L3,L4,L5,L6),IF(L2=5,CONCATENAT E(L3,L4,L5,L6,L7)))))))
In cell n2 I get: 41526415274156741568481569 This is the excel number for the above dates, but I need it to look like:
Sep. 9 - 10, 2013, Oct. 20 - 22, 2013
I also need it to be able to isolate single dates for example if the date in cell l5 was 10/5/2013 instead of 10/20/2013 then I would want n2 to look like Sep. 9 - 10, 2013, Oct. 5, 2013, Oct. 21 - 22, 2013. but my formula currently only changes that number in the string from 41567 to 41552.
The reason I need this is because this information is part of a identifier and also maybe used in reports, the people reading the reports won't know this long number is actually dates and won't be able to read them if they did.
Also the cells L3 - L7 data depends on cell l2, 1 = one date entered into cell l3, 2 = two dates, one in cell l3, the next in cell l4, and so on for up to five total days that may or may not be consecutive. Also, it seems to work as is, but only for one day.
I have found a code to pull outlook calendar information from outlook to excel. I am trying to determine how to pull from Shared Calendars. This code looks as if they calendar has a number (9), but I can't figure out what the other calendar's numbers would be. How to pull from Shared Calendars and how to pull the month for which the calendar is on. My shared calendars are under Calendars>Shared Calendars in outlook. So for example, I would need to pull John Doe's calendar information for January if the calendar was on January. My code is listed below.
Sub ListAppointments()
Dim olApp As Object
Dim olNS As Object
Dim olFolder As Object
Dim olApt As Object
Dim NextRow As Long
Set olApp = CreateObject("Outlook.Application")
[Code] ......
If I have a set of number in a column. a1-12345 a7-12356 and a20-15487. how can I get them to show like this in columns Z.
12345
12356
15487
I am use 2007 and my data go down to cell 40000 in column A:A