While using Excel 2003, I am aware of "freeze plane" and "split worksheet". The freeze plane comes in handy if I want to the first few rows to remain stationary and displayed while I can scroll down to the rows below. Similarly, the split worksheet comes in useful if I want to have the first few columns remain in place while I can maneuver to the right to see the other columns.
*** THE QUESTION IS: how can I get cells A1 to F20 to remain displayed and unmoved even if I choose to move the active cell around anywhere between G1 to K9999
(EXCEL 2003)I have 6 worksheets for 6 separate ad reps that will be populated with customer info. To make my life simple I just want column A from each of the 6 worksheets to show up on a master sheet in column A. But what also needs to happen is if we add and delete customers that also needs to happen on the master. I understand the "=" and then pointing to a cell in another sheet and hitting enter. I got that to work but I just have soooo many cells to work with that would just be way too time consuming.
I used to use Excel many many years ago for all my basic business databases and pivot table reports (so was at a basic competency level) and want to use it again but it has been quite a while and I can't remember how to do the following.....
I want to build a database of sales and purchase info in say 'sheet 1' - let's say 30 different headed columns and then many lines of different information under each column.
In 'sheet 2' I want to create an invoice template of my own design (I can do easily) for printing.
This template will need to extract say 10 bits of information from a particular row and then put them in a certain place on this sheet.
How can I type in one piece of information in a cell on sheet 2 (say the invoice or line number of the database) and that will then trigger all the other needed information from that particular line in sheet one onto this template?
I know it can be done but for life of me can not remember the formulas to use for these other cells in sheet 2 based on the info put in that one 'trigger' cell
Is there a way without using code to have the text in a text box (excel 2003), copied to another cell or another text box on a different worksheet?
I have information in a text box on 1 worksheet. I would like this information to automatically be copied to another worksheet. On the master sheet, if any of the information gets changed or updated, the copied information should get updated as well.
I am using Microsoft Excel 2003. I put color in my cells to show contrast with the information in my data list. I used blue and white for my cell colors. When I used sort to put my information in alphabetical order it did not only rearrange the information, it also rearranged the colored cells. My orginal pattern (before using sort) was blue, white, blue, white, blue, white. After I used sort, the pattern was changed to white, white, blue, white, blue, blue. Is there any way I can use sort to arrange my information only in alphabetical order, using sort, without rearranging the color of the cells?
Excel 2007 Conditional Formatting. I am trying to apply conditional formatting to a wide range of cells. I only want the rule to adjust the color and shading, under the FONT tab in the section below. Specifically, I do NOT want to adjust the number format (I have the user select the scaling and use VB to populate the number formatting). I accomplish this by removing the number formatting by going into the conditional formatting rule, clicking on format, and then on the first tab (Number) -- on the bottom right there is button to Clear.
This resolves the issue perfectly, and I can operate the report exactly as I would expect.
Unfortunately, when closing the report (after a save), and then re-opening, the conditional formatting number format is no longer cleared. Worse yet, because it no longer is cleared, the scaling VB doesn't work, unless I go back into conditional formatting rules, and go to the number tab, and click clear.
I have an Excel 2003 file that contains hyperlinks to OneNote notebooks on a Sharepoint site. An Excel macro looks for these links and determines the full hyperlink address which is then assigned to a variable. An Outlook message is generated which includes the hyperlinks.
The hyperlinks work in the Excel file. I can also copy them from OneNote and manually paste them into an Outlook message and they work. However, when I obtain their full address and transfer that to the email through code, the links do not work. The hyperlink address from OneNote starts with "onenote:http" which is not recognized as a link.
If I can do this manually, there must be a way to do this with vba. Are there characters I need to include in the OneNote hyperlink address to make this work? Is there another way to transfer the working link from Excel to Outlook?
Any way to pull in information from a particular website directly to excel? For example if I have the URL or the specific web page can I then locate specific pieces of information that are contained in specific areas of the webpage and pull it into excel?
I have Excel 2008 for Mac (vers 12.0). On a basic spreadsheet, the first column is for date which I have formatted so I can type in 15/8/9 and it appears as 15-Aug-09.
However, each time I open Excel after having Quit it, I have to reformat the cells in this column again as they revert to converting my date input with something like 39,123.
My other columns are formatted to currency and are always okay.
1. I have a userform I created that has a text box that pulls it's data from one of the spreadsheets tab. The userform also has 5 buttons I shall call Tab1, Tab2, Tab3, Done, Cancel. 2. The first thing a user will do is make a selection from the text box. 3. The User will then select one of the "Tab" buttons.
Below is my code. What happens is that no matter what button I click (Tab1, Tab2, Tab3) it ends up on Tab3 because it is the last transfersheet line of the done_click() sub routine. How do I get it to stay on the Tab I select?
+++++ Private Sub cancel_Click() Unload UserForm End Sub +++++
Private Sub done_Click() Dim transferworksheet As Worksheet Set transferworksheet = Worksheets("Tab1") transferworksheet.Cells(5, 7).Value = Me.ListBox1.Value
I have a spreadsheet that tracks employee stats. Most of these stats are amounts of TIME. These times are being pasted from another source and the problem I'm having is that as soon as excel sees "1:02:03" it puts it in TIME format. The problem I have with that is later on I need it in text format because a lot of the other times are in text already. (the program the info is coming from will use ":59:59" and not have a 0, so I have compensated for that later) So how can I paste 1:01:01 and not have it saved as TIME? I cant CONVERT it to text, I need it to be text from the start.
Was wondering if it is possible to get below information from Active Directory into an Excel Sheet though VBA. All these details are within an OU in AD: UsernameDescriptionUser Logon NameFirst Name + Last NameTelephone Number I have been searching around and i think I need to connect using ADO, but not sure how to get these objects from AD.
There are many software solutions for clinical information systems, but they all cost a lot. It occurred to me that a bespoke system could be build using excel.
I was curious if Excel was able to pull information off websites and automatically populate a table such as items from Safeway and a price then another column for Walmart with same item and price? I am betting not on its own but I figured this was the best place to ask since I am trying to find an easier way to find savings at the store.
Is there any way to create a specific template that will extract information from PDF file and make it to an Excel File ? The Template would know each PDF and will know which information to extract to excel ?
Are there any good website for a beginner on how to set up and store information in excel as a database? I have mutliple sheets that I use everyweek for payroll and I would like to store the previous weeks data in a seperate sheet so I dont have to save each weeks sheets.
In Excel 2003 I have Manually Grouped a number of rows. (Data>Group and Outline>Group).
If I use the buttons with the numbers 1, 2, 3 (indicating an outlining level) to collapse or expand the outline I receive the Error Message: "Cannot shift objects off sheet."
If I use the collapse button (-) or expand (+) button the data collapses and expands as I would expect.
I am aware of the normal issues surrounding objects and outlining and have ensured that I have changed the properties of all my of the objects in the sheet to "Move and size with cells". There are no comments in the grouped rows.
The following formula was, several weeks ago, very graciously offered to me from one of Excel Forum's contributors.
=SUMPRODUCT(--(MOD(ROW(E8:E6782),2)=0),E8:E6782)
My request was to find a formula that would add each 6th row starting in row e8 (e8+e14+e20+e26+e32 etc. through e6782) in column "e" when the column was 6782 rows deep from top to bottom. (i am not trying to add every number in column e, just each 6th row, starting at e8 and going through row e6782).
I entered the formula into my spread sheet and, voila, I had a sum that I assumed was accurate for my spread sheet of ticket sales. I began to question the functionality of the formula when I altered the E8:E6782 parameters (which represented the gross ticket sales) to E4:E6778, in an effort to sum up the E4 values e4,e10,e16, e22,e28,etc. . . (which represents the net values after commissions were deducted). The difference in the two sums (e8 values Versus the e4 values) was incorrect and did not represent the appropriate commissions (which should have been 15%).
I have a Log In/Log Out report that I need pulling data from.
Ultimately what I want to do is get the amount of time my employees were logged out.
I am using Excel 2003. Attached is an example of the data I'm working with.
Sheet 1 contains the data, and Sheet 2 will be the summary.
So, looking at Bob's times: If column A contains Bob (A2 in this case), and the same row in column G (G2) contains 79, subtract the contents of the same row in column F (F2) from the next row down in column D (D3) (which would be D3 - F2). That result should populate on Sheet 2 next to Bob's name in h:mm:ss format.
If there is nothing in the next row down in column D, then do nothing.
I would like to leave the data as is, if possible. There are extension numbers next to the names in the data. If possible to ignore those when rolling up to the summary that would be useful. Also, on the summary sheet, if the employee is not on the data sheet (if they had the day off) they can have 0 or NA or something to indicate there is no data for them.
In Excel 2003 the Status Bar is missing. I know to go to Tools, Options, View to view and hide the Status Bar however as i cannot see Tools (as Status Bar missing) is there another way to get the Status Bar back?
I have used MS Office Excel 2003 for many years without difficulty in XP. Suddenly I find I can't open many of my old documents. I get a message,"Microsoft Office (InfoPath2003) cannot open (the following file, etc.) A second message says, "The form contains XML that is not parsed: An invalid character was found in the content. Line 0, Position 0" Then I get the message "MS Office is not currently set as default." I complete the instructions that follow and it is now corrected but I continue to get the same error messages.
I am running Excel 2003.I have a spreadsheet with two worksheets, each worksheet has a Refresh button that runs a Data Extract Macro.The Data Extract Macro imports Data from another spreadsheet.I want to have the spreadsheet automatically run my Data Extract Macros for both worksheets at a specific time each week. (Scheduled task) Regardless of whether or not the excel file is open.
