I am extracting amounts from one page to another with eg = 'Oct09'!P9,
if there nothing in that line it returns a $ -
If 'Oct09'P10, has say $10.00, then it returns $10.00.
How do I go about getting rid of the $ - and leaving it blank?
I would like to create a menu representing "tasks" where an amount of "time" can be designated per option for my employees to select and fill. I would like this information to then be tallied elsewhere so I can keep track of our efficiency.
So far, I've created a table where a "time" input is nested beside each "task" drop down menu, I would like these inputs to be added up underneath the same category on the budget tab.
For example: If I work Monday, Tuesday and Wednesday doing tiling.. I would like the budget to reflect those accumulated hours (throughout the week) nested under "tiling".
Here's the file, what I'm trying to accomplish [URL] ............
I am using the "CreateObject("InternetExplorer.Application")" method to extract data from a webpage. This webpage is a sub page that I cannot find the data for, even after filtering through all the ".document.all(x)" items? Any ideas on how to obtain this "sub page" data, since there is other data that I need which is on the "main" page? I don't want to have to call up this sub page separately since I'm already seeing it (physically able to view it on the main page but not able to extract it) from the main page?
View 9 Replies View RelatedWhen I view a sheet under Page Break Preview, it shows the Page numbers in the centre of the Page. While I am aware that it would not print the page number I was wondering if there is an option to remove/hide the page numbers.
View 3 Replies View RelatedI have a directory folder with an active workbook and another workbook id like to copy data from. The Following macro opens up a file in the same directory, copies some data and pastes it in the active workbook. However with this code I have to specify the filename, 'Data.xlsm' in the example code. I would like it to copy data from the only other workbook in the current directory WITHOUT having to specify the name in the code, so just opening it up no matter what filename it has.
In addition I would like to extract the filename from the workbook im copying data from and paste it into the activewoorkbook in sheet 1 Cell A1. I had a look at getopenfilename function but cant seem to make it work for my purpose.
I have some code that parses an html table. I want to put in error handling in case the format of the page changes or the internet page is unavailable. I am testing without an internet connection and the run time error is not being branched off to the error handler and is fatally ending the macro. This is true also if I raise an error manually.
Private Sub ParseInjuryPage()
On Error Goto ErrorHandl
Dim strPage As String
Dim webIE As SHDocVw.InternetExplorer
Dim myURL As String
Dim tableBeg As Long
Dim tableEnd As Long
Dim RowBeg As Long
Dim rowEnd As Long
Dim cellBeg As Long
Dim cellEnd As Long
Dim strBeg As Long
Dim strEnd As Long
Dim myCell As Range
Dim rowNum As Integer
With Sheets("INJ")
Set myCell = .Range("A2")
.Range("A:F").Value = vbNullString
rowNum = 2
Set webIE = New SHDocVw.InternetExplorer
myURL = "http://www.sportsline.com/nfl/injuries"
webIE.Navigate myURL
Do Until webIE.ReadyState = READYSTATE_COMPLETE
DoEvents
Loop
strPage = webIE.Document.body.innerhtml...................................
I suddenly can't adjust my page breaks in any Excel spreadsheet while in Page Break Preview. Is there some option to turn it on and off?
View 7 Replies View RelatedI have a sheet that changes the number of columns based on data from other sheets. This is the only sheet in the workbook that I will print out every day. I have it set right now to fit all columns on one page, however sometimes this will result in the bottom 1/3rd of the sheet not being used. If I use fit all rows on one page, then some of the columns will get cut off. Is there a way to scale in both directions? My guess is there is not, but I thought I would ask because it would work great for me if I could.
View 4 Replies View RelatedI have a report that I use and right now I have a command button that prints the report x2. What I would like to do is to have it print one in color and one in grayscale. I could do this I think with two different buttons, but I would like to utilize just one. I tried creating a macro but it did not pick up the fact that I changed the properties to black and white.
View 2 Replies View RelatedI am looking for a formula that will take data from one page and graph it on another page. The data I am trying to graph is arranged like so:
A B
1 q 2009.05.01
2 w 2009.05.02
3 w 2009.05.01
4 q 2009.05.01
I am looking for something that will look at column A and if the answer is "q" and then look at column B and if the answer is "2009.05.01" Excel will take that and count it in a specific cell on another page. In the case above, I want Excel to give me the answer of "2" in a designated cell.
Is it possible to Change Start Page Of Multi Page UserForm Based on 8 CommandButtons. on a WorkSheet, for example.
CommandButton1
UserForm1.MultiPage1.Value = 0
CommandButton2
UserForm1.MultiPage1.Value = 1
CommandButton3
UserForm1.MultiPage1.Value = 2
CommandButton4
UserForm1.MultiPage1.Value = 3
Could I use 1 multipage UserForm Instead of 8 UserFoms. Is it better to use 1 multipage UserForm with 8 pages or 8 UserForms. I dont know if this make sense or not?
I have a workbook that has 9 different tabs in it. Once all the information has been completed throughout the workbook, is there a code that could transfer specific data on each page to a different colum on that same page? I need to do this for 9 pages at one time. The information is listed differently on each page. I need to transfer data from the current data column to the previous data column once the entire workbook has been filed out.
View 2 Replies View Relatedhow to get rid of the page numbers that appear when using page break preview mode. The page numbers appear in large grey font in the middle of each page and sometimes makes it difficult to read cells. Can I use this view with all the same functionality without that one feature?
View 3 Replies View RelatedI need a macro that will print a hidden page that is linked to the page I have active.
It needs to be able to find the correct hidden page that corresponds to the active page because there are multilple pairs of the hidden/visible pages. The name of the hidden page is the same as the visible one with "printout" added to the end. When created, the sheet code names are sheetn and sheetm; n and m being consecutive numbers.
I M doing SUMIF correct on the below but it seems not to be adding up correctly. Can anyone point me in the direction of where I'm going wrong?
What I am trying to do is sum up the amounts in D if A equals C, as you can see in C their are numbers that appear twice so I don't know if this is a problem. I thought the sum =SUMIF(D:D,L:L,M:M) would sort this but the value I get for 59846(A) is 3665 which is on number 66336(C) so obviously doesn't match as this should be 1211.25........
This year we need to display 2 types of VAT in every invoice. So any listed product in the invoice could have either 5% VAT or 18% VAT depending on its category. At the end of the invoice i need to separately express (show) the total for 5% VAT and total for 18% VAT.
View 3 Replies View RelatedWorking with 2000 Excel is bringing some new chalanges. I see things are done different. I am trying now to keep my header to repeat from page to page.
For example I have a header that may say something like:
People, numbers, date, ect. And there are so many new rows they go on for pages long. How can I keep my header on the top of each page when it prints out on paper.
Assume that calculation is set to automatic and not manual, then is every formula on a page updated when any value on the page is changed, or only when the values in the cells pertaining to a particular formula change?
For instance, if cells a1-a10 have values and in a11 is a sum a1-a10 formula, and in b1-b10 are values and a sum b1-b10 formula in b11, if I change a value in range b1-b10 but do not change any values in a1-a10,then as well as the formula in b11 updating will the formula in a11 recalculate or does excel keep track of which cells have changed and thus is aware that the formula in a11 does not need to be recalculated?
I have a dataset that is as follows:
Col1 2 3 4 ...... 45
123 456 xxx xxx xxx
100 234 xxx xxx xxx
221 543 xxx xxx xxx
112 234
112 689
122 956
122 234
This goes on from left to right for 45 odd columns.
I need to copy each set i.e set 1 is 123 down to 122 to say cell a25 and then copy the next set i.e. 456 to 234 below the first set.
I need to somehow write a loop code that knows where to get the 2nd, then 3rd set, etc and copy it to the bottom of the preceeding set
I can use help creating the following forumla.
In column "A" I have Dates of Variances. In column "E" I have the variances for those dates.
On another sheet I need variances between certain dates. For example, get all variances between the dates 03/28/09 - 04/28/09.
How to write a macro that will sum daily figures into monthly figures? On the attached spreadsheet I would like to take the daily figures on the amounts on the VRU DAILY worksheet and sum them in the appropriate month on the VRU # sheet.
View 14 Replies View RelatedFor example: I have a spreadsheet with a column that has numbers such as 48600. When I go to format it into currency, etc. I select the decimal to move 2 places. Although it keeps giving me $48,600.00. I dont want that. I want it to put $486.00.
When I try to increase or decrease a decimal, it wont do anything until I have a decimal already in the number. So then I took 48600.00 and tried to increase/decrease the decimal, but it gives me either 48600.00000 or 48600. What I need is to make my number 48600 $486.00
currently have this: = SUMIF(D8:E8: F8:G8:H8,1,D29:E29:F29:G29:H29)
Range is multiple rows
need to get to here:
"multiple" Criteria needs to be able to multiply amount in sum range when greater than 1 and then summed in column I, across each row, then totaled at bottom of column I.
I realize sumif won't suffice.
I have some sales records that contain GBP payments, along with AUR, USD and EUR.
The GBP payments are not a problem, but a formula which could automatically convert AUR, USD and EUR into GBP payments would save a lot of time and reduce any chance of errors.
I have attached an example of the current layout.
Ultimately, I would like to put the sale in it's GBP amount where the boxes are blank & then a formula that detects either
A) If Original currency (the F column) is not GBP, then multiply the amount in the C column by whatever the exchange rate is in the G column.
B) If the exchange rate column G, has anything other than N/A, then multiply this by the amount in column C.
So, if the original currency in column F is in GBP, then I can enter data into the C column, and it will not change, however if the original currency in column F is not GBP, I would like the data in column C to change.
I am looking for a method to count the number of amounts in an addition.
For example, let's assume that A1 contains 354.92+29+84.7+45 That addition contains a total of four different amounts that have been added So, I am looking for a formula that will display the total number of amounts (4) in a different cell.
Another example:
A1 -> 75+9+237
A2 should show -> 3
I have a total $$ amount in one cell. Using this $$ amount I need to populate two other cells. One that is divisble by $10,000 and the remainder would populate the other cell. In the attached excel file I made notes to this effect.
If the $ amount in cell A3 is $83,000 I would need $80,000 to populate cell C3 and the remainder $3,000 to populate D3. If the $ amount in cell A3 was less than $10,000 such as $5,000 then only D3 would be populated.
Column A: List of document numbers
Column B: List of amounts
A B
111 $5.00
222 $10.00
111 $5.00
222 $15.00
I need to merge all equal document numbers so that it shows the full amount on that document number. It needs look like this in the end:
A B
111 $10.00
222 $25.00
I realize a pivot table will do this but the formatting of a pivot table will not work with what I am ultimately trying to do.
I need to add different amounts to a column of prices based on a column of zone numbers. I don't know if that makes sense but here is an example test.xlsx
View 3 Replies View Relatedi'm looking for a formula that will classify amounts according to the aging column automatically each time the month is changed.
my file is attached...
I am creating jet report, however am having to use Excel formula to create totals.
I am trying to sum the amounts in column I depending on the currency, although as it's a jet report it needs to update when the report is run.
My formula: =SUMPRODUCT(SUBTOTAL(9,OFFSET(I4,ROW(I4:I6)-ROW(I4),0)),(F4:F6=Options!B4)+0)