ANmarS - Search results

Excel/VBA expert since Sep 1997 (20 years 9 months)

We are moving ...

We are moving to another location, better, faster and more resources .... check it out ANmar.Systems

90 Q & A posts found in April

Mon
Apr 27
2009

Microsoft Excel?????????

I need help finding an if/then statement.
I am looking to find the if/then statement for the bonus-if the employee has worked 40 or more hours they get a 5% bonus based on their Gross pay. If they work lee than 40 hours they get a 1% bonus based on Gross Pay. My hours worked column is in E10 and the gross pay column is in F10. nd the bonus column where I am trying to find the formula is in G10.


Answer

Paste this in F10
=IF( E10>=40, F10*5%, F10*1%)



VBAXLMan

Monday 4/27/2009 5:37:26 AM
Sun
Apr 26
2009

In Microsoft Excel, how does protecting a worksheet affect the autofilter function?


Answer

In the Protect Sheet dialog, you can select what the user can do

You can select any of the checkboxes in the List including
1- Sort a table
2- Filter table (Show/Hide rows)
3- Select cells

VBAXLMan

Sunday 4/26/2009 4:36:54 PM
Sun
Apr 26
2009

In Microsoft Excel, how come when i click the arrow next to "Fill Color", It doesnt show More Fill Colors?

It just shows like 10 colors that are dumb, and says above it no fill.
I want to put a non-retarded color.


Answer

Excel cells can not be colorful as you expect them

It has been known that the colors in Excel cells are restricted to some number

In Excel2007 though, there are much more colors

Try to do CTRL+1 to open the Format > Cells dialog to get more fill color options


VBAXLMan

Sunday 4/26/2009 12:28:40 PM
Sun
Apr 26
2009

Windows Vista Question! Please help!?

Okay, so I have a Windows Vista home edition, and all of the sudden, my video player has stopped working. a few Youtube videos work at random, I have no idea why some work and some don't there no correlation between them, and nothing completely flash dependent like games works. I know I have the newest version of Adobe flash player and Java, and I have enough RAM (my computer has fifty GB to work
with), and it goes nearly daily cleanup, so why won't videos work?


Answer

From your question I understood that you might need to do Windows Media Player updates

Do that here
http://www.microsoft.com/windows/windows…

Good luck

VBAXLMan

Sunday 4/26/2009 12:11:15 PM
Sun
Apr 26
2009

How to calculate expected return and correlation coefficients in excel?

Hi everybody. Someone have any idea how to calculate and what excel formula i have to use to find the expected return and correlation coefficients if I have the following data:

Date Open High Low Close Avg Vol Adj Close*
1 868 872 827 866 7083169900 866
2 855 876 836 870 6839301900 870
3 840 857 815 857 6226187600 857
4 809 846 780 843 6286869900 843
5 772 833 772 816 6952819900 816


Answer

The CORREL function will give you the correlation coefficients, like this
=CORREL( A1:A5,B1:B5)
To find it between the two arrays A1:A5 and B1:B5

VBAXLMan

Sunday 4/26/2009 5:38:15 AM
Sun
Apr 26
2009

How to made a 'formulae spreadsheet'.?

Does anyone know how to print a excel (2007) document the displays the formulas used to create the numbers.


Answer

First you need to show the formulas
- Click the OFfice Logo at the top left corner
- Excel Options > Advanced
- In the "Display Options for this worksheet" select "Show formulas in cells instead of thier calculated results"
- Ok
Now you can print it with CTRL+P as usual

Good lock

VBAXLMan

Sunday 4/26/2009 4:03:30 AM
Sun
Apr 26
2009

Excel: is there a way to emulate the lower function without using lower()?

Excel: is there a way to emulate the lower function without using lower()??

note: it has to work for cells with 2 or more words


Answer

I think there is, but it is going to be too long and time consuming
If your cell is in A1, paste this in B1
=IF(CODE(MID( $A$1,COLUMN()-1,1))=32," ",CHAR( CODE(MID( $A$1,COLUMN( )-1,1))-32))

Then drag it to right to cover all letters of that text
After that, you can easily use CONCATENATE or the & symbol to assemble them into one cell text, like this
=B1&C1&D1&D1
or
=CONCATENATE(B1,C1,D1, E1)

until you reach the end


good luck


VBAXLMan

Sunday 4/26/2009 3:23:34 AM
Sun
Apr 26
2009

When v open the excel sheet the default cell should be b1.?


Answer

If you want to make it so, you need to close that file while B1 is selected

VBAXLMan http://homepage.cs.uri.edu/tutorials/csc…

Sunday 4/26/2009 12:01:54 AM
Sat
Apr 25
2009

MS Office 07 Excel/Access question 10 pts best (right!~)answer?

how do you LINK (not import) an excel worksheet to an Access Database that is already in existence (this database was created by importing data from a separate excel worksheet). I'm stumped! Will award 10 pts best answer! :-)


Answer

When you open Access, and create your database
Go to External Data > Excel
And in the wizard, select the third option (Link to the data ...)

This will create a table that is linked to the Excel spreadsheet.


VBAXLMan

Saturday 4/25/2009 9:00:15 PM
Sat
Apr 25
2009

What is "E" in the equation of the trend line in Excel?

what is "E"? and how can I make it an actual number?


Answer

That means
8 times 10 to the power of -05
Which means
8x10^-05
Means
0.00008


VBAXLMan

Saturday 4/25/2009 7:48:08 PM
Sat
Apr 25
2009

How to resolve this error in Excel 2000: "Cannot save. 'Read Only'" file.?

Hi. I am having a problem when I try to save any kind of file whether new or old on the server from my excel version 2000 program. It keeps on stating "Cannot save. 'Read Only' file." all the time, I can save on the desktop but I can not save on the server and the only fix that I have come up with is disconnecting and reconnecting the server drive again. Any other suggestions out there on what might resolve this problem for good????? Thanks and have a wonderful day! I really appreciate it.

Sincerely,

Alina


Answer

You need to make sure that the folder you are saving in is not a Read-Only folder

Check also all the parent folders of that folder

They all should have Read-Write mark (Not Read-Only) to do that

Right click on a folder > Properties > Read-Only checkbox



VBAXLMan

Saturday 4/25/2009 6:20:47 PM
Sat
Apr 25
2009

Code in Sheets vs. Code in Modules; Excel?

I transferred all my module code into specific worksheets modifying Subs to Private Subs and replacing Form Controls with ActiveX controls. Am I running into any disadvantages doing so? Is it helping me any, besides the fact that pressing ActiveX buttons feel more like clicking buttons than Form Control buttons do?


Answer

I can think of one...
If you want to do a USER-DEFINED-FUNCTION, then you can not do it in a worksheet module, you would have to do it in a Module

Other than this, You still can call any sub/function in a worksheet form other worksheets, using the "Public" key before the sub/function name


One more thing, selecting ActiveX or Form controls are not affected by Module or sheet module


VBAXLMan

Saturday 4/25/2009 1:48:16 PM
Sat
Apr 25
2009

Free program to keep track of merchandise?

I want a free program that I will be able to keep track of how much of any particular item I have sold and how much money I have made from each item..Any such programs. I guess I could you excel but I really didn't want to..thanks


Answer

Here is a free one
It is kind of old, but it can do what you are expecting
http://www.file1.net/project.asp?id=MqHT…


VBAXLMan

Saturday 4/25/2009 1:36:16 PM
Sat
Apr 25
2009

I want to add no. of hours spreaded in multiple rows in excel, can any body tell me the formula for the same?


Answer

If you have these cells values entered as numbers (and not dates), then use the regular sum function, like this
=SUM(A1:A5)

If you have these cells values as times (or dates) then that would requires some work
Try using this one
=HOUR(SUM( B:B)-INT(SUM( B:B)))

Where you have the dates in column B


VBAXLMan

Saturday 4/25/2009 8:50:20 AM
Sat
Apr 25
2009

Excel 2003 VBA searching and editing data using a userform?

Hey!
I have a lot of data on an excel spreadsheet which has many columns for different required fields of pieces of data.
New data can be input using a user form, and this works well. However now i would like the user to be able to search for a row of data and update/edit the details of this data using a userform. How can i do this? (I have already figured out how to use the Search command, just cant do the updating!)


Answer

You would need some VBA work to be done

If you are not the one who did that userform, then contact that guy to do that for you

If you like, send me the file (or sample file) to
XL@VBAXLMan.com

And I would be very happy to do that for you, I live on these Excel things


Let me know your decision

VBAXLMan

Saturday 4/25/2009 7:32:35 AM
Sat
Apr 25
2009

I am using Microsoft Excel 2003.?

I am wanting to make a list of words so that by typing in the first letter it will automatically fill in the whole word. I do not want to have to type the list of words first (there are alot of them!!), but for this to be a setting which i can apply to different sheets. I do not want the validation option of a drop down list (there are just too many words i need to auto fill) Is this possible?
Please help! thank you!


Answer

Options 1:
If you have these words in one column, once you try to enter a word, Excel will automatically list the words that starts with the same letter

Option 2:
If you have all these words listed in a column also, right click on the empty cell (below it) and select "Pick from list"

Option 3:
Data Validate (which you already refused to do) but, you can make it Auto-updated, so when you add a word to your list, it will automatically be in the populated list
http://www.file1.net/lesson.asp?id=a1vh8…

Option 4:
Use some macros to do that

Option 4 requires some work and sample files, reply me here if you are interested



VBAXLMan http://www.file1.net/search.asp?query=va…

Saturday 4/25/2009 7:05:09 AM
Sat
Apr 25
2009

How do you develop a Trend Equation graph on Microsoft Excel 2007?

I need to develop a trend equation graph for current ratios and other financial ratios, any help would be grateful, thanks


Answer

What do you mean by "Develop"?

Need more info please


VBAXLMan

Saturday 4/25/2009 6:19:14 AM
Sat
Apr 25
2009

How do you develop a Trend Equation graph on Microsoft Excel 2007?

I need to develop a trend equation graph for current ratios and other financial ratios, any help would be grateful, thanks


Answer

What do you mean by "Develop"?

Need more info please


VBAXLMan

Saturday 4/25/2009 6:17:44 AM
Sat
Apr 25
2009

How can i enter my country date in excel instead of english date?


Answer

You need to set the date in Control Panel to your country code to do that

Control Panel > Regional Options


Once you do this, it is automatically changed in Excel to your way


VBAXLMan

Saturday 4/25/2009 4:46:46 AM
Sat
Apr 25
2009

I want Convert Numerical numbers convert in characters in excel.?

What’s the formula for the converting while I am use a formula =num2text(e5) e5 is a cell where is a numerical digit.
Example 100 hundred
101 hundred one
102 hundred two
Tell me what’s problem in above formula or if any other suggestion.


Answer

Here is the file that does that

http://www.file1.net/project.asp?id=KoFR…

It can do English and Arabic


Enjoy it

VBAXLMan

Saturday 4/25/2009 4:34:20 AM
Fri
Apr 24
2009

How do I get Excel to do this?

Let's say I type a numerical value into cell A1. How do I set it to auto-calculate 56% of that value and plop it back into that same cell? In other words, set a cell to display a calculated value of a number a user inputs into the same cell.


Answer

You would need a macro to do that, try this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Target.Value = Val(Target.Value) * 65%
End If
End Sub

The 1 is for column A, change it to 2 if you have B, 3 if you have C, etc
Also change the 65% to whatever percentage you may have


To use that code, press ALT+F11, double click on the sheet name in the "Project Excplorer" window (Up left) and paste this code


Mail me here if you have problems


VBAXLMan

Friday 4/24/2009 7:08:35 PM
Thu
Apr 23
2009

I need an MS Excel Formula that generates the total time taken from a list of times.?

Example.

I have a list of times (clock times) and I need to get the total time (duration).

5:30 am
5:45 am
6:15 am
6:30 am

The total duration time spent should be 1 hour and 15 mins or 75min.

Can I get an excel formula that does the above?


Answer

I am not sure if I got you correct here, but let me try this...

Option 1:
Now, if you have that list starting from cell A1 down
Paste this in B1
=MINUTE(A1)+HOUR( A1)*60&"min"
Then paste this down to get the others

That is one possibility answer to your question

Option 2:
Another one is this
=HOUR( A1)&" hours and "&minute(A1)&" min"

Option 3:
Or this
=SUM(A:A)


Let me know

VBAXLMan

Thursday 4/23/2009 11:41:34 PM
Thu
Apr 23
2009

[Excel/VBA] How to prevent excel to scroll up after typing ?

Hi

Each time I enter something at the bottom of a sheet, excel scrolls up the page (half of window height).
Is there a way to prevent (or reduce) this ?

Or some VBA to place a given row (last one) at the bottom of the window ?

Thanks in advance


Answer

First part of your question, no is the answer

For the second part, yes there is, it will depend on the Zoom percentage you are having and the height of your Excel screen

Try doing a macro to...
1- Scroll to the end of the end of your table (last row on your table)
2- Scroll back up to 10/20/30 rows
3- Activate any cell in the last row of your table

Something like this

Sub ShowLast()
CC=Worksheetfunction.Counta( Range("A:A"))
Range("A1").Offset( CC).Activate
Range("A1").Offset(CC-20).Activate
Range("A1").Offset( CC).Activate
End Sub


You need to try it and change the 20 to what ever suitable for you



Good luck



VBAXLMan

Thursday 4/23/2009 11:24:16 PM
Thu
Apr 23
2009

Excel question - convert a date to text?

I have a date (example: 10/26/2008) that I want to change to text and return Oct - 08 (as text, not just formatted date field). Note: no day, just month and year.


Answer

Do as Chaminda suggests
Then copy these resultant cells
Paste them over the original cells but with "Paste Special" > Values

Do not use regular Paste command


VBAXLMan

Thursday 4/23/2009 7:37:14 PM
Thu
Apr 23
2009

Can i add music to microsoft excel workbooks? How?

My ICT teacher suggested that i add music to a workbook to try and get more marks for a DIDA project i've been doing, but i havn't got a clue how to do it!

Any clues?


Answer

Here is a free file would do that

http://www.file1.net/project.asp?id=I3al…

You can only change the wav file

VBAXLMan

Thursday 4/23/2009 6:05:47 PM
Thu
Apr 23
2009

Excel > Use formulas on the last x rows of data?

I'm keeping track of the total number of wins and losses for a sports team. There's one game a week. I will have 16 rows in my Excel sheet to mark a win or loss. If the team wins that week, I'll put in "W" for that week's row, and "L" for a loss. As you can imagine, a new row will be populated with either a "W" or "L" as the weeks progress.

I'm trying to get a win/loss count of the last 5 games played. So if the team has played 7 games, I want to show the total number of wins and losses of the last 5 games (games 3-7). If 10 games have been played, then show the total number of wins and losses for games 6-10. This is similar to the L10 statistic for baseball, hockey, and other sports, but in this case, I'm trying to do a L5 for the last five games.

How can I achieve this in Excel? I'm going to use COUNTIF for counting the total number of "W" and "L", but I need it to count only the last five rows of data.

Any help is appreciated. THANKS!


Answer

Do this so that whenever you add new row/week it will automatically updated

Now, if your list of Ws/Ls starts from A2 down, paste this in D1
=COUNTIF( OFFSET(A2, COUNTA(A:A)-6,0,5,1),"W")
To get the number of Ws in the last 5 non empty cells in Column A

And do this in E1
=COUNTIF( OFFSET(A2, COUNTA(A:A)-6,0,5,1),"L")
To get the number of Ls in the last 5 non empty cells in column A

Also assuming you have the cell A1 with text as header

Good luck


VBAXLMan

Thursday 4/23/2009 4:19:56 PM
Thu
Apr 23
2009

I want to convert number to text using Excel 7.0 function.please give suggestions?

Ranjeet chiplunkar
email ranjeet_chiplunkar@yahoo.co.in


Answer

Here is the file that does that

http://www.file1.net/project.asp?id=KoFR…


VBAXLMan

Thursday 4/23/2009 4:18:21 PM
Thu
Apr 23
2009

Excel line between rows 264 and 265?

If you scroll in your all the way down in excel you will see a black line between does two rows. Is there any point to it?


Answer

That usually because you had data before (or Format cells) up to these rows and deleter them using Clear and not delete

To get rid of that, you should "DELETE" the rows (Right click and select "Delete")

This will also reduce the size of your file

If you have any problems or didn't understand the answer, mail me here


VBAXLMan

Thursday 4/23/2009 3:32:19 PM
Thu
Apr 23
2009

When i set my printer as default excel becomes very slow why?


Answer

Since Excel check up for the default printer before starts and while working

It is a bug between Excel2007 and some printer drivers

I got a default printer that make Excel (and Word) crash when closing

Expect a lot of these from Microsoft

VBAXLMan

Thursday 4/23/2009 3:03:38 PM
Thu
Apr 23
2009

Microsoft Excel - Treeview 6.0 in User form?

I have treeview 6.0 in a user form. Does anyone know how to link charts or data to the tree view, to display in a window in the user form when clicking a node? Any help would be GREATLY appreciated. Thanks!


Answer

Since you are in the VBE of Excel, you can do this

- Draw a picture box that shows when the user clicks the node
- Copy the chart and paste it as picture in some temp sheet
- Then read that picture into that picture box

This is what I have in mind right now, I think there are other ways...

Let me know what you came up with, I liked your advanced question


VBAXLMan

Thursday 4/23/2009 9:05:09 AM
Wed
Apr 22
2009

How to lock the sheet in excel 2007?


Answer

In the "Review" tab, click on the "Protect sheet"

I also recommend doing "Protect workbook" to lock the visibility of the sheet


VBAXLMan

Wednesday 4/22/2009 7:56:21 PM
Wed
Apr 22
2009

Ms excel help.. .. finding the coefficient of correlation, r^2?

ok so i need help finding the r^2 of linear and nonlinear regressions, to compare which ones fit better......... using excel.

eg. if power reg. curve is 0.998 and quad. curve is 0.992 ... then id choose power reg,

i know how to do it on a graphing calculator... but i need it for an assignment......

so how do i find it for a curve?

p.s. ive got excel 2003.....


Answer

Once you got a graph
- Right click on the line
- Select "Add Trendline"
- Select the type of that trendline
- Go to "Options" tab
- Click on "Show R2 equation"


That is all


VBAXLMan

Wednesday 4/22/2009 6:17:33 PM
Wed
Apr 22
2009

How to highlight single row by selecting a single cell in ms Excel?

dear all,

im having an question related to MS Excel...i wanted to know if i select a single cell it should highlight the entire row...this option should be friendly like whenever i required i should activate it....is it possible.?? if any one know how to do so please let me know.

thankx in advance


Answer

Here is the file that will do that
http://www.file1.net/project.asp?id=GlcN…

Once you enabled macros, you need to...
Click CTRL+W, to turn that feature on
Click CTRL+E, to turn it off

Enjoy it

VBAXLMan

Wednesday 4/22/2009 6:08:59 AM
Wed
Apr 22
2009

How do I write an excel formula to display an asnswer if a Number in one cell appears in another column?

I need to find out a formula that will display an answer in a blank Cell if the number contained in another cell in the same row is present within a whole column on another worksheet.

For example, if 1 is present in cell B1 in worksheet 1, then I want the formula to be able to search the whole of column C in worksheet 2 for that number (1) and if it is present display a custom text (e.g. training) in the blank cell A1 on worksheet 1.

I know my description is pretty garbled, but if someone could help I’d be eternally grateful.


Answer

I guess you want to know the famous VLOOKUP function

Here is a free video lesson that talks about that
http://www.file1.net/lesson.asp?id=b1vi8…


Check out other lessons below

VBAXLMan

Wednesday 4/22/2009 2:26:46 AM
Wed
Apr 22
2009

How Do I Send Bulk Emails With Attachments Unique For Each Contact?

Hi,

With help in the past I've been able to grow my excel VBA knowledge widely.

I've been able to create new workbooks and save them out according to a company name in cell "E2" and a date in cell "G2".

So I now have a list of 40+ excel workbooks uniquely named by 'Company' and date and saved in the companies folder on a network drive that need to be emailed to each of the unique companies each month.

What I need to do to save me time is to automate the bulk emails and attachments.

I've found links and codes for sending an email for one attachment or email for the active workbook, what I can't seem to find are suggestions for sending out emails according to a list of addresses (from excel) then have a pop up saying:

"<company name> email ready, please select an attachment"
and have a dialogue box for you to get the attachment.

then loop to the next company name and address with a standard body text for each email.

Then Send all the emails out.

Any help would be GREATLY appreciated.

p.s. also, how would one set the 'from' part.


Answer

I know this one now
http://www.file1.net/project.asp?id=4S37…

This will send bulk emails with separate subject and body (No attachments)

I think I can (or you can) enhance it to add a column with the path to attachment file, then play with the code to do the attachment

Let me know if you can do this, I am sure can do that for you, mail me here



VBAXLMan

Wednesday 4/22/2009 12:26:28 AM
Tue
Apr 21
2009

How do I import data into a program like excel through a virtual RS232 port?

I bought a scale and a USB interface kit, which connects the scale to the computer by using a generic interface based on the RS232 serial standard. According to the manual, data sent from the balance is in USB format, which is then directed to a virtual port. This port then appears as an RS232 port to the application program. It came with a cd with software to create the required virtual port, but I don't know how to get an application to connect to the port, or which application to use.


Answer

I am afraid this will depend on the application you have in that CD

What is the type of the files that that application can export to?

If it is a common one (I guess they must have a common one) then you can easily export ti to Excel

Common ones like
Tab delimited text (TXT)
Comma Separated Values (CSV)
etc


VBAXLMan

Tuesday 4/21/2009 3:56:31 PM
Tue
Apr 21
2009

How to disable save as option in excel & Word?

How to disable save as option in excel & Word, to protect the file from copying. But the Respective person should have the option available.


Answer

In Excel 2003, put this VBA code in "Workbook" object after you press ALT+F11
Private Sub Workbook_Open()
disablesaveas
End Sub

Then Add "Module" and paste this in
Sub disblesaveas()
CommandBars("File").Controls("Save As...").Enabled = False
End Sub

Then close the file and open it again

If you have Excel 2007, (You can also do this in Excel2003) do this
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI Then Cancel = True
End Sub


Good luck


VBAXLMan

Tuesday 4/21/2009 8:50:19 AM
Tue
Apr 21
2009

Excel 2007: Autoshapes dislocated on printing?

I have a 2003 excel file which has many arrows (autoshapes)... once we upgraded to office 2007, these arrows appear bit moved (dislocated) when printing or previewing. Tried file xls and tried to upgrade it to xlsx and still the same problem when opening in Excel 2007. Any idea why this is happening? is it a bug of compatiibiltiy between Excel 2007 and Excel 2003?
why?


Answer

Mostly yes, it is a bug

I would recommend to open that file in Excel2003 and group these objects then save the file and use it in Excel2007

Also, try the following:
1- Install the same printer you were using in Excel2003 and make it default
2- Do some cleaning to your computer (Follow these steps http://answers.yahoo.com/question/index?…
3- Make sure you have the Print scale to 100%



VBAXLMan

Tuesday 4/21/2009 7:22:25 AM
Mon
Apr 20
2009

How the heck do I use MS Excel?

I need to make charts using MS Excel but am lost. How in the heck do I learn how to use it?


Answer

These videos will help a lot

http://www.youtube.com/results?search_ty…


VBAXLMan

Monday 4/20/2009 2:20:15 PM
Sat
Apr 18
2009

In excel if i enter "a" in 1st sheet then a's record must be saved in 3 rd sheet if "b" enterd then b's in2nd.?


Answer

If the cell in 1st sheet where you put "a" is A1
Then in the 3rd sheet, paste this in A1
=$A$1

Then copy and paste that to the cells below to make the entire column linked to one cell

Now once you change A1 in 1st sheet, the 3rd sheet will be automatically changed

That what I understood from your question, if not what you needed, mail me here with more details

VBAXLMan

Saturday 4/18/2009 11:38:27 AM
Sat
Apr 18
2009

Macro to check character count & give error if exceeds 500, and display d cell with error in msgbox.plz help?

Macro to check the character count in excel worksheet and give error if exceeds charatcers exceeds 500 including spaces and other special characters. Also display the cell number with more than the specified limit in a msgbox. Pls help


Answer

You can not read the number of chars in a specified cell until the user hits Enter or selects another cell

That including VBA or Validation

If that is OK with you, you can use the Data > Validation method to achieve that
Here is a lesson on how to do that in Data > Validation in Excel2007
http://www.file1.net/lesson.asp?id=FLn3D…

VBAXLMan

Saturday 4/18/2009 5:48:23 AM
Sat
Apr 18
2009

Can you format an Excel cell with a formula and without using a macro or the Conditional Format menu?

Can you enter a formula directly into a cell to change the format of the cell, mainly the font color or the background shade color?


Answer

No
No formula there to change the format of another cell (If not macros nor Validation)

Forget it

Sorry!

VBAXLMan

Saturday 4/18/2009 12:52:04 AM
Sat
Apr 18
2009

VLOOKUP & HLOOKUP IN MS-EXCEL?

plz. tell me in brief abour "VLOOKUP & HLOOKUP" with examples.


Answer

Here is a free video lesson about VLOOKUP
http://file1.net/lesson.asp?id=b1vi8438X…

If this is basic for you try these
http://file1.net/search.asp?query=vlooku…

VBAXLMan

Saturday 4/18/2009 12:04:40 AM
Fri
Apr 17
2009

What does the $ do in an excel spreadsheet?

What is the function of the $ sign in an excel spreadsheet?


Answer

It is called the Absolute and Relation symbol
Here is a free lesson about what it is doing
http://file1.net/lesson.asp?id=GlcNf6q11…

VBAXLMan

Friday 4/17/2009 9:44:03 PM
Thu
Apr 16
2009

It there a way(possibly write a macro) to automatically sort and delete data in Excel?

I am handling data which is a mix of numbers and letters, I need to sort by some value, delete, then re-sort and delete and so on, about 4-5 times.

Thanks,
Sharad


Answer

Here is an auto Sort free video lesson
http://file1.net/lesson.asp?id=XJTFWqI1c…
And this one
http://file1.net/lesson.asp?id=a17h833WW…

And this one one how to delete rows
http://file1.net/lesson.asp?id=b2wia4l8X…

VBAXLMan

Thursday 4/16/2009 3:28:55 PM
Thu
Apr 16
2009

How to deduct tax from wage in excel?

I have a spreadsheet set up in excel to manage my finances (in truth, it is in Open Office, but it is so similar it may as well be the same).

I have it set up so I enter my hours, and it calculates, depending on my hourly rate) my wage for the week / month / year; as well as a few other things.

This works well, but gives me a pre-tax figure.

I was wondering if anyone could think of a formula to use to deduct tax from the figure? Would be very helpful.

If it helps, I am in the UK, and am on Tax Code 647L.


Answer

Put the Tax percintage in any cell (say cell A1)
Then use this formula to deduct the tax

=B1-B1*A1

Where B1 has the amount before tax

You can also do this to add tax
=B1+B1*A1

VBAXLMan

Thursday 4/16/2009 1:40:09 AM
Wed
Apr 15
2009

Microsoft Office Excel has encountered a problem and needs to close. We are sorry for the inconvenience.?

Frequently i get this type of Error Report when am using Microsoft Excel. What does this mean exactly? What is the problem? How to prevent this? After this message i could not able to open my file. Could anyone answer for this?


Answer

I faced that exact problem a month ago, it turns that because I have an HP as the default printer
Once I chose another one, this problem gone

Try it, that might be yours


VBAXLMan http://guymclaren.com

Wednesday 4/15/2009 11:11:01 PM
Tue
Apr 14
2009

How can we use macros in excel. how we can have advantage from macros?


Answer

Macros are automating Excel processes
So that you can Automate the daily Excel process you are doing for example
Copy certain region, save the file, open another one and paste the copied there
Or anything like that

There are a lot of Excel macro free files out there, check some of them here
http://www.file1.net/Projects.asp?id=18

There are 42 now

VBAXLMan

Tuesday 4/14/2009 1:34:05 PM
Mon
Apr 13
2009

VBA to show excel path name with server path?

Currently I am trying to have VBA paste the file and path to cell A1 of the current file I am using. The problem I keep having is that I get which includes the mapped drive letter instead of the server path:

H:\FILES-Excel\testbook001.xls

but what I need to get is the server name path instead i.e.

\\ServerXYZ\FILES-Excel\testbook001.xl…

Does anyone have code to do this?


Answer

You will need to UNMAP the server path, open the file and run the command that will give you the full path as you required

OR try this command, if you are not using it anyway

Path1 = ActiveWorkbook.Path

Try it

VBAXLMan

Monday 4/13/2009 9:35:21 PM
Mon
Apr 13
2009

Separate English and Traditional Chinese (excel file) into 2 columns without special characters (; or : or *)?

Hello there:

Hope u can help me...

I have a huge list of Buddhist terms (20,000) in Traditional Chinese, together with the English definition for each of the 20,000 entries. Problem: all of the terms are listed in a single column, with the english definition coming before every Traditional Chinese concept: what I try to mean with this is that some english definitions involve more than one single word (some definitions have 23 words as a whole), and the same concept, but in Chinese, might involve more than just one character (up to 7).

I cannot use a "space" to separate the text into different columns, because of the amentioned reason, there are not special characters that separate the English of the Chinese (such as ; or : or *), its all like the following sample:


9-11 am ??
a (walled) city ?
a bed ??
a day without work is a day without food ????????
a heap ?
a ko?i of nayutas ?????
a man ??
a thread, a butt ????
a wild fellow ???

like this, all the way up to 20,000

So, do u know of any way I can separate the whole text into different columns, based on the used script (Chinese and English)?

I asked some Computer science teachers at a local university: they suggested to create a macro that differentiates the script in use according to ASCII (english letters come with very low numbers, while Traditional Chinese must involve really big numbers, or so they said), but, I confess that my knowledge of computing is extremely low (main reason Im contacting u, btw...)

Hope u read this message, understand my broken english, and give me an answer...

Thanks a lot

Cheers


Answer

You don't need a macro to do that
I just fixed something like this (in pure functions) few days ago, it was about changing CamelCase to Proper, here is the file in case you are interested

Modifying that file, you can do something like this

If you have the cell in B4, paste this in E4

=IF(OR( COLUMN()-4>LEN( $B4),SUM( $D4:AC4)>0),"",IF(CODE( MID( $B4,COLUMN( )-4,1))>150,COLUMN( )-4,""))
Then copy and paste up to column AD (to get the possibilities of 26 characters)
Now in cell AE4, paste this to get the left side of the text (Before the first Chinese char)
=LEFT(B4,MAX(E4:AD4)-1)

And this in nAF4 to get the right part (after the first Chinese char)
=MID(B4,MAX( E4:AD4)+1,500)


Good luck with that, let me know if you didn't understand it or you want to edit it

VBAXLMan is here to fill your Excel needs

Monday 4/13/2009 12:09:05 PM
Mon
Apr 13
2009

Can you change the color of the Split or Freeze lines in Excel?


Answer

This splitter is one of Windows environment colors, you will need to change it from there

This is not Excel issue, Excel itself reads it from the OS

VBAXLMan

Monday 4/13/2009 10:45:24 AM
Mon
Apr 13
2009

In an Excel Macro, how do I return a value that the user selects in a userform?

For instance, the user can select one of three options via an option (radio) button (i.e. apple, orange, banana). I need code that will return this selection to module1 as a variable.


Answer

Paste this in your userform assuming:
1- You have the names of the radio buttons as Option1, Option2, etc
2- The user clicks Ok (named as "CmdOk")

sub CmdOk_Click()
If Option1.Value then Option_Sel=1
If Option2.Value then Option_Sel=2
' Do as many lines as you have in your Options
end sub

Then paste this in Module1

Public Option_Sel as integer


This will make the Option_Sel as global variable and then you can use it in any sub in your entire project


Let me know if that is fine or not


VBAXLMan

Monday 4/13/2009 8:58:02 AM
Mon
Apr 13
2009

How to add Counter to an excel workbook?

How to add Counter to an excel workbook, such that we can track how many times the excel had been viewed. The countability can be tracked?


Answer

- Press ALT + F11 to open VB Editor
- Double click on the "Workbook" object in the Project Explorer window
- Paste this in the white code area
Private Sub Workbook_Open()
Sheets( "Sheet1").Range("A1") = val(Sheets( "Sheet1").Range("A1")) + 1
End Sub

- You need to make sure that your sheet is named "Sheet1", if not, just put the name of your sheet instead of Sheet1 in this line
Sheets( "Sheet1").Range("A1") = val(Sheets( "Sheet1").Range("A1")) + 1


Good luck

VBAXLMan

Monday 4/13/2009 7:45:11 AM
Mon
Apr 13
2009

Miocrosoft Excel: I want to have a drop down box of materials, which shows thew cost in the cell next to it?

I am producing a quotation system on Excel, and want the materials to be chosen from a drop down box, and the price of te material to be automatically displayed. Any assistance to
seduardo15@yahoo.co.uk


Answer

Here is a lesson showing you how to do it

http://www.file1.net/lesson.asp?id=aFhEw…



VBAXLMan

Monday 4/13/2009 7:00:19 AM
Mon
Apr 13
2009

MS Excel . I want to search the value of one cell among other cells to check the validity of that cell among o?

Dear Sir/Madam,

To make my question more clear I want to search the value of one cell among other cells to check the validity of that cell among other cells.for instance,

a1:Jack
b1:paula
b2:jack
b3:matue
b4:sara
in case of making chart c1:true or false
in this example=T

a1:Jack
b1:paula
b2:sam
b3:matue
b4;sara

in case of makin chart c1:true or false
in this example=f


Answer

You can use COUNTIF, like this
=COUNTIF( A1, B:B)
To count the number of times that the cell A1 showed in the range B:B

Or may be like this
=IF( COUNTIF( A1,B:B))
To get True or False


That is what I could understand from your foggy question

VBAXLMan

Monday 4/13/2009 3:44:05 AM
Mon
Apr 13
2009

Is it posible to change the colour of a cell by using functions in MS-Excel ?

By Using functions in Ms Excel can we change the colour in a cell.
for example: if A1=1 then RED colour, A2=2, Then YELLOW colour..


Answer

In Excel2003, go to
- Select the cell
- Format > Conditional formatting
- Enter the values and the format for each condition (maximum 3 conditions)
- Press Ok

In Excel2007
- Home > Conditional Formatting > Highlight cells rules > More rules
- Select the values and the format for each
- You can do up to 256 conditions

Enjoy it

VBAXLMan

Monday 4/13/2009 3:04:36 AM
Sun
Apr 12
2009

HELP?!?!??! Microsoft excel?

I made a bar graph on Microsoft excel but how do i label the bars (along the X axis) ??

plz help, rlly confusin

thnkx in advance =]


Answer

In Excel2003, do these steps:
1- Select the graph
2- Go to Chart > Chart Options
3- Then go to Chart Titles and change the boxes for x-axis and y-axis labels

In Excle2007, do these
1- Select the graph
2- Two new tabs will be shown after the last one, go to
Layout > Axis title
Then do the Horizontal or Vertical one, based on your needs


VBAXLMan

Sunday 4/12/2009 9:00:33 PM
Sun
Apr 12
2009

Excel cannot access or encrypted hey friends plz help me this is a big problem for my HR Department?

Excel cannot access or encrypted hey friends plz help me this is a big problem for my HR Department


Answer

You need to give more details, please

It is not helping when you repeat the question


VBAXLMan

Sunday 4/12/2009 2:46:21 AM
Thu
Apr 9
2009

How do I merge multiple Excel worksheets into one Master sheet that updates?

I am creating a program for the agents in my real estate office. It is a Lead Management program. It has 5 worksheets, one for each different type of customer. I would like a 6th sheet that will contain all the names from the other 5 sheets. I also would like the "Master" sheet to update automatically when a new lead is added or changed. HELP!!!


Answer

Actually
SUM function WILL work fine
It will omit cells with names, or anything else than numbers
So it will sum only the numbers in the range

I would suggest checking out this video lesson, it will help you to do SUM across sheets
http://file1.net/lesson.asp?id=fjame5p10…

Then do the SUM using the same context
=SUM( INDIRECT( bla bla bla))

Lets assume you have the sheet name in cell A1, and you want to sum the row 1 from that sheet.
So paste this in cell B1
=SUM( INDIRECT( A1&"!"&ROW()&":"&ROW() )

Then copy it down to cell B2, B3 and B4 to make these cells get the total of the corresponding rows from the sheets in the range A1, A2, A3 and A4 respectively

Hope this helped

If not, mail me here

VBAXLMan is here to fill your Excel needs

Thursday 4/9/2009 2:01:12 PM
Thu
Apr 9
2009

How to find people to make video tutorials?

I have started an internet company, and we are looking for competent people to produce video’s for us on how to use various software applications. I am having real problems finding the right people. What are the best sites to advertise on? How can I best get in contact with the right people?


In essence I need to find a good strategy to find people to make the video's. The rate of pay is on a per video basis with most people averaging about $20 an hour or more. We are having trouble finding people to meet our needs. We have tried approaching people on Youtube / other video sites, but his has proved very inefficient. We did approach people that work for competing websites, but they are asking too much. We are a start-up site, with limited financial resources.

All that is needed is someone with a clear voice, and good knowledge of specific software, and free software and a microphone (found on most webcams). The computer would record the screen, while you give an audio commentary (on how to reconfigure the autocorrect in Microsoft Word (for example)).

Over the next 4-8 weeks are looking to produce 30-100 high quality video tutorials for each of the following subjects:

• Vista,
• Windows XP,
• Excel,
• Outlook,
• Powerpoint,
• Publisher, Photoshop CS,

An audio commentary would be required, and an example of our site content can be found on visiting www.helpvids.info (soon to be www.helpvids.com !).

If you know of anyone that could help please reply to this post or email videos@helpvids.info .

Obviously I am open to other strategies or suggestions for commissioning people on a "work for hire" basis? What are the best sites for me to go on?


Answer

That is good
I am thinking of joining you guys

shoot me an email here

XL@VBAXLMan.com

Thursday 4/9/2009 12:58:20 PM
Thu
Apr 9
2009

Can I count number of rows based on 2 critera that are inputted by the user?

I have many sheets of raw data that I am trying to Roll up in a simple Dashboard using excel. So far I have managed fine but have come stuck on this part.
I am trying to count a number of rows based on 2 criteria. The problem is these criteria are inputted via validation list from the user!

The user selects a name of a program and what week number they would want to view a button is selected to run a macro that calculates this information.

The macro is no problem just the input of the search criteria via validation list.
I have tried everything I know but cant figure it out.
Any help is much appreciated


Answer

I would rather you do DSUM
Here is a video lesson on how to do it
http://file1.net/lesson.asp?id=6d2CvCTrt…

You can make the criteria range as the same as the validation cell (Excel will not mind)

Let me know if everything goes well or not

VBAXLMan

Thursday 4/9/2009 12:12:14 PM
Thu
Apr 9
2009

Microsoft Excel question?

I have a column of percentages E127:E132 and I need a formula that will return one answer ($250.00) if there are three or more items in the column above 98% and another answer ($0) if there aren't.


Answer

=IF( COUNTIF(E127:E132,0.98)>=3,250,0)

Paste this in a cell

VBAXLMan

Thursday 4/9/2009 8:23:55 AM
Thu
Apr 9
2009

How do I create a formula that pulls information from one excel spreadsheet to another?

I have multiple spreadsheets in the same excel file and I am trying to create a formula or a set of formulas that will recognize a number, compute how many of the items have that number and then on a separate spreadsheet, show that sum. What I am working with are 7 spreadsheets- 4 weeks, month, quarter and year. In the week spreadsheets, an incident is labeled with a 1,2,or 3 depending on its severity. I need a formula that would calculate all of the 1s, 2s and 3s separately as their own value and then place that value in my month spreadsheet in the 1, 2 or 3 category.

Thank you in advance for your help.


Answer

If you have:
1- The week sheet name is "week"
2- The range where the 1s, 2s and 3s are in "week" sheet is A1 to A20
3- The month sheet name is "Month"
4- 1 in cell A1 in "Month" sheet
5- 2 in cell A2 in "Month" sheet
6- 3 in A3 in "Month" sheet
Then paste this in B1
=COUNTIF( Week!$A$1:$A$20, A1)
Then copy it to B2 and B3

VBAXLMan

Thursday 4/9/2009 5:20:30 AM
Thu
Apr 9
2009

I want to know the Ms-excel formulas. to use excel.?

I want to know different formulas in excel like vlookup,hlookup,pivot table,conditioning formats & links ect.,


Answer

There are a lot of websites that can help you
try these

http://MrExcel.com
http://www.File1.net

VBAXLMan

Thursday 4/9/2009 12:16:34 AM
Wed
Apr 8
2009

I received one excel Sheet from my friend,in that sheet?

he mentioned DATE in one cell,when i select that cell the TIME also appear in FORMULA BAR.but i cannot see TIME in the cell.Pls explain how to make the setting.

And also how to type in cell superscript or Power with the numbers.

thanks & regrads
lahhak


Answer

The cell content is not all the time what shown on the cell output
That is called format
Meanning that the cell may not show everything in it
You will need to change the format of the cell

To do that, you need to select that cell, press CTRL+1 and choose another format from the list
I would recommend to go to Custom and do this
yyyy-mm-dd hh:mm

To do Superscript or Subscript, you need to do almost the same

- Select the text you want to superscript (not the cell only, but the text inside that cell)
- CTRL+1
- Font
- Superscript or subscript

Enjoy it

VBAXLMan

Wednesday 4/8/2009 8:57:31 AM
Wed
Apr 8
2009

Please help with an excel formula?

I am trying to make a cell look at a specific letter combination, please see my unsuccessful formula below:

=IF(I7=HO,12,IF(I7=RO,12,IF(I7=ID=12,I…

I would like it to look for them specific letters & return the number that corresponds with it.


Answer

I would rather you to do the proper way
Which is:
1- Put the possible values expected in cell I7 in range A1 to A5 for example
2- Put the corresponding value to the cell next to it in B1 to B5
3- Now paste this in cell J7 (Next to I7)
=VLOOKUP(I7, $A$1:$B$5,2,FALSE)

Enjoy it

VBAXLMan

Wednesday 4/8/2009 7:50:56 AM
Wed
Apr 8
2009

How do I find and list all values in Excel?

I have a workbook with many sheets listing all of the schools my org interacts with. Each sheet lists 1-5 email addresses, depending on how many contacts we have.

I want to have a summary sheet that lists all of the email addresses so it's easy to copy-paste them to send out newsletter.

I did a FIND/REPLACE for "@" and it found all of the email addresses, but it won't let me copy paste them from the search results.

I've also been trying to do reserach on how to do this with a formula, macro, or VB but haven't come across anything.

Thanks!


Answer

Here is a simple macro that will help you doing that

http://www.file1.net/project.asp?id=x16f…

Enjoy it,, it will copy all rows from all other sheets than the current one into it for manipulating easy

VBAXLMan

Wednesday 4/8/2009 1:16:26 AM
Tue
Apr 7
2009

Percentages in excel?

how do i calculate percentages on one column and post them on the next column?


Answer

If you have the number you want to get its percentage in A1, paste this in B1
=A1*50%
To get 50% of the value in cell A1

Then copy and paste B1 down to match all values in column A

VBAXLMan

Tuesday 4/7/2009 8:15:32 PM
Tue
Apr 7
2009

In Excel How do i copy values from every fifth row(1st,5th,10th.) and paste it in every second(1st,3rd,5th)?


Answer

Here is a video lesson on how to do that
http://www.file1.net/lesson.asp?id=b2wia…

It is called How to delete rows, you may need to modify the formula a little bit to achieve that

Enjoy it

VBAXLMan

Tuesday 4/7/2009 2:35:50 PM
Tue
Apr 7
2009

Filtering data in Excel 2007 based on formatting?

I have two columns I need to filter. Some have text and some have text with strike-through (a line going through the text). Does anyone know how I can filter based on the strike-through - or if that's not an option can I filter based on anything that is not formatted like the regular text so it will separate the cells with the strike-through? Thank you.


Answer

There have been a lot of requests to read format through formulas

Since it is nit supported yet, here is a user definec function to do that

Function IsStrickeThrough(CellAddress As Range) As Boolean
If CellAddress.Rows.Count = 1 And CellAddress.Columns.Count = 1 Then
IsStrickeThrough = CellAddress.Font.Strikethrough
End If
End Function

To use it...
- Press ALT + F11
- Insert > Module
- Paste this
- Now go to any cell and use it like this
=IsStrickeThrough( A1)
Assuming the cell you want to check is A1



Enjoy it



VBAXLMan

Tuesday 4/7/2009 9:01:21 AM
Tue
Apr 7
2009

Auto Generate Calendar in Excel?

Anyone know how to generate a calendar, in excel, with appointment entries from worksheet data as a sourc?. Just like creating a graph and using worksheet as data source. Want to do the same thing, but populate a calendar. Thanks


Answer

Here is a free calendar for any month any year
It also has the ability to show your appointments for a full year

http://www.file1.net/project.asp?id=k7fr…

Enjoy it

VBAXLMan

Tuesday 4/7/2009 5:43:51 AM
Tue
Apr 7
2009

Is There A Function On Microsoft Excel Which Allows Me To Highlight Duplicate Records?

What I need to do, is to paste 2 different corporate structures into one spreadhseet and then delete the duplicates. Is there a function on microsoft excel which ahighlights any duplicate records in column A of excel?

Thanks


Answer

Yes
If you are using Excel2007, the Conditional Formatting in the Home tab has duplicates, go to
Home > Conditional Formatting > Highlight cell rules > Duplicate values

If you are in Excel2003, then you may need to use a function plus the conditional formatting like this
Use the COUNTIF function in an empty column to show 1 if duplicated, 0 if not like this
=IF( COUNTIF( A:A, A1)>0,1,0)
Then apply conditional formatting to that column to color if the cell value equals 1

Enjoy it

VBAXLMan is here to fill your Excel needs

Tuesday 4/7/2009 5:22:21 AM
Mon
Apr 6
2009

How to print a chart in Excel 2007?

Help! I have an assignment due tomorrow where I had to make bar graphs in Excel 2007. The graphs are showing up perfectly in print preview, but the actual bars won't print. The X and Y axis and chart title prints only. I have a HP Officejet 6210. I have no idea what to do. Thanks!


Answer

You need to do these steps as they are the most popular reasons for problems like this

1- Clear history and cookies
2- Delete temporary internet files
Both 1 and 2 are in Internet options from Control panel
3- Delete "Temp" folder content (and not the folder), it is in your C: > documents and settings > YOURUSERNAME > Local settings > Temp, this will solve a lot of problems
4- Restart computer and try again

Do Step 3 if you are using WinXP, if you have VISTA, then step 3 would be
3- Delete "Temp" folder content (and not the folder), it is in your C: > Users > YOURUSERNAME > AppData > Temp, this will solve a lot of problems


VBAXLMan

Monday 4/6/2009 4:14:56 PM
Mon
Apr 6
2009

I am subtracting 2 cells i a excel sheet. Keep getting wrong answer in the same cell off by .01 cent format ok?

Example answer should be $10.20, answer I keep getting is $10.19. All my formatting is o k, all formulas are o k, can only get right answer when I format to 1 Decimal place instead of 2


Answer

The number that Excel shows in a cell is not all the time the same number that is calculated
for example
If you have 10.48 in a cell, and formatted that cell with 1 decimal place, then it shows as 10.5
However the number is taken into calculation is not 10.5, but it is 10.48

That may be what you are facing in your example

You can do that:
1- Go to Tools > Options (In Excel2003)
Logo > Excel options (in Excel2007) and search for the setting that says "Precision as displayed"
This will make Excel treat every number (in the current worksheet) as it is displayed, and by then you will see correct values

VBAXLMan

Monday 4/6/2009 2:33:01 PM
Mon
Apr 6
2009

Excel Chart Function?

The problem is this: To generate chart on a chart sheet u should:
1. Generate it on excel sheet 2) Select the function Move Chart 3) select Chart.
In my case, when I select Chart (3) I get a blank sheet and the chart does not appear on the selected chart
Thank u


Answer

That sounds weird
Try this...

You need to do these steps as they are the most popular reasons for problems like yours

1- Clear history and cookies
2- Delete temporary internet files
Both 1 and 2 are in Internet options from Control panel
3- Delete "Temp" folder content (and not the folder), it is in your C: > documents and settings > YOURUSERNAME > Local settings > Temp, this will solve a lot of problems
4- Restart computer and try again

Do Step 3 if you are using WinXP, if you have VISTA, then step 3 would be
3- Delete "Temp" folder content (and not the folder), it is in your C: > Users > YOURUSERNAME > AppData > Temp, this will solve a lot of problems


VBAXLMan

Monday 4/6/2009 2:14:09 PM
Mon
Apr 6
2009

Not Show Excel Formula Results?

hi

I have a formula with IF function in a cell and I dragged it to replicate the formula .

This added FALSE in all the cells how do i avoid that and have nothign displayed but the formula will still be active in that cell

thanks


Answer

Use this mask

=IF( [condition] , "", [result])

Something like this
=IF(A1-"", "", 40/A1)
This will show nothing if the cell A1 is empty

You can also try this
=IF( NOT(ISNUMBER( A1)),"",40/A1)


Enjoy it

VBAXLMan

Monday 4/6/2009 1:40:53 PM
Mon
Apr 6
2009

Is there a way to send an email to multiple email addresses written in Excel?

I have a long list of email addresses in an Excel worksheet. Is there a way to send the email without having to write or copy /paste each one of these addresses individually to the email I am sending them?


Answer

Here is a free file to do that
http://file1.net/project.asp?id=4S374y8l…

VBAXLMan

Monday 4/6/2009 1:15:41 PM
Mon
Apr 6
2009

Excel question? After the last colunm on Execel?

How do I get past the IV ( last column on excel ) I want to continue adding data.


Answer

In Excel2003 you have only 255 columns
If you would upgrade to Excel2007, then you can have up to 16384 columns
Otherwise, you may need to use the columns from another sheet to continue your database.

That is how we used to do it before Excel2007

VBAXLMan

Monday 4/6/2009 8:32:23 AM
Mon
Apr 6
2009

In Microsoft Excel, the auto filter does not show all my items that are listed.?

There are a lot of drop downs, but it randomly does not have all come up in the list. If I go to them in the spreadsheet, they are in there correctly but do not appear in the filter drop down. Does anyone know why this is? thanks


Answer

Autofilter requires no empty spaces between rows
That could be the reason
Or

Make sure that you disable / enable the Auto filter

VBAXLMan

Monday 4/6/2009 7:58:05 AM
Mon
Apr 6
2009

Moving a chart to a new page?

I am using excel 2007. After I making the chart on the excel sheet I cannot move the chart into a new chart sheet


Answer

Try cleaning up your temp...

You need to do these steps as they are the most popular reasons for problems like yours

1- Clear history and cookies
2- Delete temporary internet files
Both 1 and 2 are in Internet options from Control panel
3- Delete "Temp" folder content (and not the folder), it is in your C: > documents and settings > YOURUSERNAME > Local settings > Temp, this will solve a lot of problems
4- Restart computer and try again

Do Step 3 if you are using WinXP, if you have VISTA, then step 3 would be
3- Delete "Temp" folder content (and not the folder), it is in your C: > Users > YOURUSERNAME > AppData > Temp, this will solve a lot of problems


VBAXLMan

Monday 4/6/2009 2:45:48 AM
Mon
Apr 6
2009

Excel 2007 - Using words in the y-axis?

Hi, I'm trying to make a line chart and coordinating times with letter values where the time is on the X-axis.
i.e.
1200 is ACA
1300 is ACC
1400 is BAD
I need to put the letters/initials into the Y-Axis so instead of charting numbers to numbers, it'll do letters and on the Y-Axis I'll have AAA, AAB, AAC et cetera.
How do I do that? Thank you!


Answer

The Y-axis is where the values are drawn, meaning you can not put letters since letters can not be drawn as line

The Y-Axis has the values that representing the line itself

Does that make sense?

VBAXLMan

Monday 4/6/2009 1:56:36 AM
Mon
Apr 6
2009

Excel spreadsheet question. =IF( OR(A1="", B1=""), "", A1+B1) How do I do this for a large range of cells?


Answer

If you have that formula in cell C1
Just copy C1 to C2 and see what the formula is in C2

You will that every 1 in A1 and B1 had become 2

Try it, that is called Relative references

So... Copy sell C1, select the target range (may be C2:C500) and paste


VBAXLMan

Monday 4/6/2009 1:36:32 AM
Sun
Apr 5
2009

How can i restore deleted excel files?

In my Server excel file missing then how can i restore that file


Answer

I faced similar issue a while ago and restored all my deleted files
There is an application called FinalData, it worth every penny

Just make sure that you didn't create / copy large files into that drive that had your files

Check it out here
http://download.cnet.com/FinalData-Enter…


VBAXLMan

Sunday 4/5/2009 7:49:07 PM
Sat
Apr 4
2009

Microsoft Office Excel 2007 counting months?

example
how do i count the amount of months i have been employed
i want the worksheet to update automatical everytime i open it, no static values

Start Date =DATE(2007,8,22)
Today Date =TODAY()
Months
Days =B8-B7 = 592

whats the formula to count the months..is there a easy way
any help much appreciated...thanks


Answer

Based on how you want to show the number of months (Integers or parts) you got several options:
1- do this in any cell
=(B8-B7)/30
This will give you how many 30 days this period has

2- You also do this
=INT(( B8-B7).30)
To get how many full months

VBAXLMan

Saturday 4/4/2009 1:34:45 PM
Sat
Apr 4
2009

How to solve this problem with Excel conditional formatting?

I have a table on an Excel spreadsheet, which table contains only numbers. I want to color a "path" in this table using the following algorithm:

1) Make current the upper left cell and color it.

2) Examine the cell to the right of the current one and the cell beneath the current one. Make current the one that contains the smaller of the two numbers and color it. If the two numbers are equal, make current and color the cell beneath the current one.

3) Repeat 2) until the bottom or the right edge of the table is reached, whichever occurs first.

Can this be done using only the means of Excel's conditional formatting (i.e., no VBA) and how? That is, what formulas for conditional formatting to use?


Answer

Conditional formatting will "FORMAT" the cell and not change the cell content

To change cell content (or value) you will need a function

Based on your request, IF function would do the trick for you
Now, if your first cell is A1, then paste this into it
=IF( OR(A2="",B1=""),"", A2<B1, A1, IF( A2>B1, B1, A2))

Then fill cell A1 down

Now, Since the Conditional Formatting is to "FORMAT" cells, I don't see where you need it here

let me know

VBAXLMan

Saturday 4/4/2009 11:14:45 AM
Fri
Apr 3
2009

Working with Excel 2007?

In Excel 07 how do you put in the formula to make time add (i.e.)
1330hrs - 1500hrs = 2hrs. 30mins in one cell


Answer

What you need actually is to convert a regular number to a hr. min format
So, you can do this
=INT(A1/60)&" hrrs. "&A1-INT(A1/60)&" mins"

This will convert the number of hours in cell A1 into the format you wanted

Since all you have are numbers of hours doing the add is just like any other numbers

Or, you can do this
=INT(( A1-B1)/60)&" hrrs. "&(A1-B1)-INT(( A1-B1)/60)&" mins"

To subtract B1 from A1

VBAXLMan

Friday 4/3/2009 9:41:41 PM
Fri
Apr 3
2009

CountIF, DCount or DcountA, please help?

Hello
How are you? Hope you are fine & will receive this message at your best.

I have a problem & I need your help.
I have created a work sheet in MS excel where I have entered all our transactions during the last year processing by our different sales representatives in different cities of country.
Column D contains CITY which may b like Lahore, Karachi or Islamabad where they have sold the items, & column I contains sales representative name like Ali, Asad, Waqas etc who have sold the items.
I need to do a count of all the transations where the city is "Islamabad" AND the Sales representative is "Ali"

Please help


Answer

You have these options:

1- If you are in Excel2007, you can use COUNTIFS, which will count based on more than one condition (up to 30)
2- If you have Excel2003, you can do DCOUNTA, since the cells are having strings
3- You can also do DCOUNTA in Excel2007.
4- You can add a column having the criteria you want using simple IF, then count that column, something like this
=IF(AND( A1=T1, B1=U1),1,0)
Assuming
A1 has the city name and the list goes down to whatever
B1 has the sales man and it goes down also
T1 has the city you want to search for
U1 has the sales man you want to search for
Then do a SUM for that column to have the count
5- You still have another option, which is PivotTable, but I don't recommend using that powerful tool for something simple like this

let me know

VBAXLMan

Friday 4/3/2009 12:45:16 AM
Thu
Apr 2
2009

Shopping List software for Pocket PC Windows Mobile with POS style interface?

I would like to get a shopping list software for my smartphone, but all of the ones i've seen are either overly complicated or just simple checklists.

I would like to have a program that functions similar to a point-of-sale, when you search for an item in a database, it adds it to a "cart", you specify the quantity, and it adds the total cost as it goes. I would also like the ability to alter a line's quantity or price, as well as add a one time MISC item that is not in the database. I also have Pocket Excel; I'm not sure if there is a clever way to setup something like this with the limitations of Office Mobile.

I've searched freewarepocketpc.net and haven't found anything I like. Any ideas?


Answer

Try these websites also

http://Handango.com
http://wiki.xda-developers.com/
http://www.spbsoftwarehouse.com/

If I were you, I would do it in Pocket Excel, step by step using functions

It is true that it is limited Excel, but even though you can do a lot with it

VBAXLMan

Thursday 4/2/2009 7:58:01 PM
Thu
Apr 2
2009

How to automatically update dates in EXCEL?

I have a schedule that I want to automatically update every 7 days. For example I want 4/8/09 - 4/15/09 to automatically turn into 4/15/09 - 4/22/09. Any suggestions are greatly appreciated.


Answer

This is a weekly calendar that has the weekly schedule
And will help you see how doing these stuff are really easy and helpful

http://file1.net/project.asp?id=V45C0OnJ…

VBAXLMan

Thursday 4/2/2009 7:10:36 PM
Wed
Apr 1
2009

How do I draw a normal curve graph on Excel?

For my first set of data, I have a mean of 5.409090909 and a standard deviation of 1.370437585. For the second set, the mean is 4.590909091 and the standard deviation is 1.969289002. How can I draw a normal distribution curve? I have 22 values for each set. Any help would be appreciated.
Regards


Answer

If I understood your question, do this:
Add new series (one for each mean and STDEV) with the same value for all points

And graph it


If this is not what you wanted, let me know

VBAXLMan

Wednesday 4/1/2009 10:39:02 AM

Tags

History