ExcelBanter http://www.excelbanter.com A Excel forum covering all aspects of Microsoft Excel acting as a web gateway with the finest Micorsoft Excel community newsgroups en Sat, 25 May 2013 00:57:34 GMT vBulletin 60 http://www.excelbanter.com/images/002/misc/rss.jpg ExcelBanter http://www.excelbanter.com need help writing formulas. http://www.excelbanter.com/showthread.php?t=448811&goto=newpost Fri, 24 May 2013 04:33:35 GMT Hello, I would really appreciate some help on writing a formula for the attached spreadsheet: I would like for the rows between H to S to be populated by... Hello,

I would really appreciate some help on writing a formula for the attached spreadsheet:

I would like for the rows between H to S to be populated by looking up the table on sheet 2. I have populated 2 of the rows as examples.

Where the ramp % numbers need to start depends on the effective date and also whether the person is a new hire or transfer.

Not sure whether to use vlookup or if statements, help!

Not sure if I am attaching this appropriately as it looks weird on my mac.
lookup formula question.zip
]]> Excel Discussion (Misc queries) sfnbgirl http://www.excelbanter.com/showthread.php?t=448811 Storing values http://www.excelbanter.com/showthread.php?t=448810&goto=newpost Fri, 24 May 2013 02:30:31 GMT How can I store calculations using a currency exchange rate (stored in A1 for example) but not have the result change when the exchange rate is update? I do... How can I store calculations using a currency exchange rate (stored in A1 for example) but not have the result change when the exchange rate is update?
I do not want to store each exchange rate variation in a new location every time it changes.
]]> Excel Programming Alex Skinner http://www.excelbanter.com/showthread.php?t=448810 <![CDATA[Identifying Unique ID's in a Single Column]]> http://www.excelbanter.com/showthread.php?t=448809&goto=newpost Thu, 23 May 2013 15:19:50 GMT Hello -
I have thousands of ID's in a single column and need to find all ID's that have a (-#) the # are varied; 1, 2 3 etc... at the end. Sorting does not work, the -# are at the end of the ID.
Thank You!! ]]> New Users to Excel MichaelPK http://www.excelbanter.com/showthread.php?t=448809 Speeding up Excel simulations http://www.excelbanter.com/showthread.php?t=448808&goto=newpost Thu, 23 May 2013 13:12:18 GMT I have a VB based Excel simulation that I want to speed up. I have read that VB runs single thread. Is there any good data on picking out a computer that... I have a VB based Excel simulation that I want to speed up.

I have read that VB runs single thread.

Is there any good data on picking out a computer that will run VB
fast?

Any other pointers.

I plan to work on improving the algorithm, but I am also trying to
figure out how to pick the best hardware and take advantage of it.
]]> Excel Discussion (Misc queries) http://www.excelbanter.com/showthread.php?t=448808 <![CDATA[TEXT "ddd" giving #N/A on some machines]]> http://www.excelbanter.com/showthread.php?t=448807&goto=newpost Thu, 23 May 2013 09:48:48 GMT Hi,

My first post here, hope someone can help.

I have created an Excel 2010 spreadsheet that is to be used in various European countries (I'm in the UK). One of the pilot testers in Austria (but otherwise using a UK laptop with UK settings) is experiencing the following issue whereas all other testers under similar conditions report no fault. I can't explain it...

I have a calculated date based on an input year: A1=DATE(Current_Year,1,1)
Adjacent to this I have B1=TEXT(A1,"ddd") in order to display Mon or Tue or Wed etc

This works fine in the version I've sent and recalculates fine. However, change the Current_Year and the result of the TEXT function becomes ddd (and not Mon etc)!

Can anyone explain why, and better still, how to fix this!?

Thanks,

Adrian ]]> Excel Discussion (Misc queries) _Adrian http://www.excelbanter.com/showthread.php?t=448807 Now You Can Use Visual Studio 2012 To Work with Excel in .net and reporting services http://www.excelbanter.com/showthread.php?t=448806&goto=newpost Thu, 23 May 2013 07:37:11 GMT Many of our users use Visual Studio to speed up development and help organize their code. It’s a flexible development platform, and version 2012 adds tools... Many of our users use Visual Studio to speed up development and help organize their code. It’s a flexible development platform, and version 2012 adds tools that improves agile development and helps testing in Windows 8. All Aspose .NET and SQL Server Reporting Services products can be used with Visual Studio 2012.


• Developers can now use Visual Studio 2012 to work with Microsoft Word and other word processing formats from inside their own applications for creating, manipulating and converting documents independently of Microsoft Office Automation using Aspose.Words for .NET.

• They can also work with Microsoft Excel and other spreadsheet file formats in their own applications and can create, manipulate and convert documents without needing Microsoft Office Automation using Aspose.Cells for .NET. Aspose.Cells for .NET lets you automate reports and work with external data sources, create and export charts and work with formula.

• Developer can also work with Adobe Acrobat PDF files without relying on Adobe Automation. Aspose.Pdf for .NET lets you create and modify PDF files: concatenate or split files, add digital signatures, save pages to image format and more.

• Developers can now use Visual Studio 2012 to work with Microsoft PowerPoint and other presentation formats using Aspose.Slides.

• Developers can manipulate Microsoft Visio files using Aspose.Tasks.

• Developers can generate and recognize a large number of 1D and 2D barcodes using Aspose.BarCode.

• Developers can Work with Microsoft Outlook, from emails to contacts and appointments using Aspose.Email.

• Developers can Draw, manipulate and convert images using Aspose.Imaging.

• Recognize characters from entire images or specific areas using Aspose.OCR and many more advance features in their own applications. ]]> Excel Programming aspose http://www.excelbanter.com/showthread.php?t=448806 Help http://www.excelbanter.com/showthread.php?t=448805&goto=newpost Thu, 23 May 2013 06:58:43 GMT Hi.... I am new in excel and want help ...
please check the attached file....
i wand to checke C4 - AX4 if they contain "D", ... if it is true then return Header title and in this case C2 - AX2
any help ? thx
Attached Images
 
]]> New Users to Excel ahmed1972 http://www.excelbanter.com/showthread.php?t=448805 Loop to skip some sheets in workbook http://www.excelbanter.com/showthread.php?t=448804&goto=newpost Thu, 23 May 2013 06:24:34 GMT
What do I put where it says 'DO NOTHING so the loop will skip ws. X, Y & Z?

Thanks,
Howard

Option Explicit

Sub LoopThroughSheets()
Dim ws As Worksheet
If ws.Name = "X" Or ws.Name = "Y" Or ws.Name = "Z" Then
'DO NOTHING
Else
'Do some code stuff here like:
ws.Range("B1") = ws.Name
End If
End Sub
]]> Excel Programming Howard http://www.excelbanter.com/showthread.php?t=448804 Vertical scrollbar screwed up http://www.excelbanter.com/showthread.php?t=448803&goto=newpost Thu, 23 May 2013 06:18:13 GMT Normally when I drag the vertical scrollbar down to the bottom position it goes to about line number 150 or so, depending on how much data I have in that... Normally when I drag the vertical scrollbar down to the bottom position
it goes to about line number 150 or so, depending on how much data I
have in that worksheet. But on one worksheet it suddenly and
mysteriously changed to scrolling down to line number 1048576. I don't
know what I did to make it do that. How can I get it back to normal? I
don't like it the way it is because the scrollbar handle is very small
and in normal use stays at the top of the range, making it harder to use
it to scroll up or down within the range of lines where I have data.


]]> Excel Programming http://www.excelbanter.com/showthread.php?t=448803 Labeling on independently stacked line chart http://www.excelbanter.com/showthread.php?t=448802&goto=newpost Wed, 22 May 2013 23:46:20 GMT Hi, I need to display percentage results from 8 stores in one chart, with each store having a line (trended over time). They are positioned one above... Hi,

I need to display percentage results from 8 stores in one chart, with each store having a line (trended over time). They are positioned one above another, ultimately, in PowerPoint. The lines are not cumulative - they are independent.

From another site, I figured out how to do this by giving the first store ratings from 0 to 100%, the second store 100 to 200%, etc. This gets the position I want for the charts. I do not need to display any values on the y-axis.

However, I DO need to display the data labels. I'm looking for an automated way to change 795%, for example, to 95%. I can do this manually in the data label but once it's changed manually, any underlying change to the data won't update.

Any recommendations?

Thank you in advance! ]]> Charts and Charting in Excel Deb P http://www.excelbanter.com/showthread.php?t=448802 if and replace function? http://www.excelbanter.com/showthread.php?t=448801&goto=newpost Wed, 22 May 2013 17:57:40 GMT Hello I need help I have names on my left and depending on the name I need to remove a space Original data Answer John, Repp John,Repp Jay,... Hello
I need help
I have names on my left and depending on the name I need to remove a space

Original data Answer
John, Repp John,Repp
Jay, Thorp Jay, Thorp
Jana, Washington Jana,Washington

So just depending on the name some need the space after the "," trim and some dont I have a total of about 40 names only about 6 dont need the space after the coma.
I was thinking of Replace and If But I cant seem to put it together.
]]> Excel Worksheet Functions jc9972003@yahoo.com http://www.excelbanter.com/showthread.php?t=448801 VBA Macro to customize Right Header http://www.excelbanter.com/showthread.php?t=448800&goto=newpost Wed, 22 May 2013 12:14:57 GMT Hi! :-) I have spent the last few days trying to make a macro in vba to customize the right header, in a excel sheet with multiple pages, but without much... Hi! :-)

I have spent the last few days trying to make a macro in vba to customize the right header, in a excel sheet with multiple pages, but without much success. :-(

I need some help to do the following macro:

"In first right header page": N. M001 a M002
"In second right header page": N. M003 a M004
"In third right header page": N. M005 a M006
"In fourth right header page": N. M007 a M008
"In fifth right header page": N. M009 a M010
"In sixth right header page": N. M011 a M012
"In seventh right header page": N. M013 a M014
"In eighth right header page": N. M015 a M016
...
...
...
to the last page


Thanks in advance


]]> Excel Programming kimkiko@gmail.com http://www.excelbanter.com/showthread.php?t=448800 MS Query http://www.excelbanter.com/showthread.php?t=448799&goto=newpost Wed, 22 May 2013 09:19:55 GMT I have created a Workbook(A) which extracts data from different workbooks(B to G) by using MS Query and stores in Workbook (A) Workbook(A) is sitting on... I have created a Workbook(A) which extracts data from different workbooks(B to G) by using MS Query and stores in Workbook (A)

Workbook(A) is sitting on Server A.

Workbooks (B to G) are located on N, O, P, Q, R, S i.e. 6 different servers on 6 different locations in 6 different cities.

Workbook(A) has a macro assigned to run the MS Query (Refresh all)

I am connected to Server A and so as my colleague.

Workbook (A) is saved in a folder to which both of us have same access rights to alter, modify etc. all the files.

I can always use macro to refresh all the data and the query runs perfectly fine. However, my colleague encounters a problem and even though can access the file, he is not able to use Macro to refresh and run the query (the error message pertains to path)

I have reached a deadlock and do not have any answer to why. I can fully understand that path problems will come for my colleagues sitting on different servers but I have failed to understand as to why we 2 of us sitting on same server in same folder but my colleague cannot run the query.

Any help would be greatly appreciated.

Ps: I am not technically strong rather a DIY person. ]]> Excel Programming SinghNZ http://www.excelbanter.com/showthread.php?t=448799 Standard column width in points... http://www.excelbanter.com/showthread.php?t=448798&goto=newpost Wed, 22 May 2013 07:35:01 GMT Hi, I can read the standard column width of a sheet with ActiveSheet.StandardWidth ....but this gives me the width in chars.... How to get the standard... Hi,


I can read the standard column width of a sheet with
ActiveSheet.StandardWidth

....but this gives me the width in chars....

How to get the standard columnwidth in points?


TIA,

CE

]]> Excel Programming http://www.excelbanter.com/showthread.php?t=448798 Compare shorter date range against longer date range http://www.excelbanter.com/showthread.php?t=448797&goto=newpost Tue, 21 May 2013 14:16:08 GMT Hi Excel Gurus !

I am in need of a macro / formula which is able to find the missing date range(s).
Example:
John stayed in Apartment A&B for period : 1 February until 15 September.

Rental paid shown in records :
Apartment A rental period : 1 January until 28 February
Apartment B rental period : 1 April until 31 July
Apartment A rental again : 1 June until 31 August (overlaps)

The above start date - end date is keyed into cell A1:B3.

I need excel to show results :
1 March - 31 March
1 September - 15 September
which represent periods where no rental paid was shown in records.

My thousand thanks! in advance for any help on this.
SauQ
]]> Excel Programming SauQ http://www.excelbanter.com/showthread.php?t=448797