ANmarS - Search results

Excel/VBA expert since Sep 1997 (21 years 2 months)

We are moving ...

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

34 Q & A posts found in May

Mon
May 17
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

Monday 5/17/2010 7:59:50 AM
Sat
May 15
2010

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

Saturday 5/15/2010 1:00:58 AM
Sat
May 15
2010

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

Saturday 5/15/2010 12:03:22 AM
Fri
May 14
2010

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

Friday 5/14/2010 6:29:30 PM
Fri
May 14
2010

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

Friday 5/14/2010 1:52:10 PM
Fri
May 14
2010

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

Friday 5/14/2010 11:14:43 AM
Fri
May 14
2010

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

Friday 5/14/2010 2:43:04 AM
Fri
May 14
2010

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

Friday 5/14/2010 2:02:29 AM
Fri
May 14
2010

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

Friday 5/14/2010 1:03:04 AM
Fri
May 14
2010

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

Friday 5/14/2010 12:01:04 AM
Thu
May 13
2010

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

Thursday 5/13/2010 8:59:04 PM
Wed
May 12
2010

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

Wednesday 5/12/2010 8:55:57 PM
Wed
May 12
2010

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

Wednesday 5/12/2010 3:49:22 PM
Tue
May 11
2010

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

Tuesday 5/11/2010 8:36:52 AM
Sat
May 8
2010

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

Saturday 5/8/2010 7:10:13 PM
Sat
May 8
2010

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

Saturday 5/8/2010 11:00:50 AM
Sat
May 8
2010

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

Saturday 5/8/2010 8:37:22 AM
Fri
May 7
2010

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

Friday 5/7/2010 1:54:43 PM
Fri
May 7
2010

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

Friday 5/7/2010 12:53:58 PM
Thu
May 6
2010

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

Thursday 5/6/2010 5:16:55 PM
Wed
May 5
2010

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

Wednesday 5/5/2010 9:29:47 AM
Wed
May 5
2010

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

Wednesday 5/5/2010 7:40:52 AM
Wed
May 5
2010

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

Wednesday 5/5/2010 5:22:15 AM
Wed
May 5
2010

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

Wednesday 5/5/2010 4:00:49 AM
Tue
May 4
2010

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

Tuesday 5/4/2010 4:02:44 AM
Tue
May 4
2010

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

Tuesday 5/4/2010 3:40:57 AM
Mon
May 3
2010

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

Monday 5/3/2010 1:31:03 PM
Mon
May 3
2010

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

Monday 5/3/2010 1:00:36 PM
Mon
May 3
2010

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

Monday 5/3/2010 8:23:46 AM
Sun
May 2
2010

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

Sunday 5/2/2010 7:40:01 PM
Sun
May 2
2010

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

Sunday 5/2/2010 5:33:27 PM
Sun
May 2
2010

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

Sunday 5/2/2010 6:53:36 AM
Sun
May 2
2010

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

Sunday 5/2/2010 12:25:22 AM
Sat
May 1
2010

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

Saturday 5/1/2010 2:11:18 AM

Tags

History