Field A which I need to convert into two cells.
So I would have two columns for which would give me the split of A
A) 6210220121120
What this needs to be is 62102 in one field
The other field it needs to read as a date (which is backwards) and this would be: 02/11/2012. As you can see that is how the field is.
Last four digits is the DD/MM backwards and the year is forwards.
I need a script that will turn a excel doc into a txt doc. Thats the easy part. The hard part (at least I think it is), is I need it to be in a certain format and I'll do my best to explain that fomat below.
View 9 Replies View Relatedhow to change the formula below which is referencing an internal worksheet, to a forumula that will reference an external worksheet that is located in an excel document on the G: Drive.
=SUMPRODUCT(--(1210laradj_v_2009!F2:F65536="HMO-ALT SELECT"),--(ISNUMBER(MATCH(1210laradj_v_2009!AO2:AO65536,{3,5,9,12,37,39,46,47,54,73,74,75,76,77,78},0))))
So what it's doing is totaling the counts in the 1210laradj worksheet that match columns HMO-ALT SELECT as well as the numbers 3,5,9 etc.
Well now these worksheets will be on a seperate excel spreadsheet located on a network drive, so I am trying to figure out how I can fix this.
I tried adding it in like so, but it fails.
=SUMPRODUCT(--('G:ROC-CLAIMSAudit ResultsProvider infomonthly statsReproc'd claims2011ProductionCounts[0111rpt2235_laradj_v.xls]0111laradj_v_2009'!F2:F65536="HMO-ALT SELECT"),--(ISNUMBER(MATCH(1210laradj_v_2009!AO2:AO65536,{3,5,9,12,37,39,46,47,54,73,74,75,76,77,78},0))))
I have a linked document, only 2 sheets. On the first sheet there is a column for + or - signs, I need a formula for the second sheet to show the + as a 6 and the - as a 5 instead of just the symbols carrying over.
View 3 Replies View Related******** type=text/javascript>*********>******** type=text/javascript src="http://pagead2.googlesyndication.com/pagead/show_ads.js">*********>
I tried locking an excel document and i ticked a box that said protect structure, and then entered my password and now I cannot open the document. It is a white square on my desktop now with no options to do anything, it cannot even be attached to an email or deleted. This was on excel 2008 on a macbook.
In the attached sheet i want one more column that will populate week no for each item .. using excel formula. like for item A if 1st date if 1/6 then value will be "W" and if it is 8/6 that is greater than w then the value is "W +1" and we can do it otherwise.. Formula to populate this.
sortbyDates.xlsx
Is there anyway to find missing numbers from column by excel formula?
Here is an example:
A
B
C
01 06 44
01 31 45
01 39 48
04 21 27
07 21 44
27 30 44
[Code] .....
Missing Numbers
32, 41, 49
02, 06, 11, 14, 17, 25,33, 34, 46, 47, 50, 51
04, 05, 13, 20, 29, 33, 40, 41, 49
I want to add these document properties I created to a cell ("A1" or any of the cells) without doing any VBA programming. Is this achievable in any case?
DocProperties.PNG
Some time ago, a friend of mine told me he didn't use any Pivot Tables at all, due to the imense space they require.
Instead, he made connections between Forms in Excel and the Databases using SQL.
Do you know of any Internet site where I can start to learn something about this?
I am processing a fair few Invoices, which are being sent to me via e-mail as excel documents, very often they contain mistakes, a decent amount of mistakes. Usually the prices are wrong.
I keep track of every single entry on the invoice on my own document - Tracker, which I consider to be the superior/more correct document to the Invoice presented to me by my contractor.
Both of the documents have a reference number, which is a specific docket number, and horizontally, in the invoice, there is going to be a price for this docket. In my document, there is going to be a separate column for the total price.
Is it possible (I guess with VBA) to check for mistakes in the Invoice, but use the Tracker as a reference for this check.
Tracker has
columns
A - Name
B - Department
C - Date
D - Docket No.
C - Total price for the docket (calculation of E to Z)
E to Z - all smaller entries
Invoice has
columns
A - Date
B - Docket No.
C to E price for that docket, but it is spread, because departments are separated out, so each VAT account can be charged accordingly. I guess it is possible to do a separate column for the price, if it is easier to do a script that way.
Basically, I need to check if in the Invoice document, the price (C to E) for Docket No. (B) is the same as the price (C) for the Docket No. (D) in the Tracker.
I would like the wrong entries to be highlighted on the Invoice Document, so I can see straight away, that this needs attention.
Not always the price is wrong, sometimes the Docket No. is spelled incorrectly (Dyslexic contractor), hence the highlighting.
I'm using excel 2003 and have a problem regarding some code.
Dit(a, b) = "=" & Hit(a, (d - 12 - e) + f) & "/" & (Pro & ".NrE.sol")
Where "Hit(a, (d - 12 - e) + f)" can be numbers with decimals.
When I run the code the result is nothing, unless the number is a number without decimals.
If I use just "Dit(a, b) = Hit(a, (d - 12 - e) + f)" it shows the right number.
I am using Excel 2003 and trying to create a formula that will combine comma delimited numbers from two or three cells in the same row and output them to another cell in the same row as comma delimited numbers, sorted with duplicates removed.
So the data is something like this ...
Cell A1 ... 1,5,8,19
Cell B1 ... 4,26,1,8,8501
Cell C1 ... 1,6,1301,12
I would like to consolidate these into cell D1, sorted and distinct ...
Cell D1 ... 1,4,5,6,8,12,19,26,1301,8501
I am working in Excel 2010 (Windows 8) and am having real problems trying to find a function/formula that will work with both numbers and text. I am trying to identify if the contents of Col A cells and Col C cells on each row are duplicated within a worksheet, and if they are, to add a 'J'.
(Column A is a list of numerical company codes and Column C is a list of adviser codes using the letter A-G. If a company ID code has more than one of the same adviser codes referenced to it, it is termed 'Joint', hence the 'J').
The formula I've used is: =IF(A2:C2, FREQUENCY>1, "J")
But it tells me 'numbers only'.
We have mapped a network drive to a SharePoint Directory, while we are able to copy a file (using Macros) to this location, this file does not appear in SharePoint to the other users, the reason being that it is not checked in. How to Check using Excel Macros.
[URL] .....
I wonder is there a way in excel to replace multiple words at once. To have like script where I had all words that need to be replaced and words replacing those. And just click the button and done? More specifically I'm translating some exports in xls and there are few words repeating over and over again in every document. So for example I need to replace word parfem for perfume like 500 times in one excel document atd. I think there has to be a easier way to replace those words at once.
View 5 Replies View RelatedWhen an Excel document crashes, I get the option to recover the document the next time I open Excel. However, if it is a read-only file, I don't get this option.
Is there any way around this? I usually work in read-only documents, saving my changes to new documents. If the read-only file I'm working in crashes.
How do I add a password to protect the PDF that I create from my excel 2010 document?
View 5 Replies View RelatedWhen I click the general Excel program icon that use to just open a blank excel workbook, it now always opens the same existing file (apples.xls) If I am clicking on a different existing file (pears.xls), excel opens pears.xls and apples.xls.
View 4 Replies View RelatedI have an Excel 2010 spreadsheet which I need to print to PDF. There is a simple "Save as" PDF option which always fails with just the message "Document not saved". I created a simple test sheet with just a few numbers on to check it isn't due to complexities with the particular workbook. This works on my home PC (Windows 7) but not on the Windows 7 Club PC which is where I need to run it to generate the output which eventually will go to the club's website.
It also fails the same way if I try to create an XPS file (although I don't want an XPS file).
This fails from the normal user interface "Save As". I also tried it from a VBA macro and run it: I get Run-time error '-2147467261 (80004003)' Document not saved
I've googled endlessly but all the references I tried failed to uncover a solution. I was a bit surprised that this doesn't seem particularly common and most of the reports seem to be a couple of years old or older (no workarounds were useful).
I have a few excel files in a common shared folder. I want to allow other users to access this folder to make changes in the worksheet but should never allow them to delete the file. How can i do this?
View 5 Replies View RelatedI'm trying to create Word documents (letters.doc) from Excel fields.
I already created bookmarks in Word and have the same Titles in A Row in Excel 2003 and I have tried the following threads:
Add bookmarks to Word from Excel Inserting data values from Excel into a Word doc using bookmarks
But I cannot manage it properly. I know that I need the code that goes on the lines of
Set WdApp = CreateObject("Word.Application")
rather than Set WdApp = New.Object
I cannot get Word to open a document! =(
It's basic letter creation, First_Name, 2_Name, Address_1, Address_2.. These have been bookmarked in Word and have exact same Titles in Columns A-D
I have this macro currently running on an original excel document
Private Sub Workbook_Open()Dim fname
fname = "C:ackupEconomics Tracker - " & Format(Now, "dd mmm yy hh mm AM/PM") & ".xlsm"
ThisWorkbook.SaveCopyAs Filename:=fname
Sheets("Menu").Activate
End Sub
Which creates a backup of the document each time the document is opened; I was wondering, is it possible to remove that particular macro from the backup as opposed to saving the backup as .xlsx? I don't want to be able to open a backup and the backup makes a backup
That particular macro is running on the main workbook.
I have an Excel spreadsheet that contains information for a Word document. It uses an elaborate macro to "substitute" unique codes in a starter document. At completion of the process, it goes through and delete all unused codes
The problem is that the codes are preceded by a "bullet" symbol.
How can I remove that orphaned bullet symbol. If the code is ". CtlCode10" (where the . is a bullet), then ALL need to be removed
We have mapped a network drive to a SharePoint Directory, while we are able to copy a file (using Macros) to this location, this file does not appear in SharePoint to the other users, the reason being that it is not checked in.
How to Check using Excel Macros.
For work I keep track of errors in files my co-workers make. So in one individual cell it may look like this :
OP15698
OP25687
or
OP45986
OP74563
I simply need to count how many of those file numbers there are in one cell.
Is this possible? I also use Excel 2007.
I'm using Excel 2010.
A formula I am using is outputting "0" instead of the expected value. Upon evaluating the formula I realized that this was because some of the values - years, in this case - had quotes around them while others did not. Please see the image below for the screenshot of the evaluation.
These values - the years - are being evaluated in the following formula.
Code:
=SUM(
IF(inventory!$F$3:$R$3=$A3,
IF(inventory!$B$4:$B$56=$G$1,
IF(inventory!$D$4:$D$56=$G$2,
inventory!$F$4:$R$56))))
This formula references the following sheet (an excerpt from that sheet). You can also see the formulas found in the cells causing the problem.
*ABC5212/20/20102010Adjustments53*20112011543/17/20112011Carwen Printers559/9/20112011Adjustments569/9/20112011Copeland Printing
Spreadsheet FormulasCellFormulaB52=IF(A52="",C52,YEAR(A52))B53=IF(A53="",C53,YEAR(A53))
B54=IF(A54="",C54,YEAR(A54))B55=IF(A55="",C55,YEAR(A55))B56=IF(A56="",C56,YEAR(A56))
I suspect that the problem is being caused by the output of the formula in these cells. If I simply type in "2011" instead of using the formula in B52:B56, then the first formula in the code section above does not have a problem.
How I can reconcile this?
I have a problem concerning sending an active word document via mail. I have a worksheet with various information in it, that I am exporting to a word document. I then want to send the word document via email. I want to do this without saving the changes in the word document. I am working with excel 2010. I have posted some of my code below.
VB:
Sub letter()
Dim myWord As New Word.Application
Dim adr As Variant
[Code]....
I get the following errormessage: "Compile error. Wrong number of arguments or invalid property assignment"
I am trying to open an .xls excel file within a non-email lotus notes database with vba code in excel.
View 3 Replies View RelatedIn my Excel 2010 workbook I wen through File > Info > Check for Issues > Inspect document. But as it's shown following, it doesn't show anything. Why?
Capture.jpg
I have designed an excel spread sheet for my staff to fill in when completing care plans - there are a lot of drop down boxes etc., on the form - when it comes to printing 3 computers on the office print it fine but the other 3 the margins seem to move and the sheets come out totally wrong - I have checked the setting and we are all using the same setting etc., but it is still happening - we are also all printing to the same printer.
View 1 Replies View Related