## 81 Q & A posts found in year 2010

### How do you change the what order lines go on a graph in excel?

I have an excel graph and i want to move one line in front of the other. How do I do that?

### Answer

In Excel 2007

- Select the chart

- Go to Design tab > Select Data > then select the series you want to move and click on the up and down arrow in that window to move it up or down

In Excel2003

- Select the Chart

- Go to Chart > Source Data

- In Series tab, move it up and down as required

If you are talking about the 3D chart you have, then that can be made the same way since Excel is using that order to draw the lines

VBAXLMan

### Cluess! I need help getting my barcode scanner Qw2500 to work.?

I have the QS2500 RS-232 scanner. It seems to be working, lighting up, beeping, but it is not working in excel, or inflow program. Help!!! thanks

### Answer

Try it on another port

Try it on another computer

Try it on another OS

Reinstall its own software (from manufacturer website)

After doing all that, you can tell if the problem is in the scanner itself or in the software

VBAXLMan

### Is there a way to send batch emails from a list in Excel through Outlook at different times?

I am curious to know if there is such code or function that can send batch emails from a list in Excel with a personalized message through Outlook for delivery at different times.

### Answer

This is the file that does that

maybe except for the "delivery time" thing

http://www.vbaxlman.com/Files/?ID=4S374y…

You can customized it if you like

VBAXLMan

### Why do i see the lock icons on my ms office files (excel)? How do i get rid of them?

I've a Windows 7 machine.

### Answer

It is not Office issue, it is a Windows issue

You have that drive encrypted and that is why your files have lock on them

Means you cannot access them if you are not the owner

or something like that

VBAXLMan

### Can we use excel worksheet to evaluate a multiple choice question answer sheet?

for eg: to a question, 1) Prime Minister of UK is

a) David Cameron b) Nick Klegg c) Gordon Brown d) None of the above.

how to use logical formulas in EXCEL for candidates answering as a/b/c/d and to assign 1 mark for correct answer and 0 for incorrect

### Answer

You need to put the answers in one row or one column

Then use the COUNTIF formula to calculate how many A's there

Something like this:

=COUNTIF(3:3,"A")

Assuming your answers a,b,c,d are wherever in row 3

VBAXLMan

### Excel question currency question?

Basically what i want to do is take colum m multiply it by 1.42 and show the results in colum N in the cell ajoining the original amount this make sence?

### Answer

If you have the first value in cell M3

then in Cell N3, paste this

=M3*1.42

Then copy N3 down until the end

VBAXLMan

### How do I enter data in Excel that will update in multiple worksheet?

In Excel 2003, I'm trying to create 2 different reports based on the same set of data.

How do I set it up so that for example, in worksheet one, I enter data, but the data will update in the same fields in other worksheets.

### Answer

Also try to copy then Paste Special > As Link

VBAXLMan

### How would I automatically sort the selected cell in excel 2007.?

First in aphabetical order by office location and then by asscending order. What are the steps that this can be done.

### Answer

There is a file that sort any table automatically using formulas

Once you have it, you can customize the sort easily

where is it? where is it?

Ah, here it is

http://www.vbaxlman.com/Files/?ID=HlCOfq…

VBAXLMan

### Excel spreadsheet formulas?

If I was to make an monthly budget with excel, how would I use 4 different formulas for calculating total expenses, income, etc. I am confused on how to get different answers using different formulas>?

### Answer

You need to do those 4 formulas in 4 different cells

If this is what I understood from your question

otherwise, you might need to explain more

VBAXLMan years of Excel experience

### In Microsoft Office Excel, how do I get the sheet tabs to show at the bottom?

### Answer

That was for Excel2003 and earlier

For Excel2007, do this

- Click on the Office Logo at the top left corner

- click on Options

- Go to "Advanced"

- Scroll down until you reach "Display options for this workbook"

- Make sure that you have the "Show sheet tabs" is checked

VBAXLMan

### What is the formula in Excel for a check box when clicked gives time and date in another cell?

I apologize if I am not explaining myself right.

I am working on a spreadsheet for tracking the work that's been done. I would like to use check boxes in a column and on another column for the current date and time when the box in a cell is selected. What is the formula and do you have an example to help me out, please?

For example the check box are in column V and I want the current time to be in the W column.

Thank you so much for helping!!!!

### Answer

I can see what you are trying to do

The problem is that you can not (easily) do multiple checkboxes in a column and connect them to corresponding cells

I recommend you use the below shortcut keys:

CTRL + ;

To insert current date as constant

CTRL + Shift + ;

To insert current time in the selected cell as constant

This way, you will not have an updated version of Date or Time, means once you insert them ,they will not change.

VBAXLMan

### In Excel 2007, how do I make specific number values correspond to specific text values?

I'm trying to create a template for entering social science questionnaires, and SPSS has spoiled me. When I start entering the data, I want to able to type numbers and then have the correct text fill the box. For example, one question concerns race. I want to be able to type "1" and have "caucasian" automatically appear in the box. However, I want to RETAIN the number values so that I can easily run statistical analyses. Can Excel do this? Someone please help!

### Answer

Yes, it can

This called VLOOKUP function, it is very popular and widely used

Here is a lesson on how to do that

http://www.vbaxlman.com/Lessons/?ID=b1vi…

VBAXLMan

### My excel is not working on my 2003 professional edition of microsoft. How do I get it to work again?

I just got rid of a virus and tried to upload microsoft updates. Now excel doesn't work. Every time I try to open it, it tells me that it is installing the new version. Then it says the network is unavailable and something about PRO11.MSI.

### Answer

It is obvious, you will need to reinstall Office2003 again

You need to have the CD for it, the same original CD you installed it the first time, to do Installation repair.

When you insert the CD, you will have several options one of them is Reinstall Office to fix some errors.

VBAXLMan

### How can you make an excel workbook, with about 1500 formulas that total from 3 other workbooks?

The auto fill does not work because it is pulling in information from 3 separate workbooks. Is there a quicker way then building all the formulas separately.

### Answer

It looks like you have custom external links,

something like this

http://www.vbaxlman.com/Lessons/?ID=fjam…

This lesson will show you how to get values from several sheets

And also this one to show you how to generate external links

http://www.vbaxlman.com/Lessons/?ID=pbkw…

VBAXLMan

### How to create a fixed x-axis in Excel 2007 charts?

I need to make an Excel 2007 line chart with a fixed x-axis that starts at 0 with tic marks every 10 values up to 100. My data values fall within this range, but I don't want the data points to determine the x-axis. Can someone tell me how to do this?

### Answer

Right click on that X-axis, and click "Format Axis"

Then, make sure you check the options that said "Fixed", instead of "Auto"

Doing that will make it fixed to the specified values, even when the line goes out

VBAXLMan

### How do I get my PDA to scan bar codes into pocket excel while I'm scanning into another program?

I use a Dell Axim x5 with a scanner attached to scan the bar codes of books in order to find out their prices on the net. I would like to get a listing of each bar code scanned in pocket excel without having to scan each bar code twice.

### Answer

Usually scan bar devices got the scanned bar info into one application

So you need to have any book scanned twice in order to make it in both applications

If you want it to read the bars into Excel only, then just open Excel, and start read the books

You might need to press enter after each read

VBAXLMan

### How to send email to multiple people from an excel file?

I have a an excel file with over 50 email ID's and I want to email them all together. Is there any way of doing it with out adding them induvidally?

### Answer

Here is a free file

http://www.vbaxlman.com/Files/?ID=4S374y…

You will need an email client (like Outlook) already installed to do that

Enjoy it

VBAXLMan

### How can I insert intervals of numbers, not discreet elements into Excel?

I need to sketch the sin, cos functions' graph in Excel. I have a problem with a domain. I don't want to manually type myriad values - even then, the graph is sharp, not curved, and inaccurate. Is there a possibility that I simply set an interval, infinite, but bounded set, as a domain? If yes, how do you do it?

### Answer

You need as many points as you can to do the exact curved sin

to answer your question, do the following:

- Do the first number you want in a cell, say you do 5 in cell E4

- Now do the second number with the intervals into the second say.

So if you want to have the numbers as 5, 15, 25 (with 10 interval), put 15 in cell E5

- Now select both cells

- Drag the selection from the black square at the right bottom corner down to whatever range you want.

Or, you can also do:

- Put 5 in cell E4

- Select some range of cells including cell E4 (Cells E4 to E20)

- Go to:

Edit > Series > Fill (if you have Excel2003 or earlier)

Home > Fill > Series (below SUM sign)

And select the interval and stop value

VBAXLMan

### How do I stop excel auto formatting cells into dates when I restart excel?

In other words I don't always want to have to go and change my settings back to what I want

### Answer

That is one of Excel big bugs

Once you enter 1-4 or 11-12, Excel converts it into date

To go over that, you need to do one of the following:

1- Insert as many spaces as you can, between/after/before your number

2- Insert the single quote ' as the first character in that cell

3- Add some text before/after/between the numbers, may be like this G1-4

4- do a formula that generates exact number, like this

="11-"&"4"

or even like this

="11-12"

VBAXLMan

### How do you label extra information on Excel line graphs?

So if I already made a line graph and then the instructions say that I need to label the point at which certain things happened in time along the line, how do I do that on Excel. (without doing it by hand)

Thanks :)

### Answer

What do you mean, "by hand" ?

You can do the following:

Show labels for all data points

Select the point you want to label

Click on it again (not double click), just click on it again after few seconds

Now you can modify that point's label

VBAXLMan

### How do I generate small groups from a large Excel file?

I have a large Excel file (~22,000 entries). These entries span across multiple columns (e.g. name, address, phone number, etc.). I would like to break this down into groups of 5, 10, or 20.

### Answer

Use Filter (Auto Filter, or Advanced filter)

If this will not do it for you, then you might need to consider Subtotal

If both didn't do it, a macro will sure do

mail me here some details and will be glad to do it for you

VBAXLMan

### How do I transfer a schedule from Excel to Outlook?

I have schedules that I need to transfer from Microsoft Excel to Outlook. Is there way to do it without typing everything in? Is there a way I can just put it in all at once?

### Answer

Did you try the Import from Outlook?

It should do it

Here is a tip

Do an export to Excel file for your already existence schedule in outlook, then go modyfy that file (or create one like it) then import it to outlook

Hope this will help

VBAXLMan

### Google spreadsheet format: how to make it look like a questionnaire and not like an excel file?

i wrote a spreadsheet on google which looked like a questionnaire (with options, multiple choice etc.) but when i saved it the original format disappeared and instead i got what looks like an excel file. How can i switch to the original format without re-writing it?

### Answer

When you say "saved it" you meant export it to your desktop, right?

If so, then try to save it as another format, may be OpenOffice Calc, or any other format

Google was meant to be online and accessed from other locations

If not, then you need to report this as a bug to Google

VBAXLMan

### I can not type arabic in MS word 2003?

I can't type arabic in word 2003, I can type in excel and other programs but in word I have to press each space bar to type the arabic.

### Answer

Word has two sets of keyboard keys to type (vs one set in all other applications)

I guess you already know that

Right CTRL+Shift will make it Arabic on most applications, in Word will make it write Right-to-Left

Instead, use

Right ALT+Shift to type in Arabic, even in the same direction.

If this is not the case, then I didn't understand the question well

VBAXLMan

### How would I create the type of graph shown in the link below?

I have data in excel for the daily minimum and maximum temperatures and daily rainfall for a full year.

Also, what is the name of this style of graph?

### Answer

Use the regular column chart

It is called Histogram, but Excel will not recognize that name, use Column chart

Assuming you have the data listed by date.

VBAXLMan

### How to import into MS Word ,data from two worksheets from Workbook of MS Excel file ?

I have MS-excel Data with a customer sheet with address details on one sheet & Items bought by a customer on other sheet,by using mail merge I am able to generate a letter for each customer by using customer sheet,I want to include details of items bought(from that items sheet).

### Answer

I suggest you having a third worksheet that list the customers and their buying details

Then mail merge it

You might need to use formulas to do so, like VLOOKUP, MATCH, INDEX, IF, COUNTA, etc

use the customerID to grap these info

VBAXLMan

### How to make connections in excel.?

I want to make a list on Excel, it is a list of money i use. I want to write a number (Expense) and press Enter and then get another number (Remainings). For example: I have 1000 in one square, then i write 600 in another one this is money i have used, and then I want Excel to calculate for me and write 400 in the last and third square! Does anybody understand this and know how to do it?

### Answer

Yes sure

You need to do the following:

1- Put the first amount (1000 in your case) in cell D2

2- In D3, paste this

=D2-C3

3- Now, put 600 in C3

From now on, put any other amount in column C starting from C3

So when you do an amount in C4, you just need to copy cell D3 to D4 and so on

Meaning anytime you add another amount, you just need to copy the corresponding cell in column D from the cell above

Does that make sense?

Let me know

VBAXLMan

### Need help putting quotes around my text in excel spreadsheet?

Hi,

I have an excel spreadsheet with different words in all the cells. I need to put quotes around each word. I have tried using a macro but I am new to it and so far have not been able to get it to not just paste the exact same word I used to record the macro. Every cell is a different word. I need the macro to ignore the word in the cell and just put quotes around each individual word. Can anyone help with this??? I appreciate it!

### Answer

The char code for the quotes is 34

Meaning, if you use Chr(34) in VBA code

or

=CHAR(34) in Excel formulas, you will get the quote

Maybe something like this, in formula bar

=CHAR(34)&C1&CHAR(34)

or

Range("A1").value= Chr(34) & Range("A1").value & Chr(34)

Hope this will help

VBAXLMan

### Any microsoft excel experts here? need excel formula please help?

i know i asked this before but i never got a formula and i am still looking

i am looking for an excel formula in which i can add exactly one month to the month thats on the left of it on the previous row.

for example

Date Date

12/27/10 1/27/10

the second one i wish to show up automatically,

i know that it would be something like this:

=DATE(YEAR(C13), MONTH(C13)+1, DAY(C13))

but i am looking for a formula for the entire column so that i do not have to add the formula to each individual cell

thank you in advance

### Answer

Just Copy and Paste that cell (with formula) to the cells below

VBAXLMan

### Can someone help me with a lookup table in Excel 2007?

I have to "create a lookup table that will help determine the letter grade for each student. The percent levels should be entered in the first column; the corresponding letter grades entered in the second column. (Assume a typical grading scale where 90%=A, 80%=B, etc.) Name the lookup table Grade_table. Arrange the % levels and corresponding grades from lowest to highest. Please help!!!

### Answer

You need to use the VLOOKUP formula

In addition to Name feature

First you need to do the table, somewhere in any free area (say G1 to H10) put the percentage in G, corresponding Grade letter in H

Then, select that range, and go to Formula > Name, type in the name you have "Grade_table"

Then in cell A1, put any grade, say 76

In B1, paste this

=VLOOKUP(A1, Grade_table,2,TRUE)

You need to sort the Grade_table ascending by column G

VBAXLMan

### Excel help? 10 points?

http://i181.photobucket.com/albums/x93/x…

1. Add the numbers in the Student Number column as labels. (Example: '1203)

What does this question mean and how would I add the labels?

### Answer

Labels by default are enabled, means you can use them right away

however, labels are usually the first column (or first row) of a table

It is confusing that you want to ADD labels

may be you need to enable them, if they are disabled

Go to Excel Options > General tab

And search for the "Use labels in formulas" check box, make sure it is clicked

After you enable them, you can use them in formula, just like

=SUM(Student Number)

to sum all the numbers

let me know if it worked or not

VBAXLMan

### I need to have the auto sum function in excel calculate alphabet characters?

I would like to have the sum function include alphabet when calculating sums. How can I do this?

### Answer

Did you try the formula LEN

LEN is the formula to sum how many characters in a cell

So, if you add this in column D (Assuming the column C has the cells you want to sum its chars). paste this

=LEN(C1)

Then copy it down to get the length of all cells characters

Then do a regular SUM on column D

Hope this will help

VBAXLMan

### Trouble with sorting numbers on excel?

I'm trying to sort grade numbers on an excel worksheet, it was working for some columns and now I'm trying it again and it's not working. I'm highlighting all the numbers in the column and going up to Date, then sort. Any help?

### Answer

Try not to select the cells

Instead click on one of the cells, and not all of them, then do sort

What is Excel telling you when you click on "Sort"?

Excel should suggest the sorting options based on your selection

Again, clicking one cell when you do Sort, can remove a lot of pain

VBAXLMan

### Why does it come up with "#VALUE!" when i subtract 2 cells in excel? and how do i correct it?

How do i get it, to come up with the actual answer. Thank you! :)

### Answer

Another way is using the N function

Like this

=N(A1)-N(A2)

N will convert the text (that represent a number) into a number

VBAXLMan

### How do you put two different lines on a graph in excel?

Thanks.

I need a baseline and intervention line.

### Answer

In Excel, to control a graph, you need to design its table

So it is all about tables here

New line means new series, so you need to add another series to do that

I used to add another series, having same value for all data points, and add it to the graph series

VBAXLMan

### Is there a formula for a name count within the entire workbook of excel?

I am creating a workbook in excel with January - December Spreadsheets. I need to find out if there is a formula that I can use to count how many times a name is used through out the ENTIRE workbook, not just each sheet. Thanks in advance.

### Answer

You have two options:

1- is use the COUNTIF with multiple-3 dimensions ref

In English, do this

=COUNTIF( January:December!A:IV, "name")

Just make sure that the sheets January to December are in the regular order.

2- Using INDORECT to retrieve the value for each sheet (In a new sheet), then total that

- Put sheet1 name in cell A2 in new sheet

- Paste this in B2

=COUNTIF( INDIRECT( A2&"!A:IV"), "Name")

- Put other sheet names below and fill B2 down

- Total the column B to get the total plus the number for each sheet

Let me know if all these makes sense or not (from my profile)

VBAXLMan

### I want the output of my PHP to be opened in EXCEL or OpenOfficeSpreadsheet. How can I do it?

I have one PHP program which use mysql database. I want t the output to be opened in EXCEL/OpenOfficeSpreadsheet(in a separate window) when I click on a link provided. Any option to do it other than to save it as a CSV?The problem with CSV is that it opens as a single column and I have to use text to columns option to convert into columns. Thanks in advance.

### Answer

CSV is Comma Separated Values

Means if you use values with comma as separator, Excel will open in in multiple columns

like this

"Value 1" , "Value2 " , "The long value of 3rd column"

VBAXLMan http://www.dougboude.com/blog/1/2009/06/PHP-Export-to-Excel-Snippet.cfm

### How to add check boxes to Excel 2007 to a column if a the row has data?

I want to have check boxes in column D, but I dont want the box boxes to appear unless data is intered into column A, that way I can keep the spread sheet clean because I will be adding data to this spread sheet daily. I have no idea how to even start this process. Any help would be great! Thanks!

### Answer

The only ay to add checkbox automatically is using VBA or macros

However, there is another way, but using Validation instead

You can use Validation based on Validation, or Validation based on your value in column A

Make the validation list of Yes, No and you will get something similar to check box

Check out Multiple validations here

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

VBAXLMan

### Does anyone knows the command to put the data from excel into the R programme?

Does anyone knows the command to put the data from Excel into the R programme ( R programme is a statistical programme) I will be really thankful .

### Answer

You need to export the data you have into CSV file format

Most statistical and database applications can read CSV formats

Good luck

VBAXLMan

### How to work with open Excel workbooks where filenames are given in cells?

I have the user input the file name into a cell. There are several open Excel files so the user will call out the name of one. What is the syntax to reference the named file so I can copy/paste?

The files come from emails so want to bypass having to save them and use the

...Workbooks.Open(Range("A5").Value)

command.

I've tried lots of variations with syntax but can't get it.

### Answer

You need to create a loop through all open files, like this

Now, if you have the cell that holds the file name in A12 sheet1, then do this

MyFileName = Sheet1.Range( "A12").Value

For I=1 to Workbooks.Count

If UCase(Workbooks(i).Name) = UCase( MyFileName) then

Workbooks(i).Activate

Exit For

End If

Next

I used Activate because doping Open while the workbook is already open will generate an error

Try it and let me know (using my profile) if that solved it or not

VBAXLMan

### How to determine an unknown X value on excel?

So we had to do a beer's law plot in my chemistry class. I have an unknown sample's absorbance and I need to determine the concentration. We are suppose to use excel to do so. The absorbance is my Y value and the concentration is graphed on the X axis. How can I determine the concentration of my sample. Thanks for helping me!

### Answer

You should have either a formula that runs the graph

Or a graph so we can get the formula

You cannot do it other way

If you have a formula, you can easily apply to get X value using it

If you have a graph, then you need to add trendline (Right click on that curve and select "Add Trendline")

Doing so will show you a window that enables you to show the formula

from that formula you can get the X value

Let me know if you did or not, I can guide you through that process if you lost

VBAXLMan

### How do you write a VBA function to work in any Excel spreadsheet you have open?

I wrote a function and saved it in my personal.xls file. When I want to call that function from another spreadsheet, I have to write Personal.xls!FunctionName(). Is there a way to write the code so that I don't have to reference my personal file each time?

### Answer

That is because your function name is the same as a build-in function already used in Excel

Try a unique function name, like MYFunc0001

That is the only reason I see, otherwise, the function would be work easily as long as the function file is open

I am using that all the time

let me know

VBAXLMan

### How do I find an 10 character alphanumeric string in a Excel spreadsheet cell with 70+ characters?

I need to find a 10 digit character string that is in a cell in Column G that has 70 plus characters. It can be located anywhere. The 10 character string will have these unique attributes:

* 10 characters long

* 1st two characters will be alpha (can upper or lower case)

* last 8 characters will be numeric

The following formula provided by this forum gives me a TRUE return if the this string is the only data in the cell, starting in position 1.

=AND(LEN(F2)=10, CODE(LEFT(F2,1))>64, CODE(MID(F2,2,1))>64, ISNUMBER(VALUE(RIGHT(F2,8))))

How can this formula be modified to:

1.) Allow me to find the string anywhere in the text

2.) Place the 10 digit string into Column H

Thank you.

Your assistance is appreciated.

Mark

### Answer

I would do it if I only have the file

mail it to me so that I can do what you want

Trust me, If Excel can do it, then VBAXLMan can do it

### How do I remove blank (but not necessarily empty) cells from a drop down menu in excel?

I want to have a row of drop down lists. In each drop down I want all the options to exist EXCEPT any that have already been selected. This is my primary problem. The closest I can get is to remove value from the drop down range by an IF function in the source data, so that the list updates based on what has already been selected. This requires extensive functions and also leaves blanks in my drop down menus. I'm keen to have the drop downs only contain the remaining options and no blank gaps. Are there any solutions to either the primary problem, or, failing that, a solution to remove the blank (but not empty) cells from my drop down menus?

Cheers :)

### Answer

You have very interesting request

Check out this file

http://www.File1.net/Links/NewList.xls

It has new list populated every time user selects an item, the new list will have the same as old list without the newly selected one, it was done for tutoring purposes

It should do what you are looking for, you might need some tweaks though

That was done using the following Excel features:

Data Validation

OFFSET function

IF function

Name

Let me know if it helped or not

VBAXLMan

### How to do in excel when i key-in data in A1 the information in B1 and B2 will appear at C1 and C2?

for microsoft excel 2003

### Answer

Paste this in C1

=B1

And this in C2

=B2

That is all

VBAXLMan

### How do I make a half life graph on excel?

Excel keeps making it into a weird kind of graph, and I want a line graph with the years as the X axis and % left as the Y axis

### Answer

Once you have the values in two columns

Insert chart, make sure you select the XY-Scatter from the chart type

VBAXLMan

### How do you add an npv function into vba for cash flows?

I have interest rate as 10% and future value of 1,000. I've already done everything in VBA that allows excel to show 10 years of present values, future values and compounding factors. I just don't know how to add a function in for NPV so that a specific cell would add up all the present values that were already calculated with the formula pv = fv / (1+irate) ^ 1 using VBA and not excel's npv function or sum of values.

### Answer

You have several options here

1- Use the mathematical way to calculate the NPV

2- Use some temporary cells to set the values and use NPV in another cell do calculation, then read the result

3- And the one I used a lot is to use WorkSheetFunction object to use Excel function in VBA, like this

NewValue = Worksheetfunction.NPV( MyRate, MyAmount, etc)

If none of those helped, that means I didn't get your question right, reply me here.

VBAXLMan

### How do you export numerical values from java into an excel file?

Is it possible to export an array of numbers generated by a java program into a column of numbers in excel?

### Answer

You can easily export these array into CSV file

http://en.wikiversity.org/wiki/Java_File…

http://www.roseindia.net/java/beginners/…

CSV files are Regular Text files with extension of CSV

Excel by default would open CSV files when double click

VBAXLMan

### Import address.dat file?

Hi, is there a way to import an old "address.dat" file into Outlook 2007 without installing Palm Desktop? I have an old backup contacts list but I no longer use a Palm. I have tried to read the .dat file with Notepad, Word and Excel but it is illegible. Thanks in advance.

### Answer

Try these links

http://www.ehow.com/how_6077867_convert-…

http://www.convertzone.com/all/go-epab%2…

### Microsoft excel function?

how would i enter f(x) = 33.5 +1.3log(x+3) in microsoft excel as a function in order to graph it?

### Answer

Let's say you will have your x's value in cell A2, paste this in B2

=33.5+(1.3*LOG( A2+3))

And press Enter

If you got new X value in cell A3, copy and paste cell B2 to B3, and so on

VBAXLMan

### How do I put multiple criteria in a case statement in excel visual basic?

Here's my problem: if cell k4>0 and b4>100, then I want b3 rounded down to the next digit. If cell k4<0 and b4>100, then I want be rounded up to the next digit. If cell k4>0 and b4<100, then I want b3 rounded down to the nearest 1/100. If cell k4<0 and b4<100, then I want b3 rounded up to the nearest 1/100. Since this analysis is done only once in the spreadsheet, I don't think I need a loop statement. Thanks.

### Answer

You want that in VBA in Excel, right?

Because if you want, you can use formulas to do that.

I will assume you want it in VBA, since nested IF is not that hard to figure out.

Case statement work on one variable or criteria

Meaning, if you insist using Case, then you will need multiple Case statements, like this

Select Case Sheet1.Range( "K4").Value

Case >0

Select Case Sheet1.Range( "B4").Value

Case >100

Sheet1.Range( "B3").Value= NewValue1

Case <100

Sheet1.Range( "B3").Value= NewValue2

End Select

...

...

End Select

And I guess you know the rest

Let me know if you need more than this

VBAXLMan

### How can I transfer excel data into a webpage?

I have an excel spreadsheet, and I want to get it into my website.

Also, if I make any changes to the original excel file, it should be reflected upon the webpage as well.

Is there any code that can make that happen? Or any application that can do that for you?

### Answer

I have a suggestion doing that easily

First create a new worksheet, having all the data you want as you want it in the webpage

Then just export that to HTML

In English, you have cells with values in some worksheet (say Sheet1)

Then create new sheet named (Sheet2)

Spend some time to change the layout of Sheet2, with all borders/format/colors/themes/ etc

Then use the cells you have to link to the original ones in Sheet1

Any time from now on you change Sheet1, Sheet2 will be updated, now you just need to save that worksheet as webpage and upload it into your server

If that doesn't make sense, reply me here to show you how to do it

VBAXLMan

### How do I make a comparison chart using formulas in Excel?

I have an assignment to do where I need to research three different computers and make a comparison chart to find out which is the best computer for the money. The only problem I'm having is that I was never taught how to do this using formulas and/or functions. What formulas would I use to compare different things and find which is the best?

### Answer

Your question is so general

But let me try

You need first to build the table

Based on the data you have, you need to study formulas like

=A1/A2

or

=

besides IF, SUM, AVERAGE

most of these formulas are in Insert > Functions (if you have Excel2003 or earlier)

or in Formulas tab > Insert Functions (If Excel2007)

Let me know if that worked or not

VBAXLMan

### How do you add a critical value as a dotted line to a column chart in excel?

### Answer

Use "Add Data" to add new series

First you need to create a new column in the spreadsheet itself having that critical value (one value for all column cells)

Then add it to the graph

You can add it either from "New Series" or "Add Data" depend on your Excel version

Or, add it as regular bar, then change its type to XY-Scatter

Let me know if that make sense

VBAXLMan

### When using Excel how is a percentage added for the cost of living at 4%?

### Answer

Paste this in cell B3 assuming your cost of living is in cell C3

=C3+C3*4%

or, you can also put the 4% in another cell, say H1

In this case formula in B3 would be

=C3+C3*$H$1

VBAXLMan

### How do i create a dynamic chart in excel 2007 that creates a chart from ONE CELL only that is updating....?

how do i create a dynamic chart in excel 2007 that creates a chart from ONE CELL only that is updating its data from an external source every sec....i want to plot the values as they change into an excel chart live from this one cell...thanks, christian

### Answer

When you create a chart, make sure you select THAT CELL when it asks for the value

Doing this will make that chart updated every time the cell is updated

That is what I got from your question

VBAXLMan

### How do I populate corresponding cells with a code in excel?

Hello Everyone,

I have been struggling this for so long , your help will be much appreciated.

I am trying to make an invoice for a sales order with many different items. I need to know how I can populate the corresponding cells automatically from data stored somewhere else when I enter the product code for the specific item so I don't have to always enter it manually. For example, if I enter product code A-1 on cell A, I want the other cells on the line to be filled out automatically like "Hair Styling Iron" on cell B and "$50" on cell C and so on...

Thanks!

### Answer

Once you have this table in some sheet (say Sheet1)

A, B, C, D

ID, Name, Description, Price

ID1, Name1, Hair Styling Iron, 50

Then in your invoice sheet, let me assume you have cell B15 has the Item ID that you want to populate its values, then in cell C15, paste this

=VLOOKUP( $B15, Sheet1!$A:$D, 2, FALSE)

And paste it down to fill other items

Then in D15, paste this

=VLOOKUP( $B15, Sheet1!$A:$D, 3, FALSE)

And paste it down to fill other items

=VLOOKUP( $B15, Sheet1!$A:$D, 4, FALSE)

And paste it down to fill other items

And you got what you are looking for

Let me know if that make sense or not

VBAXLMan

### How do i do regression analysis with excel 2007 to find the parameters for the equation f.r.=A0x0+A1x2+A3x3?

the x's are given data and i need to solve for a0,a1,a2

### Answer

Put A1, A2 and A3 values in cells B2 to B4

Put x value in cell F1

Then in cell C2, paste this:

=SUM( B2*$F$1, B3+$F$1, B4*$F$1)

Cell C2 has the result

Now changing any of the cells B2, B3, B4 or F1 will affect the result instantly, try it

VBAXLMan

### How do i add 2 different sets of data to one graph on Excel 2007?

im doing a chemistry lab and i have to make a computer generated graph with excel. im using Microsoft Excel 2007 and im having trouble. ive added my info and for some reason it wont let me put the 2 different sets of data on the same graph. my two axis' are supposed to be temperature on the Y-axis and time on the X-axis. ALSO: if it is possible for you guys to help me find out how to do the "best fit line technique" i would really appreciate it.

### Answer

To add new series

- Select your chart

- Go to Design tab

- Click on "Select Data"

- Click "Add"

- Select the new series values/title, etc

That is all

Now regarding "Best fit line"

- Right click on the series you want to add best line to it (The line you want to get its best fit)

- Select "Add Trendline"

Hope this is what you are asking about

VBAXLMan

### How can I add a column of cells in Excel like C3 +C49 Excel says the formula is wro?

Addition of a Column of Cells

=(C3:C49)

Excel says the formula is wrong why?

= SUM (B1:B3)

123

### Answer

I don't understand the problem

Try this though

=SUM( C3:C49)

VBAXLMan

### How can I link two separate workbooks in MS Excel 2000?

I am looking to link some formulas so that whatever I enter in January workbook will then go to February automatically then march and so on. I have tried copying and pasting special but that just copies the formulas what I really want is for them to talk to each other. Being not as computer savvy as most the answer in the most basic terms would be highly appreciated.

### Answer

When you do Paste Special

Select "Paste Link"

This will do it

OR

You can go to February sheet, select a cell and type

=

then select the cell you want to link to in January sheet

VBAXLMan

### Excel formula help. In a list of numbers, is there a formula that will tell me the top 3 numbers?

I have a variety of numbers in a vertical list. Is there a formula to input that will tell me the greatest 3 numbers in the list? And another to tell me the least 3 numbers?

### Answer

Just like siti Vi said

use this to get the largest three numbers in cells B2, B3 and B4

=LARGE( A:A, 1)

=LARGE( A:A, 2)

=LARGE( A:A, 3)

But adding the second request

Paste this formula in C2 to get smallest one

=SMALL( A:A, 1)

And this in C3 to get 2nd smallest number

=SMALL( A:A,2)

and this to get 3rd smallest

=SMALL( A:A, 3)

VBAXLMan

### What is the VBA code for Convert Excel Worksheet in to PDF in the Name of the Text of specified Cell?

Please Mention the VBA code to convert Excel work sheet in to PDF in the name specified in the particular cell of the Excel Sheet.

### Answer

I am assuming you do know how to play around with VBA

In this case, install this PDF Printer

http://www.primopdf.com/

Then print the Excel file through VBA (Record macro and see how it does it)

Then use this VBA line to rename the exported file

Rename [originfilename.ext] AS [newfilename.ext]

Or

Save the file itself (Excel file) with that name from cell, then do the export

PrimoPDF usually saves the file in the same name as the Excel file

Good luck

VBAXLMan http://blog.soliddocuments.com/2010/01/b…

### How to change the author of excel macros?

how to change the author of excel macros?

i've gone into visual basic and replaced the original author's name with my name, but it still appears with the original authors name when i use the macros in excel.

Thanks!

### Answer

Excel saves these info usually in the few lines of the macro (the ones that start with ' )

So editing that will do it for most cases

However, you might also looking for the Author of the file itself (The file that has the macro), change that also in Right click in Explorer > Properties

Hope that is what you are looking for

VBAXLMan

### How do I create a book collection list in excel 2007?

I am trying to categorize the library books I have read based on the receipts the library provides on check out. The library cannot provide me a list of the books I have read because of "Privacy" laws. I cannot remember if I have read a certain book before till I start reading it after checking it out.

### Answer

I am not sure if this will help you or not

but if you have the ISBN number you can easily get the book info using this small Excel-app

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

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

### My excel is not working well, as soon I open it.?

The samething is happening with acrobat, is there any free program I can use to fix these problems.

Thanks

### Answer

Hmmmm

Most problems like this are caused by temp files

Let us try the following.

- Clear history and cookies

- Delete temporary internet files

Both 1 and 2 are in Internet options from Control panel

- Go to C: > Documents and settings > YourUserName > Local settings > Temp

and delete every thing here

(If you could not see Local settings, type it in address bar)

- Restart computer and try again

VBAXLMan

### How do you run a macro within a formula in microsoft excel 2007?

.... for example If(A2=A3, "Run Macro", "")

thats what i want to do, i just dont know how to do it

### Answer

A formula is ACTUALLY a macro

It is FUNCTION macro and not SUB macro

So, to do that, you need to replace the lines of your macro

from

Sub WhatEverName()

....

End Sub

TO

Function WhatEverName()

...

End Function

AND, putting that function in a module (and not a form module) will enables you to run it as formula

However, you need to go to "User-Defined Functions" in the Insert Function dialog and you should see it there

Keep in mind that this is a macro, means that you need to have the macros enabled for that file to make it run

Also, you can run that formula from any opened file, as long as the file that has the function (the function macro) is open

You can see it in action in this free file

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

Does that make sense? let me know if not

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

### How can you add games to a Excel sheet?

A co-worker had a TBS game (mini golf) up on the computer even though our computers internet web pages are blocked. He had it using a excel sheet but I cant figure out how he got that game on excel.

### Answer

Here it is

http://gamesexcel.com/games-excel-golf.h…

Just few lines when I Google "mini golf tbs in excel"

### How do I view and edit Excel documents on my HTC Snap?

I have been trying to figure it out, it was the main reason for buying this particular phone. I have read through the manual twice, but most of the stuff in there is common sense stuff...not what I need. Can anyone help me?

### Answer

Your phone comes with Windows Mobile 6.1, right?

Then you should already got Microsoft Excel Mobile.

Just copy the file into your phone and open it

If you don't have Windows Mobile, then I you need to have a third-party app that opens it.

VBAXLMan

### How do I make an abridged frequency table using excel?

I have a large data set, they're ordered but I want to create an abridged table i.e (20-29, 30-31, etc..) Halp. x

### Answer

You need to do it in formula

If you have Excel 2007, then the function SUMIFS will help you

http://office.microsoft.com/en-us/excel/…

VBAXLMan http://office.microsoft.com/en-us/excel/…

### Export/Save from Oracle to Excel Spreadsheet?

Im not getting the option to save to excel.

When I press save for tsv the program stops responding then I oracle shuts down. I have checked the pop up blocker is off, enabled downloadds from IE and have also made sure the path is correct from IE - windows, Still cannot get the option to save as .xls

Many Thanks

### Answer

If you can try to do export to CSV format, Excel will also be able to read that

Try it

VBAXLMan

### Can i print a webpage onto a saved word document?

Can i print a webpage onto a saved word document WITHOUT having to copy the webpage then open that word document and paste it?I have a letterhead i made in Word i have it saved and i want to print customers invoices from my website backoffice and it opens up the invoices as a webpage view of course and btw it is a HEADACHE to import to excel then quickbooks so im not taking that route so please let me know if there is anyway i can print a webpage onto a saved word document so i dont have 2 copy and paste every order otherwise i will have 2 go 2 Kinkos or staples and have 2 make 500 copies of the header then set my printer up so it always prints from a little below the header etc please help me save 50$bucks all the time for copying thanks!

### Answer

After you save the webpage as HTML, depending on your browser (usually File > Save As)

Go to Word and open that file using File > Open

You can also try that in Excel

Or, in Excel, you can go to Data tab, and select "From Web"

Hope this will help

VBAXLMan

### Create an automatic table based on the past three months?

hi, i have an excel sheet with the past five years data on it. Each row represents a month so each month it gets one row longer.

I already have a defined range that selects the most recent 3 months, but i need to know how to display the last 3 rows and the field headings on another sheet in a specific position by the click of a button(macro).

I can make a line graph using the last three rows of data with just a click of a button and now i want to show the data used underneath the chart.

Thanks for all answers

### Answer

You don't need a macro to do that

You can make it in Formulas which means faster, auto updated, etc

Let's start with some assumptions:

- You have two sheets (Sheet1 has the full data and Sheet2 to have the last three months)

- In Sheet1, your data starts from cell A1 along to V1, with headers in first row and the data below it.

- In Sheet2, you want the last three months to be in A2 to V2, A1 to V1 has the row headers

Now, in Cells A1:V1, just copy the headers (you need to do that once)

In cell A2, paste this

=OFFSET( Sheet1!$A$1,COUNTA( Sheet1!$A:$A)-ABS(ROW()-1),COLUMN( )-1)

Then copy A2 along to V2, then copy Row2 into Row3 and Row4

These three rows will bring you the last three rows from your table in Sheet1 assuming you don't have any empty cells in that table in column A

And when you add new values at the end of that table in Sheet1, these will automatically updated

Does that make sense?

let me know

VBAXLMan

### How do I copy particular images from one excel file to another in a automatic manner?

I have an excel file with more than ten thousand of product details and its images. Most of the times I am getting the requirement for images of 100 or 200 random items. Each time I have to search manually and copy those image to the new file. It would be great if there is any 'program' or 'shortcut' to paste those images from the original file to the newly requested file.

Thanks in advance

### Answer

Excel has the most powerful macros (VBA), that we can use

I recommend saving the images out in a seperate folder, naming them with certain mask that has the product ID

Then, when you need a product image, you can easily let the macros to call its image

I use this technique all the times, however, you might need some VBA skills

VBAXLMan www.samotech.net

### Excel Data Bars. How to change its orientation?

I want to use Conditional Formatting > Data Bars in excel 2007.

The challenging part is that I want the Data Bar to grow from Right to Left in the cell.

By default in excel they go from Left to Right in the cell and I can not find a way to change it.

Is it possible to change the the orientation? If so, How to??

thanks

### Answer

I can't see a way to go over that

Sorry!

VBAXLMan

### Microsoft word disks?

So my sister has these installation disks for microsoft word, power point, and excel, and she installed them on her laptop. Would I be able to use them on my computer with out her losing hers?

### Answer

If you got your own serial number that you bought from Microsoft, yes

Otherwise, it is consider stealing, and no one will tell you to do so

VBAXLMan

### How can I put a private sub within a private sub?

I am working with visual basic 2008 and I want to create a timelog within excel but have the clock in and clock out within the same worksheet. I got it to work but the times are in different worksheets or should i say different books. I just need to know how to put them together here is the code:

Thanks

Public Class Form1

Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick

Label1.Text = TimeOfDay

End Sub

Private Sub ClockIn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ClockIn.Click

Dim Time As String = Now()

Dim Name As String = TextBox1.Text()

'writes time to label on form

Label2.Text = Time

Dim oExcel As Object

Dim oBook As Object

Dim oSheet As Object

'Start a new workbook in Excel

oExcel = CreateObject("Excel.Application")

oBook = oExcel.worksheet.Add

'Add data to cells of the first worksheet in the new workbook

oSheet = oBook.Worksheets(1)

oSheet.Range("A1").Value = Name

oSheet.Range("A1").Font.Bold = True

oSheet.Range("B1").Value = "Clock In"

oSheet.Range("B1").Font.Bold = True

'Adding the time

oSheet.Range("B2").Value = Time

End Sub

Private Sub ClockOut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ClockOut.Click

Dim Time As String = Label2.Text

Dim Time2 As String = Now()

Dim Name As String = TextBox1.Text()

'writes time to label on form

Label3.Text = Time2

Dim oExcel As Object

Dim oBook As Object

Dim oSheet As Object

'Start a new workbook in Excel

oExcel = CreateObject("Excel.Application")

oBook = oExcel.Worksheet.Add

'Add data to cells of the first worksheet in the new workbook

oSheet = oBook.Worksheets(1)

oSheet.Range("C1").Value = "Clock Out"

oSheet.Range("C1").Font.Bold = True

'Adding the time

oSheet.Range("C2").Value = Time2

'Save the Workbook and Quit Excel

oBook.SaveAs("C:\Book1.xls")

oExcel.Quit()

End Sub

End Class

### Answer

First of all, your question is confusing

You can not put sub inside another

Yes, you can call it from the other one, but not put it there

As far as I understood your question, here is my suggested answer

You need to Open that Excel file first, then modify it, then save it

What you are doing now is creating new workbook every ClockIn/ClockOut

I recommend putting these three in one workbook, call it main.xls for example

And open one workbook (already created) , add new rows for timein/timeout, save it and close, instead of creating new one every time

VBAXLMan

### Database records exported to Excel CSV encode language characters?

I have database records that use different language characters (European languages mostly). When exported to Excel file, CSV to be exact, these characters are come through as encoded. I have an example here, characters "zlw" output as "zlw". I tried "charset=UTF-8", "charset=ISO-8859-1" and nothing. Any ideas?

### Answer

You might need several attempts

- Copy the CSV file, in case you messed it up

- Open the CSV file in NotePad (Open NotePad, drag the file to it.)

- Do "Save As", now what is the suggested format of the file?

- I recommend importing the file in Excel instead of open it

- In both cases, use the same format used in Notepad

I faced issues like that when I work with Arabic/Hebrew chars, and these steps usually work it out

If not, then pay attention to the format when you export the data from the database, most database applications give you the ability to choose the format when export

Good luck and don't forget, VBAXLMan is here

### Can you insert a drop-down or subcategory into an Excel text filter list?

I have a vocab spreadsheet that filters terms by their assigned Category, but there are a great many categories and I'd prefer to make many of them subcategories within the Category filter (essentially, a drop-down within a drop-down, but within a text filter list), rather than create a separate column from which to filter the subcategories, but so far it does not seem possible!

### Answer

Using AutoFilter, the only approach I see is what you already did, which is adding another column with the subcategories

However, you can still use some trick to make it in one column by having certain mask in the categories name

So if you have Cat1, Cat2, Cat3

And Cat10 is sub of Cat1, Cat20 is sub of Cat2, etc

Then do this in the category column

Cat1 - Cat2

Meaning combining the two categories into one cell, the do sort and you can now easily visually see the category/subcategory in one column

Does that make sense?

let me know if not

VBAXLMan

### How to rename worksheets (tab) in a locked workbook in MS Excel?

Hello, am working on a spreadsheet that is locked - But I need to rename the tabs. I know there is a way to open a new spreadsheet and create a macro and go back to the locked spreadsheet and run the macro. Can someone please help with the coding? Thanks

### Answer

This is what you are talking about, right?

http://www.xl-logic.com/modules.php?name…

Let me know

VBAXLMan

### How do i set up a formula in excel using "choose"?

i've got this for example : "11.Jul.95" and need to use a formula ( which i guess is "choose") to pick the season like July - Summer

please helpp!!! :D

### Answer

Actually it is like this

If your date is in cell B1, then in C1 paste this

=TEXT( B1,"mmmm")&" - "&CHOOSE( MONTH( B1),"Winter","Winter", "Spring","Spring","Spring", "Summer", "Summer","Summer","Fall","Fall","Fall", "Winter")

This will show it exactly how you want it

VBAXLMan