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

154 Q & A posts found in year 2009

Tue
Jun 9
2009

In excel, is there a formula to select the last cell of worksheet to calculate values in the next worksheet?

I currently used this formula:
='NSN Motorsports'!A12+1
In this example my previous worksheet is "NSN Motorsports" A12 is the last cell in that worksheet. I used this formula to create the next number on the new worksheet. This requires me to go back to the previous worksheet and click the last cell. My workbook will have many worksheets, so doing it this way is very time consuming. Any ideas for formulas that will not require me to leave my current worksheet to get a value from the previous worksheet?


Answer

You can create a pattern for your sheet names
After that, use some formulas to determine "the previous" sheet to the one you are in...
1- In an empty cell (make it B1), paste this
=MID(CELL( "filename",A1),SEARCH( "]",CELL( "filename",A1))+1,500)
This will give you the name of that sheet, so copying that cell into another sheet will give the name of that sheet, etc
2- Now, If your 'Pattern" is NSN1, NSN2, NSN3, then paste this in B2
="NSN"&VALUE(Mid(B1,4,1))-1
This will give you the name of the "Previous" sheet to the one that you are in
So if you are in NSN4, B2 will give you NSN3, and so on
3- Finally, to get the last value of the "Previous" sheet, do this
=MAX( INDIRECT( B2&"!A:A"))+1

Good luck with that, this will be totally automated method, justcopy and paste the three cells into the new sheet

If you need more info or think that you can customize it more, please contact me here

After all, it is the VBAXLMan here

Tuesday 6/9/2009 11:08:54 AM
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
Tue
Mar 31
2009

I want typing in hindi on ms word and Excel?


Answer

To use any language other than English in Windows
You will need to install it first

Go to
Control Panel > Regional and Language options
And make sure you see your language in that list
and also make sure that you have the checkbox checked for middle east and far east countries

or something like that

VBAXLMan

Tuesday 3/31/2009 7:10:14 PM
Tue
Mar 31
2009

How do you turn macros on in microsoft excel 2007?


Answer

First, you will need to save your file as Excel macro-enabled worksheet with XLSM as extension rather the default XLSX.

If you are doing ans XLS 2003 file type, ignore that

Then once you open the macro file, Excel will show you a new bar above the Formula bar with "Security warning"
like this screen
http://www.file1.net/img.asp?prj=-8-_-2_…

Click on "Options" and enable the macro content

OR
Show the "Developer" tab, here is a lesson on how to show it
http://www.file1.net/lesson.asp?id=6VM8P…

Then in the Developer tab, click on "Macro Security" in the "Code" section


VBAXLMan

Tuesday 3/31/2009 6:18:07 PM
Tue
Mar 31
2009

How can I create a Macro with Excel 2000 and Visual Basic.NET?

When I start Excel 2000 and click tools > marcro > visual basic editor, it starts Visual Basic 6. I also have Visual Basic.NET and would like to program Macros in a more up to date version of VB. Is there a way to do this?


Answer

No, Cozmosis you are wrong

Sorry Nathan
All Excel versions after Excel97, (Including Excel2007) are having VBA version that is based on VB6
Excel97 has the VB5 version of VBA

You can not program VBA code in Excel using other than the built-in VBA

Which is for now all in VB6

You would may be wait until they might put .NET in the coming version of Office

VBAXLMan

Tuesday 3/31/2009 3:42:01 PM
Thu
Feb 12
2009

How to create a function in Excel...a second question?

Thank you to the person that helped me yesterday...a huge help! The isanswer and match functions worked great!

I now need to write a formula to do the following: I need to find if the value in cell C1/Sheet 1 is in column D/Sheet 2. If true, return the value (text) in Column E/Sheet 2 that is in the same row as the value found in Column D. If false, return "null" or "false".

I tried the same isanswer and match formula as it seemed it was close to the solution but it didn't work. Can you help me again? :)


Answer

Paste this in cell E1 Sheet2
=IF( ISNA(MATCH( Sheet1!C1, Sheet2!D:D,0)), "","Text")

And fill it down

----- Edit ----
Yes, in this case, use this
=IF( ISNA(VLOOKUP( Sheet1C1, Sheet2!D:E,2,FALSE )),"Not found",VLOOKUP( Sheet1C1, Sheet2!D:E,2,FALSE))

This whould work

VBAXLMan

Thursday 2/12/2009 3:18:14 PM
Wed
Feb 4
2009

In excel, is it possible to find a string or data in a set of pdf files?

I Need to extract information automatically from a set of pdf,, lets say I have 3 pdf files named. f-1.pdf, f-2.pdf, f-3.pdf and I need to extract a value inside the f-2.pdf file. that string will be something like "force A =100lb"
I need to extract that 100.

any clue where I can start to look?


Answer

If you don't have Adobe Acrobat (and not Acrobat Reader)? then I guess the only solution is copy and paste

May be any pfd creator application can help you automate that

VBAXLMan

Wednesday 2/4/2009 6:33:25 PM
Wed
Feb 4
2009

How do i get Excel to automatically extend the cell to fit the text typed?


Answer

The other two solutions are "AFTER" you hit enter or select another cell

To answer your question, there is no way in Excel to extend your column width "WHILE YOU TYPE"

forget it



VBAXLMan

Wednesday 2/4/2009 4:37:11 PM
Wed
Feb 4
2009

Excel 2003 - VB - Accessing abilities between modules for public functions?

I have one module with:
"Public Function Code128B(inpara As String) As String"
and then the function (i'm pretty sure it's copyrighted etc; it said Shareware on a few sites i was at and it's for barcodes)

I have another module with:
----------
...
Dim foot As String
foot = ActiveWorkbook.Name + " " + ActiveSheet.Name
ActiveSheet.PageSetup.CenterFooter = "&""MRV Code128bMA,Regular""&14 " & Code128B(foot)
...
----------

And the error is returning is that it is an "Ambiguous Code Name"

Is there something fishy going between modules with public functions? I'd think that a public function means it's always available to every module but I'm not sure.

If this is not enough data to solve the problem say so and I'll just let the whole thing drop (it's not my code or job to fix it :P )


Answer

You are right
"Public" will set this function public to every other module inside that workbook
And I quote "That Workbook"
If you are running this from another workbook, you need to call the function in another way
Workbook1.xls!Code128B(Foot)

If you are using the same workbook, then you may need to check two stuff:
1- You have a reference to the library that is used by that function
Some function requires you to add a reference (Toos > References) to a specified library to make it rub
2- You don't have the "Option Explicit" in any of your modules

If you still face the problem again, mail me some sample files, I can do it for you (May be for free)

VBAXLMan is here to feed your Excel needs

Wednesday 2/4/2009 9:05:51 AM
Wed
Jan 21
2009

I have no space in my local disk? Help?

I was trying to download a program for my laptop and i hadnt enough space to download it and save it to my local disk.

But in my TEMP_PART01(D:) 76 gb of unused memory.

How do i use this memory?


Answer

You can move the download folder to D:
What is the application you are trying to download with?

Go to ite settings and change the download folder into any folder in D:

Also you can select the folder you want to install that application in into D:

So you can make use of it

VBAXLMan

Wednesday 1/21/2009 8:09:23 PM
Wed
Jan 21
2009

Searching across sheets in Excel?

How can I do a search for several items across sheets in Excel, whether using vlookup or any other function


Answer

This is the lesson for that
http://www.file1.net/lesson.asp?id=28

Enjoy it

VBAXLMan is here to feed your Excel needs

Wednesday 1/21/2009 6:54:45 PM
Wed
Jan 21
2009

Windows Dvd Maker & Movie maker continuously shutting down?

Opening the two associated programs is fine, its when i click on something or try to add something the program freezes and the message:
Windows Movie Maker has stopped working.
It does the same for DVD Maker.
Ive checked that all my programs have been updated but yet this problem still occurs.
I have plenty of ram and all that so its nothing to do with the hardware. Im on windows vista.

Not sure if this associates with the problem but when i try to insert my USB into the laptop a message appears in the bottom right (a balloon) saying USB has malfunctioned.
Any ideas on how to fix this?
Thanks :)


Answer

You said it might not related with hardware, but I am afraid it is
First you need to do the following:
- Delete Empty files from (C:\Users\ [YOURUSERNAME\ \ AppData \ Temp) Windows put some files for temporary and that could make that problem
- Did you changed the Ram recently? it sounds like a ram conflection to me
- If not, then make sure you are having a good VGA card (Display adapter) that can hundle the files you are working with.
- Check also if you are facing this in some files or not
- Do you case this also in any other heavy graphic applications (Like Premier or PhotoShop)?

Good luck with that

VBAXLMan

Wednesday 1/21/2009 5:30:29 PM
Wed
Jan 21
2009

What are the available Note-Taking Apps for Windows?

I'm looking for the best note taking app that runs on windows and saves locally (at least has that ability). Does anyone have any ideas?


Answer

You can try any of those:
1- Install Yahoo! Widgets and get the notepad widget, it is all free
2- There is an application comes with Microsoft OFfice 2007 that does that too
3- Or check free ones here
http://www.download.com/1770-20_4-0.html…

VBAXLMan is back

Wednesday 1/21/2009 5:26:35 PM
Wed
Jan 21
2009

Urgent! MS Excel Macro help needed!?

Hello

For worksheet A, I need to loop through the values in column A, and if AX="cow", then I need to copy cells CX, FX and HX into worksheet B.

Any tips or code on how I can accomplish this?

Thank you very much.


Answer

Sure, Advanced Filter is your solution
- While you are in Worksheet B, type the name of the AX field in Worksheet A and type "cow" below it
- Go to Data > Filter > Advanced Filter
- Check the option "Copy to another location"
- And enter the three values, knowing that, the Criteria range is your two cells in Worksheet B

Enjoy it, it is a powerful feature

VBAXLMan is back

Wednesday 1/21/2009 3:21:07 PM
Wed
Jan 21
2009

In the new excel (2007) when you type in a cell, it comes up like normal text (so you can highlight it etc)...?

but I want it like the old version, so that you cant highlight it etc. apart from in the formula bar, does anyone know how to do that thanks


Answer

Go to Excel Options by clicking the Office logo (at the top left) and press "Excel Options"

Now, in Advanced > Edit Options
Make sure the checkbox "Allow edit directly in cell" is unchecked

Enjoy my profile, VBAXLMan is back

Wednesday 1/21/2009 2:49:20 PM
Wed
Jan 21
2009

Excel Formula - I need help!?

Hi,

I'm not sure if I'm supposed to use the If formula for this - and if so, how. Here's what I want to do:

Something has X views, 10,000 for this example. I want:

1) The first 100 views to be worth 3 points each (=300 points)
2) The second 100 views to be worth 2 points each (=200 points)
3) The third 100 views to be worth 1 point each (=100 points)
4) Any view thereafter to be worth 0.75 of a point (in this case, 9,700 views times 0.75 = 7,275) points.

How would I create a formula like that (without having to actually divide up the views into four groups and do the separate calculations?)

Thanks!


Answer

So these views are in column B I assume starting from B1
Then in C1, paste this
=CHOOSE( INT( ROW()/100)+1,3,2,1,0.75)

And copy it down to the end of your list

This column will give you the points you have to multiply by, just add another column multiplying the value you want time this, or change the C1 into
=CHOOSE( INT( ROW()/100)+1,3,2,1,0.75)*B1

VBAXLMan is back

Wednesday 1/21/2009 2:42:33 PM
Wed
Jan 21
2009

How do you make a password field in excel so when you type in one of the cells, it comes up as asterisks *****?


Answer

Excel cell does not allow you to do this, you need to do an alternative ways, you got two:
1- You can use the TextBox from Forms toolbar (If you have Excel2003) or from Developer tab (If you have Excel2007)
2- Add a VBA form and add the text box to it.

Wether you do 1 or 2 you need to change the password property of it to make it show you * when you type in it

Again Excel cells can not allow you to do that, forget it

If you need help in doing this, or you don't know how to add a VBA form or Textbox to your Excel sheet, mail me here

VBAXLMan

Wednesday 1/21/2009 2:21:07 PM
Wed
Jan 21
2009

How do you convert a sound file to play on a power point?

I'm making a power point and i want a song to play through out the presentation. I tried to apply the song, and it said it needs to be converted to .wav ? Or something like that? Can anyone tell me how to do this PLEASE!!! Thank you in advance :)


Answer

The best one I tried is this one
http://www.download.com/WM-Converter/300…

It is free, try it

VBAXLMan

Wednesday 1/21/2009 12:08:47 PM
Wed
Jan 21
2009

Microsoft Paint - custom sizing printing?

i have created some pictures on paint that i want to print at custom sizes (they are cd covers: back and front) i desperately need to know if this is possible. many thanks,
callum


Answer

Try these in sequence:
1- Use Word, to insert that picture and print using sizes by right click on the picture > Properties
2- Print it using "Picture Viewer", if you are using XP
3- If you don't have word, use WordPad

VBAXLMan

Wednesday 1/21/2009 9:32:08 AM
Wed
Jan 21
2009

Excel 2007 document protection & locked cells?

Hi,

I've created an excel document that numerous users will be editing, however I need for only certain users to be able to edit certain cells.

I currently am using the track changes method, however it appears that there are ways around this.

Does anybody know if there is a formula or a function I can use to only allow a user to edit a certain cell if the username = joe.bloggs for example? Excel can clearly look up the username as it does so in the "track changes" but its how do i use this information to protect my cells?

Many Thanks,
Mark


Answer

The Standard way is to give the password to modify the file (Save As > Tools > General Options) to those people only allowing them to edit these cells, while others can not

The other way is to lock these cells with password (Review > Protect Sheet) and give those the password

In addition to that you will always have the macro way, but that requires more info from you

Enjoy my profile, I am the VBAXLMan here

Wednesday 1/21/2009 6:55:16 AM
Wed
Jan 21
2009

Multiple pivot tables controlled by a single set of page fields to automatically update all tables?

Hi,

Is it possible in Excel to update multiple pivot tables, all using the same page fields, by changing the page fields just once instead of having to change them for each table?

Thanks


Answer

Sure
When you build the pivot tables, make them reading the values from that source table

Once you update your fields, you may need to refresh each pivot table, or use the macro to make updation for all you pivot tables like this

ActiveSheet.PivotTables( "PivotTable1").PivotCache.Refresh

All you need to add is a loop through all your sheets with pivot tables and refresh each one

For i=1 to ThisWorkbook.Worksheets.Count
Worksheets( i).PivotTables( "PivotTable1").PivotCache.Refresh
Next i

Also you may need to make sure that each Pivot is named "PivotTable1" in each sheet

VBAXLMan is back to feed your Excel needs

Wednesday 1/21/2009 5:28:24 AM
Wed
Jan 21
2009

How do I merge seperate folders into a new one in excel?

each folder has a part # , descrptn , Qty , Unit price , total
fldr 1 bin 1 = 101.00
bin 2 =23.21
bin 3 = 31.01
fldr 2 bin 1 = 21.03
bin 2 = 333.31
bin 3 = 21.13
fldr 3 bin 1 = 31.69
bin 2 = 61.23
bin 3 = 19.61
bin 4 = 21.33
what is the best way if possible to put the three into one with a grand total? Trying to track inventory Thanks for any ideas


Answer

Do a forth table (or folder) and put bin 1, 2, 3 and 4
then use the SUMIF function to achieve the total of bin 1 from the other three tables (folders)
Paste this in B2 in Sheet2
=SUMIF( Sheet1!A:A, A2, Sheet1!B:B)
Assuming your first table (folder) is in sheet1 having the bin text in column A and the total in column B
Also you have Bin 1 in Sheet2 A2

do the same for the other 3 tables (folders)

VBAXLMan is back

Wednesday 1/21/2009 3:24:58 AM
Tue
Jan 20
2009

Get answers from millions of real people.?

In excel im having data in 3 columns like Column ABC in 1000 rows each column
in column A data is greater than or equal to B in B colum data is greater than or equal to C. how to get the result what is the formula


Answer

In column D paste this
=IF( AND( A1>B1, B1>C1), "Ok", "Not Ok")
and fill it down

That is what I understood from your question, you didn't gave enough info

VBAXLMan is here again to feed your Excel needs

Tuesday 1/20/2009 11:57:27 PM
Tue
Jan 20
2009

What formula do i use in excel if i want a certain column to say 0 if another column equals 0?


Answer

Paste this in B1
=IF( A1=0,0, "Not zero")
Copy and paste this down to any range of cells

this will give column B 0 if column A is zero

VBAXLMan

Tuesday 1/20/2009 8:45:41 PM
Tue
Jan 20
2009

Why does a formula remain visible in a cell on an excel worksheet?

I have many formulas in my excel spreadsheet and when I want to extend the search a few more rows, and change the number in the formula bar, the formula appears and then remains visible in the cell. Please email me if you have a solution on diddlesjnr@adam.com.au


Answer

Perhaps you have the format for that cell as text
Excel does that if you:
1- Have no equal "=" sign as the FIRST character
2- Format that cell as text

VBAXLMan is back

Tuesday 1/20/2009 8:09:30 PM
Tue
Jan 20
2009

Debug error 3001 on VBA when I try a sequential search of the database.?

I am trying to subtract the quantity ordered by a customer by the Number In Stock of a particular product. To do this, I needed a sequential search, and I used a code which I thought of. However, it brings up a "Run-time error '3001': Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another."
The Debug error is this:
rst.Find "ProductID = " & Me!ProductID, 0, adSearchForward, 1
Here is the code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
Dim con As New ADODB.Connection
Dim rst As ADODB.RecordSet

Set con = CurrentProject.Connection
Set rst = New ADODB.RecordSet

rst.Open "tblProduct", con, adOpenKeyset, adLockOptimistic

rst.Find "ProductID = " & Me!ProductID, 0, adSearchForward, 1
rst("NumberInStock") = rst("NumberInStock") - Me!QuantityOrdered
rst.Update
rst.Close

Set rst = Nothing
Set con = Nothing

End If
End Sub


Answer

- Is the name of field you want to search in named "ProductID" too?
- Did you gave all arguments of "Find"?
- What about the result of Find, shouldn't you assign the result to a variable?
- Are ProductID columns from both sides in the same type?

VBAXLMan

Tuesday 1/20/2009 2:05:12 PM
Tue
Jan 20
2009

Can someone help me with excel?

I know i should figure these questions out on my own, but i would like some help.
my questions are;
true/false
1. if you sort a last name column in ascending order, adams would come before williams
2. to fill in a series, a pattern must be established in the initial selection of cells

questions
1.What feature enables you to quickly fill in the days of the week or the months in a year
2. What menu is the sort command in
3. Does autoformat include borders, shading & data formatting
4. What must you indicate to sort data in excel

fill in the blank
1. to sort a worksheet by multiple criteria you must open the ______ dialog box
2. the ______ that indicate the boundaries of each cell in a spreadsheet do not appear on the printed worksheet
3. A(n) ______ is text that prints in the top margin of every worksheet page
4. The ________ tab in the format cells dialog box allows you to format numeric data with commas separating thousands
5. To give a worksheet a new name, you can right click the _____ and then select rename from the shortcut menu.



thankyouuuuu ! (:
17


Answer

Part one:
1- true
2- false

Part Two:
1- Edit > Fill > Series
2- Data
3- Yes
4- Sort by column

Part Three:
1- Sort
2- Grid lines
3- Header
4- Number
5- Sheet tab

VBAXLMan

Tuesday 1/20/2009 1:48:52 PM
Tue
Jan 20
2009

When I want to print ,it shows the messege"WINSPOOL.DLL" not found.What should I do?I am using XP5.8?

I have installed urdu software name 'inpage'.the excel and word files are easily printed but the files from inpage urdu are not printed.


Answer

It looks like your program can not recognize the XP printer style and still using the old fashion way

The best way to do it is to print the file you have into a program printer (Office has one called "Office Document Image Writer" or "Office XPS Writer")
Or you can install a free PDF printer here (Http://www.prismpdf.com) and print to that printer
This will generate a file that you can easily print using its application

So you are using a third application to print yours since you can not print using your own application

good luck

VBAXLMan

Tuesday 1/20/2009 7:48:03 AM
Mon
Jan 19
2009

How do you print comments on microsoft excel 2007?

i can't make the comments print on microsoft excel 07


Answer

Go to Page Setup by:
- Click the "Page Layout" tab
- Click the small arrow next to Page Setup section (below the "Print Titles" command)
- Then in "Sheet" tab select "As displayed on sheet" for "Comments"

VBAXLMan is back

Monday 1/19/2009 11:31:33 PM
Mon
Jan 19
2009

Please help me clean up my laptop. 10 pts!!! Plz help!!?

I have an HP NC600 ... Its a good little laptop... a few years old.
I'm not sure the memory but it could use with an expand...

My problem? It runs SO SLOW!!!!
I have Ad Watch and Spy Bot on my computer but whenever i try to run the scans it takes hours...

I don't have alot of extra things added to my computer. I have firefox, interent explorer, MSN and your usual Word, Excel etc documents...

Please what can i do to speed up my computer... I know buying memory would help but im a student in university.. dont have money right now :(


Answer

Hmmmm
You need to do these steps as they are the most popular reasons for slowing down
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 1/19/2009 4:53:33 PM
Mon
Jan 19
2009

Excel % formula non-blanks?

I have an xls where I want to enter a formula that counts the # of non-blank cells & divides by # to get a % result. i.e 56 non-blank cells/735=8%. Can anyone show me how to do this?


Answer

Sure, COUNTA will do it for you
like this

=COUNTA( A:A)/735

Format that cell as Percentage to get the %

VBAXLMan is back

Monday 1/19/2009 2:57:55 PM
Mon
Jan 19
2009

Display date and time together in a cell when using a DatePicker?

Hi, I added a date picker add-in from the following url. into excel
http://officeblogs.net/excel/samradDatePicker.xla

Ensured that i make a column accept dates. and now when i click on that cell i do get an icon to enter the date.
My question is the output comes as only date "01-mar-09" if i want the time also to be there along with date(in the same cell), what do i have to do.
I tried changing format in DATE in cell formating but its not working.
anyway i can get this done
Thanks


Answer

It is a "DatePicker" and not a timepicker
That was 1.
2. what time do you want to use if the user didn't select one?
3. You can do the following:
Do a validation in the next cell with the expected time (as hours or halves) then allow the user to select from it

The date usually uses the integer part on the number to refer to the date, time uses the decimal part

So if your date is Jan-19-2009 (means=39832) then the time of that would be 12:00 AM

So when you select a date in DatePicker, you are actually selecting the hour 12:00AM in the morning of that date

To add a time, you will need to do a validation or do some other macro code

VBAXLMan is back to feed your Excel needs

Monday 1/19/2009 11:37:50 AM
Mon
Jan 19
2009

Nesting IF and SUMIF?

I'm trying to create a summary document of invoicing for my company and was wondering if Excel has any functionality that allows me to look through a table of vendor invoices that have been paid, identify all invoices that have been coded to a particular project, identify all instances of one particular vendor within the list of invoices for a specific project, and then sum all invoice values that are associated with the instances I identified of the particular vendor.

So, for example, I want to search a table for all occurences of project code "1234", then search the list of occurences of "1234" for vendor "XYZ", then sum the invoice values for all occurrences of vendor "XYZ" within the list of occurences of "1234".


Answer

If you have Excel2007, then use SUMIFS like this
=SUMIFS( D:D, A:A, "1234", B:B, "XYZ")

If you have Excel2003, then use DSUM like this
Put the title of project code column in cell F1, put 1234 in F2
Put the title of vendor column in G1, put XYZ in G2
Paste this in H1
=DSUM( A1:E500, "Invoice total", F1:G2)

Assuming, you have the list in A1:E500 with column headers in A1:E1
And you have the column you want to sum named "invoice total"

Do the changes as your table and enjoy it

VBAXLMan is back to feed you Excel needs

Monday 1/19/2009 9:04:24 AM
Mon
Jan 19
2009

Can you help me with my Excel question?

I have people send me a lot of reports that were created through AIX access. I usually take those reports and dump it into Excel, where I do some formatting afterward. A problem that I run across every now and then, is when I hit a multi-valued field and the information is displayed vertically... like so (I will put in which cell it would fall into next to the value)

A1 - [patient 1] B1 - [perscription number 1]
B2 - [perscription number 2]
B3 - [perscription number 3]
A4 - [patient 2] B4 - [perscription number 1]
B5 - [perscription number 2]
A6 - [patient 3] B6 - [perscription number 1]

and so on, and so on...

Is there any way to get those values to move to be displayed on line like so

A1 - [patient 1] B1 - [perscription number 1] C1 - [perscription number 2] D1 - [perscription number 3]
A2 - [patient 2] B2 - [perscription number 1] C2 - [perscription number 2]
A3 - [patient 3] B3 - [perscription number 1]



I appreciate any assistance you can give, and I apologize if I've made my own question sound more complex than it has to..


Answer

Yes, you have two solutions:
1- Using macros, which sounds to me in your situation faster and easier
2- Using formulas, but this will be kind of duplicated formula (or set of formulas)

Now, I want you to answer some questions:
1- How many is the maximum number of drugs per patient you can expect?
2- The formula below, can work in cells C1, D1 and E1 and below, but the problem will be deleting the empty rows, if you answer Q1, then I think I can do that also
Now paste this in C1
=IF( $A1="","",IF( OFFSET( $A1,COLUMN( )-2,0)="",$B2,""))
And this in D1
=IF( $A1="","",IF( OFFSET( $A1,COLUMN( )-2,0)="",$B3,""))
And this in E1
=IF( $A1="","",IF( OFFSET( $A1,COLUMN( )-2,0)="",$B4,""))

then copy those three down

I told you it is a complicated, now these three will transpose your rows into columns
You will still need to delete the empty row, I could delete them using formulas, but I needed more details as I told you

Enjoy my profile, VBAXLMan is back to feed your Excel needs

Monday 1/19/2009 8:44:10 AM
Mon
Jan 19
2009

I want column to change by not the row in excel or google doc?

My formula looks like this

='Activity sheet'!C$1
='Activity sheet'!C$1
='Activity sheet'!C$1

I want it to be

='Activity sheet'!C$1
='Activity sheet'!D$1
='Activity sheet'!E$1
='Activity sheet'!F$1

etc.

Help will be more appreciated.


Answer

Sure, do it like this

Now this will depend on where you are putting it:
So Assuming you want to put this in cell A1 and down, then paste this

=OFFSET( $C$1, 0, ROW()-1)

And fill it down

Yes, I know, I am the VBAXLMan here

Monday 1/19/2009 6:41:27 AM
Mon
Jan 19
2009

How can i sum or count Yes or no in a column and categorized them in Product group im just one formula?

Excel Formula


Answer

SUMIF will do only one criteria, so you will need SUMIFS, but this will work in Excel2007 only
If you have Excel2003, then you can do DSUM

DSUM is criteria based sum, but you will need to build range of cells as your criteria range

You didn't give that much info, otherwise I could do it for you

Mail me here with more info or some sample file, and I can do it for you for free

VBAXLMan is back

Monday 1/19/2009 4:34:55 AM
Mon
Jan 19
2009

How do u get excel to calculate a column of numbers that is the average of successive numbers inthe leftcolumn?

I want (B1+B2)/2 and then (B2+B3)/2 below and so on in the column. Oh ya and please explain clearly and gramattically and speak english!


Answer

Paste this in cell C2
=B1+B2/2
Then copy C2 into C3, C4, C5 and so on

Doing this will give you the average of B2 and B3 in cell C3
Average of B3 and B4 in C4 and so on

VBAXLMan is here to feed your Excel needs

Monday 1/19/2009 1:28:11 AM
Sun
Jan 18
2009

Why can i not open some files?

why is it i keep getting message insert the microsoft office xp proffesional with front page disc, telling me this is on a removable disc.i am trying to open a microsoft excel file which i have done before with no problem why is it now not letting me open it?


Answer

That depends on
1- Your version of Excel
2- Did you installed the Office in full?
3- What are the file types you are opening? I mean extensions?

The best solution could be re-installing Office again, but in full this time

good luck

VBAXLMan

Sunday 1/18/2009 12:04:58 PM
Sun
Jan 18
2009

Excel - how can I have a cell auto-completed with a description of another cell filter?

Hi
I have a spreadsheet with lots of orders on it that I want to filter by name, I have set up the filters fine and that is working well. However, I want another cell in the same sheet to show what I have name I have chosen to filter.
Is this possible? If so, how.

Any help appreciated.

MGB

Any help appreciated.


Answer

The AutoFilter does not do that
Or you can not do this in AutoFilter
However, you can do this if you are using Advanced filter

Advanced Filter allows you to filter range based on a criteria in another range
Meaning you can see the filter criteria when you filter the table

But, you will need to use the criteria in cells that are not affected by the filter, I mean you will need to shift the table down starting from A10 for example and put your criteria range in A3:C7 for example
This way you can see the filter settings and the filtered range

VBAXLMan is back to feed your Excel needs

Sunday 1/18/2009 7:31:54 AM
Sun
Jan 18
2009

What is the simplest way to extract text pattern in Excel spreadsheet?

I have a sheet of a few columns of URLs. I need to extract a text pattern (I.E. extracting an URL parameter) and then replace it with something else and place it into another link structure. What's the simplest way to do that in Microsoft Excel spreadsheet? Does Excel provide regular express?


Answer

Yes, sure
I do this all the time
Now, the link you have may look like this
http://www.file1.net/project.asp?id=89

If this link in cell A1, then paste this in B1
=LEFT(A1, SEARCH("?",A1)-1)

This will bring you the first part until the "?"

Then in another cell, say C1 type 5

Then in D1 paste this
=HYPERLINK( B1&"id="&C1, B1)

Now this link will have this result
http://www.file1.net/project.asp?id=5

So doing 6 in C2, 7 in C3, etc
Copy and paste D1 down to generate multiple hyperlinks to different locations based on your criteria

I did this as example, because you didn't give any details

try applying your criteria and see

VBAXLMan is here to feed your Excel needs in case you want more help

Sunday 1/18/2009 4:34:54 AM
Sun
Jan 18
2009

What formula would I use to determine profit or loss in Excel.?


Answer

Having numbers as follows:
A1 has the total amount sold
A2 has the total cost

In A3 paste this
=IF( A2>A1, "Loss:"&ABS( A2-A1), "Profit:"&A2-A1)

VBAXLMan

Sunday 1/18/2009 2:15:06 AM
Sat
Jan 17
2009

Students often tell me that they use excel to figure ?

Students often tell me that they use excel to figure out sales goals for a month and keep track of how much they need per day to make the amount required for their monthly goal. You can use Excel to develop a spreadsheet for this.

How might you do this?


Answer

It is called "Goal Seek", you can find it in Data menu if you are using Excel2003

This Goal seek requires that you have all the cells for data and formulas already set before using

Try it, it is interesting feature

VBAXLMan

Saturday 1/17/2009 6:44:38 PM
Sat
Jan 17
2009

How do I make a bar graph using Microsoft Excel 2003?


Answer

Type in your data into cells as columns
Then go to
Insert > Chart
Select Bar from the chart type and click finish

This is the fast solution

VBAXLMan

Saturday 1/17/2009 6:29:37 PM
Sat
Jan 17
2009

How can I copy my favorites (bookmarks) from my Pc to nokia e71 ?

I made the favorits as a htm file & excel , but dives couldn't open it !!


Answer

Try do it as CSV or simple text files, most applications can read those types

otherwise you need to know what format your phone accepts

VBAXLMan

Saturday 1/17/2009 2:49:42 PM
Sat
Jan 17
2009

I want to fix a pic in MS Excel 2007 cell and to use in an another sheet with Vlook up formula?

For each cell of employee I want to fix a pic and to use in forms with Vlookup formula


Answer

Excel functions are working with text and numbers
You can not do this with functions, instead you will need macros to do that

I would suggest to do a folder with employee pictures named with employee ids
Then insert a picturebox in that Excel file and do some macros to make it read the selected employee picture.

Something like this:
Sub ReadPic()
EmployeeID =Range("A1").value
PictureBox1.picture=LoadPicture( "D:\Pix\" & EmployeeID & ".jpg")
End Sub

This will read the picture in the picturebox assuming cell A1 had the employee id

I will be glad to help if you just send me the file you are talking about and more details to build the macro for you

VBAXLMan

Saturday 1/17/2009 10:34:54 AM
Fri
Jan 16
2009

How can I sort a ROW alphabetically in Excel 2000?

I know very well how to sort a COLUMN in Excel 2000, but I have a ROW of names at the top of a spreadsheet that I'd like to sort alphabetically. How can I do this?


Answer

In Data > Sort
Select Options
And choose the option that says, Sort Left to Right

That is after you made the proper selection


VBAXLMan is back

Friday 1/16/2009 7:25:29 PM
Fri
Jan 16
2009

How can i divide a screen in two sections (see 2 pages simultaneously)?

for viewing two applications on the same glance for example excel spreadsheet and my yahoo mail


Answer

Right click on the Task bar and select Tile

Or Arrange depending on the OS you have and the version

VBAXLMan is back

Friday 1/16/2009 6:52:19 PM
Fri
Jan 16
2009

Excel - How to get the frequency of the words in a para?

The para contains 1000 words. I need to find out the the top 50 words appearing most often in the para together with the number of appearances only using excel commands - can't use VBA or any other s/w!

any help ??


Answer

The only way to do it is to convert that para into cells (For temporary)

Now, if you have that cell in A1, paste this in B1
=LEFT( A1,SEARCH( " ",A1)-1)
And this in C1
=LEN( B1)
And this in D1
=COUNTIF( B:B,B1)
And this in B2
=MID( $A$1,SUM( $C$1:C1)+2,SEARCH( " ",$A$1,SUM( C1:C1)))
and copy C1 to C2, D1 to D2

Now copy cells B2, C2 and D2 down until you find all the text you have in that para in the cells of column B

Now, the D column will give you the number of any corresponding text found in cell A1

The interesting thing, is that when you change cell A1, all of these will be changed automatically


Enjoy it, Yes, I know I am good at this, I am the VBAXLMan here

Friday 1/16/2009 6:13:55 PM
Fri
Jan 16
2009

Getting an equation from points on excel 2007 ?!?

Alright I have a set of data points that I can graph but I don't know how to label the axis or to get it to derive an equation from a line. When I try to type in my AXIS label its says that the formula I tried has an error all I'm trying to do is label AXIS not put in a formula? what do I do?


Answer

When you click on the chart Excel2007 will show you new 3 tabs at the top right
Go to "Layout"
Then click "Axis Titles"

And do your titles here

VBAXLMan is back

Friday 1/16/2009 5:20:08 PM
Fri
Jan 16
2009

Excel - is it possible to save a picture seperately from an excel spreadsheet - if so, how?


Answer

The only way to do so is:
1- Copy that picture
2- Open MS Paint, (Or any other photo editing app)
3- Paste it there
4- Save the file from Paint


I tried several ways before to do that automatically, but no luck

VBAXLMan is back

Friday 1/16/2009 3:30:31 PM
Fri
Jan 16
2009

Excel e-mails me via a macro. Can Excel e-mail me as per my macro even when the workbook is closed?

I just added a macro to Excel that will e-mail me when a cell value changes from '1' to '0' from live data. Will it e-mail me even when the workbook is closed or does the spreadsheet need to be open in order for the macro to take effect?


Answer

If the workbook is not opened, the value of cell will not be changed
Meaning the email will not be send

Yes, the Excel file have to be opened in order to execute the macro and email to be send

VBAXLMan

Friday 1/16/2009 1:08:00 PM
Fri
Jan 16
2009

2 questions regarding memory stick and excel file?

1.I put memory stick in pc and work on some files. Now, when I want to save some typing I did on a file in a folder on the memory stick and go to save, pc box says" Cannot open the ..... 3 reasons. One reason is a file with that name is already open." I have to close file without saving revision.

2. I exit computer and then later turn on and click on drive with memory stick plugged in and it cannot access the files I previously saw when I double clicked on K-file. Blank screen where previously the screen showed the files I was typing in.


Answer

Sounds like a virus to me

Make sure you have a good antivirus

Besides that
The best way to edit files from USB drive, is to copy it to a fixed drive

Sometimes Windows disconnect the USB drive and that will delete the connection between the application and the file opened

VBAXLMan

Friday 1/16/2009 11:31:51 AM
Fri
Jan 16
2009

I want to convert 200 EXCEL files to TEXT files. Is there an easy way to do this?

I know I can do each one separately by opening it and saving it with a different format. But it would take a while to do this 300 times. There seems to be programs that can do this that cost money. Is there a free one. Any ideas? Thanks!


Answer

The free option is a macro inside Excel file, something like this

Sub ConvertAll()
ThisP= Thisworkbook.Path & "\"
For I=1 to 300
Workbooks.Open ThisP & "File" & i & ".xls"
ActiveWorkbook.SaveAs ThisP & "File" & i & ".txt" , xlUnicodeText
Next I
End Sub

Now this macro assumes:
1- You are having these 300 files in the same folder of the one having this macro
2- Your files are named as File1.xls, File2.xls, etc

You can change that macro as you like, or email me here to change it for you

VBAXLMan is back to feed your Excel needs

Friday 1/16/2009 10:07:55 AM
Fri
Jan 16
2009

Does Excel 2007 return answers with more than 15 significant figures?

Can you do me a favour? If you are running Office 2007, can you go to Excel and type in any digit about twenty times?
My Excel 2002 loses interest after 15 significant figures and returns 0 after that. Thanks.


Answer

I did
1234567890 1234567890
And Excel converts it into
1234567890 1234500000

And when I did
1234567890 .1234567899
It converted into
1234567890 .12345

(I had to put spaces here to prevent Yahoo! from cut the number


So, it is the same as Excel2002, if you need a better one regarding this, use SPSS

VBAXLMan is back

Friday 1/16/2009 9:28:25 AM
Fri
Jan 16
2009

Is it possible to add a calendar alert to Excel spreadsheet?

I've created a Excel '07 spreadsheet as a calendar. Is it possible to insert pop-up alerts/reminders in the spreadsheet?


Answer

Yes, using macros, for example:

Put this code in the Workbook module in the VBA (ALT+F11)
Private Sub Workbook_Open()
Application.OnTime Worksheets( "Sheet1").Range( "A1").Value, AlertSub
End Sub

And put this in a new Module after you insert it

Sub AlertSub()
MSGBOX "Alert", VBOkOnly
End Sub

Assuming you have the cell A1 in Sheet1 having the time that you want to alert in

Close the Excel file, and reopen it again with macros enabled, then it is done

VBAXLMan is back to feed your Excel needs

Friday 1/16/2009 9:14:43 AM
Fri
Jan 16
2009

Excel, Update sheets automatically?

Hi,

I have this sheet which i am currently working on - what i would like to do is every time a new record is added to Sheet1, it automatically updates Sheet2.

Sheet1 has 6 categories but every record is just added to the list. Sheet2 has records by categories. It has to be able to find the correct category in Sheet2 and update it with the new record.

Thanks.


Answer

This can not be done in Excel formulas
You will need to do one of the following:
1- Do a Pivot Table in Sheet2 to get the result of each category in certain columns (Might need to do more than pivot table)
2- Do a macro that is refreshed every time you change a cell in sheet1

I can not show you how to do the PivotTable here since it is different between Excel2007 and Excel2003

Also the macro thing cannot be done here, since I need more details from you, but it can be something like this:

- In Excel, Press ALT+F11
- Double click the sheet name (Sheet1 in your case) in the left tree list
- Paste this

Private Sub Worksheet_Change(ByVal Target As Range)
' Macro should be here
End Sub

The macro you need to put here is the macro that I will do it for you for free, if you send me your file to
XL@VBAXLMan.com
with some additional details

VBAXLMan

Friday 1/16/2009 8:13:22 AM
Fri
Jan 16
2009

How to build loops in excel?


Answer

Usually what can be work as loops in Excel is the related references
Meaning:
Put 1 in A1, 2 in A2, and so one
Now, if you want to do a loop to get the result of this function for example:
y = x^2 + 5
Using values in A1,A2, etc as X
Then paste this in B1
=A1^2+5
And copy and paste this into cells down

That is what can be as loops in Excel, if you are looking for loops in Excel macros, that is another story, you didn't add any other details

VBAXLMan

Friday 1/16/2009 4:54:51 AM
Fri
Jan 16
2009

How can retrieve the InstallDate for Windows and MS Office with an Excel Macro ?


Answer

The date of Windows installation is the date that Administrator account was created (or your User account)
And the date of Office istalled is the date of Microsoft Office folder was created
So
Depend on your Windows version (VISTA or XP) use this code to get the installation date/time of Windows

Sub WinDate()
Dim fs, fo
Set fs = Server.CreateObject("Scripting.FileSyste…
Set fo = fs.GetFolder("c:\Users\Administrator")
MsgBox fo.DateCreated
End Sub

Change the c:\Users\Administrator to c:\Documents and Settings\Administrator if you have WinXP
(Although the second one will run in VISTA as well)

Do the same for c:\Program Files\Microsoft Office

good luck

VBAXLMan is back

Friday 1/16/2009 3:59:00 AM
Fri
Jan 16
2009

I need excel spreadsheet help for my new company?

I'm starting a company based on commission only. I need an equation for excel in which I can input a $$ amount sold and have it auto read the different pay scales and generate their income. in $$s and on a line graph. The pay scale is as follows:

$612 to $1223 is 6%
$1224 to $2447 is 12%
$2448 to $4895 is 24%
$4895 to $ 8499 is 26%
and $8500+ is 27.5%

Thanks!


Answer

Do this pay scale in a separate sheet, make it Sheet1 cells A2:C6 having A2 is 612, B2 1223, C2 6%
and so on
Now, in another sheet, put your amount in cell D4, and in E4 paste this:

=INDEX( Sheet1!C2:C6,MATCH( D4,Sheet1!A2:A6,1))

and this in F4
=D4*E4

That is all

VBAXLMan is back to feed your Excel needs

Friday 1/16/2009 12:14:18 AM
Thu
Jan 15
2009

Why won't Excel open when opening a Word merge document?

I have Microsoft Word file linked with Microsoft Excel to merge. Used to be prior versions would automatically open up both files in it's respective windows (Word & Excel), but using Microsoft 2003, it just opens the Word document and when I try to manually open the Excel file (double click the icon) it says I'm already in it. Is there a way to automatically open both files by opening the Word file?


Answer

Excel have the feature of opening more than one workbook by double click on one Icon (It is called Workspace)
Not long ago, Office used to have Microsoft Binder, that enables you to put bunch of Office files (like Excel, Access, PowerPoint, etc) in one file called Binder.
But not anymore

The way you are trying to do, I don't think they care much about it now

I would suggest that you do one of the solutions below:
1- Create a hyperlink in one doc to open another (like hyperlink in Word to open Excel file)
2- Or, Do bunch of shortcuts in one folder to open all you files, select them all, and press enter, to open all those files
3- Or, may do some old fashion bat file.

I can not see any other way

VBAXLMan

Thursday 1/15/2009 2:02:12 PM
Thu
Jan 15
2009

I really need help with Excel.?

I have added drop down lists to a workbook. Now I would like to have a total on another work book of things from the lists. Is that possible?


Answer

Yes, sure
When you say total you mean sum of numbers right?
if so, just do a regular sum like this
=SUM( [Workbook2.xls]Sheet1!A:A)
or insert the SUM function in the second workbook (While first one is open) and go back to the first workbook, and select the range you want to sum

If this is not what you are looking for, you need to describe more

VBAXLMan

Thursday 1/15/2009 8:55:41 AM
Thu
Jan 15
2009

Ms Excel macros problem ??? ?

it give the error

Macros in this workbook are disabled bcase the security leavel is high, and the macros have not been digitally signed or verified as safe. to run the macros , you can either have them signed or change your security level.

i reduce security level high to low , i also crate one password for that file also than after it give this error ?????

what can i do ???

give full information about this problem and how to solves ??


Answer

Excel does that even if your security level is low, you will need to enable macros

I can smell that you have Excel2003 or XP, then do enable when you open the file

IF you not enable the macros, you can not run them, whatever is your level of security

It should show you a window asking you if you want to enable macros

Click "Enable macros"

VBAXLMan

Thursday 1/15/2009 7:13:43 AM
Mon
Jan 12
2009

Problem opening Excel files after crash?

Husband's laptop crashed and we got it repaired but now he cannot open his Excel files. It keeps telling him they are corrupted/damaged or have viruses. Any suggestions? His OS is Windows XP and Excel was a preloaded cab file when we purchased the laptop from Dell.


Answer

The first thing came in is that you have the format of the Excel files in a newer version than your Excel application
If you are sure that you are using the Same Excel version, then make sure you have the serial for that Excel.

The best advice is that you reinstall the old Excel (The one that you already had and worked your files in) and they are going to be opened.

If not, let me know what is the old Excel VS the new one

VBAXLMan

Monday 1/12/2009 1:22:51 AM
Mon
Jan 12
2009

Can i back calculate a formula using excel?

I have a polynomial function here that i known of the y values and want to derive the corresponding x values. Anyone knows how to do it??

y=-0.0969X^4+342.16X^3-452972X^2+3*10^…


Answer

Your formula is cut

Try to insert spaces between it, Yahoo! Answers cut the rest of it

VBAXLMan

Monday 1/12/2009 12:12:20 AM
Sun
Jan 11
2009

How to view more columns in Excel 2007?

I just got Excel 2007 and I can't figure out how to widen the spreadsheet to display more columns. There's an inch of grayspace on the left of the spreadsheet and four inches or grayspace on the right, yet only 9 of my 25 columns are viewable. In other words, I have to scroll left or right to get to the other columns. It's as though the spreadsheet is in a viewer that needs to be resized. Any suggestions?


Answer

Try double click the title bar of that worksheet window
If you can't, then it might be protected
When a worksheet protected mode is "Windows", then you can not resize it unless you unprotect it

VBAXLMan is back

Sunday 1/11/2009 10:07:22 PM

Tags

History