## 294 Q & A posts tagged with excel

### Copy pictures from sheet to another

i have two workbooks, lets say, workbook A and Library Workbook

Workbook A is where all the code has been going, Library has no code in it. My project in Workbook A needs to look up and pull a worksheet out of Library Workbook - just a direct copy. Simple enough in general, only one problem. Most of the sheets in Library Workbook have images embedded in them that dont get copied when doing a direct Workbooks(Library)Cells.Copy

i can get all of the formatting, column widths, and values/formulas, but not the images

now, there is one thing i havent tried that I think will work, but it has drawbacks...i could copy the entire sheet, not just the cells, but the sheet itself and put it in Workbook A. I think that this would work in theory, but Im reluctant to try it for one reason

I have embedded sheet code in Workbook A in the sheet that I want to be the destination for the copied library contents. The sheet code is event-based, and Id like it to stay in the code. So I was trying to paste the library contents into this sheet to retain the sheet code, where if I paste the entire sheet directly from the library, I dont get to keep the sheet code, because the library, by definition, is supposed to be (vba free)

so...Ive been chasing two different possible solutions, neither one with much luck yet.

(1) Learn how to copy cells from a worksheet with the images included

i know you can use vba to copy images, but the sheets have varying numbers of images, so theres no good way to programatically handle all sheets

shapes(1).copy, ...

might be more than one image

### Answer

you still can use shapes.count and start a loop, did you tried that?

however, there might be a downside, when you copy and paste, they will all be over each other, but you can override it by using:

Sheet1.shapes(x).left = Sheet2.shapes(x).left

sheet1.shapes(x).top = sheet2.shapes(x).top

assuming copying shape from Sheet2 to Sheet1

does that make sense?

### 3 questions in microsoft excel! please help me!?

If you want to change a setting that applies to Excel as a whole, such as R1C1 referencing style or showing the Formula Bar in the program window, what are your first steps?

Choose one answer. A. Click the Microsoft Office Button, and point to Prepare

B. Click the Microsoft Office Button, and click Excel Options

C. Use the commands in the Editing group on the Home tab

D. Use the commands in the Modify group on the Developer tab

..

Which of the following would allow you to insert a new column in an existing spreadsheet?

Choose one answer. A. Click in the column where you want to insert; then in the Cells group on the Home tab, click the arrow on Insert. Select Insert Sheet Columns.

B. With any cell in the worksheet selected (active), select the Insert option in the Cells group on the Home tab.

C. Click in the column where you want to insert; then in the Table group on the Insert tab, click the Table command.

D. Right click in the column where you want to insert; select Insert and then Shift Cells Right.

..

How do you adjust the column width to fit the information typed in the cells in that column?

Choose one answer. A. From Home tab, Cells group, click arrow on Format. Then select AutoFit Columns Width

B. From the Insert tab, click the Column command and choose the desired column style

C. From the Page Layout tab, click the Width menu and select the desired width

D. Right click on the column label, select Format cells and select the desired width

..

### Answer

Q1 B

Q2 A

Q3 A

### How to convert to Excel formula?

Please help, im not sure about this Excel formula.

I need to enter the following equation into the formula/function bar in Microsoft Excel but not sure if its completely right .

V = 0.000045 + 1/4p0.09^2[0.15+0.035-(0.035cos180+v( 0.15^2-0.035^2sin^2*180))]

I think i got most of it right, but dont know how to do the Pi, Cos, Sin or the square root.

### Answer

Pi is PI() in excel

So od this

... 1/4*PI()*0.09^2 ...

### Why won't Yahoo won't export contacts to .csv file?

I'm trying to create a csv file with all of my yahoo contacts. I hit the "export all" the file for a yahoo.csv, screen shows a file downloads but no contact data is included in the excel file. Its just an empty excel file that includes headers...ie first name, last name, address, email etc...but none of the contacts' data is included?

What am I doing wrong?

### Answer

Could be a prblem in that file format

Try open it in Notepad

Also, try add some more contacts then export again

Did you try another format?

### Im trying to repair my excel and word starter 2010?

When i go to programs they aren't on the list although the icon is right there on the desktop, how can i get them on the list?

### Answer

You need to use the installation CD, to re install Office Starter, or use another option called "Repair"

Otherwise, you may need to re install the whole system (Windows and everything) if Office starter came with Laptop itself

### How do i get the total of one column based off of another column in microsoft excel 2010?

I do not want to "add" the numbers. Basically i have one column of numbers lets call it column (B) and another column (C) with one character it might be an "x" and it might be a "1" anyway. I need a formula that will tell me the total number of info in column B if there is a character in column C. Is this enough info to get help?

### Answer

Yes sure

Paste this in cell in column D

=SUMIF(B:B, C:C, "X")

This one will get the total of column B, if its equivalent cell in column C is "X"

You can change "X" to be "1", or 2 , etc

Let me know

### Excel - How do I stop cell overlap?

How do stop text from overlapping onto the next cell WITHOUT using the wrap text feature? I don't want the row width to adjust like it does when it wraps text, nor do I want to manually adjust cell size. I just want to everything I type or enter to be hidden when it exceeds the standard cell size.

### Answer

You can also put an empty string (space or " ") in the cell next to it, this way, Excel will assume it has a value and stop the overlap

This is usually how I do it

### Can someone please help with this piece of visual basic excel code? 10 Points!?

Please go here for the code: http://pastebin.com/Hr4rhd3P

The reason I couldnt put it here was becasue for some reason it went past the character limit. Please could you check out my visual basic code in excel. I keep getting mainly Else without If Errors and Sin errors. Please could you go through my coding and check for the mistakes. Ty in advanced. Ten Points to best answer...

### Answer

You need to give more details on what is exactly you need to do to have your code corrected

When I went through it trying to correct it, I got lost since I don't know what you want to do

It is in sub FFactorial indeed

plus Private Sub Factorial_Click()

Both have mistakes in IF statement

email me here with more details to go through it and correct your IFs

### What Kind Of Excel Function Do I Need?

Q: In cell B6, insert a function to calculate the total number of units in the apartment complex in A6. Be sure to enter the criteria range as an absolute reference. Copy the function down through B10.

In column A there is a list of different types of apartments and I need to find total units of each apartment column B. what kind of function would I use to count and recognize each number of units for each different apartment?

### Answer

I am not sure what do you have exactly in A6, however, I am assuming only

paste this in B6

=COUNTIF( A:A, A6)

Then fill down

### Microsoft excel selection movement behavior?

in excel, there is an option to choose which direction the selection will move after pressing enter on the current cell (i.e. down, right, etc). i frequently have to change this option because i require different behaviors depending on which worksheet i am working on. is this only a global option or is it possible to set this behavior specific to a particular worksheet?

### Answer

Yes, it is global

Now there is a trick that you can use

- Select a range of cells, say B5:F12

- Now in your selection, you can edit first cell

- Then move using Enter, Shift+Enter, Tab, Shift+Tab

- Enter, moves cursor down (assuming default settings in Options)

- Shift+Enter, moves it up

- Tab, moves it to the right

- Shift+Tab, moves it to the left

- Notice that you cursor will not go out of your selection earlier

- As long as you don't use Arrow keys, this will be valid

Again , these assuming you have default settings

Bottom line, using these keys will help you decide where to go after every cell edit

I think that is the only way around your situation

let me know if it helped

### What function to use in excel?

I have a range of the first five digits of telephone numbers and I need to replace them into country name. so i need to compare very single digit of these telephone numbers with the range of prefixes i have before deciding the country name. is there any function to do that ?

I used lookup function but it compare the number exactly

example if i have the number 27800 and in my prefixes i only have 27 it will not recognize it.

### Answer

You can also still using VLOOKUP but with some modifications

Modify your list of prefixes to have the entire number with zeros

so 27 becomes 27000, 15 to become 15000, etc

here you can use the VLOOKUP with Range lookup to be TRUE, like this

=VLOOKUP( A1, D:L, 2, TRUE)

The "TRUE" will let you search for the nearest item to the searched one before it

Assuming your list of prefixes is sorted in ascendant order by prefix number

Excel help has more details about the "TRUE" in VLOOKUP

### Excel formula to convert gram to kilo?

Hi everybody ,

I would like to know if there is a formula to convert grams (example 375 gr) to kilos (0.375)

What is the easiest way to achieve that ?

Thank you very much for your help

### Answer

Say grams is in A1, in B1, paste this

=A1/1000

That is all

That would be the easiest way

### What function to use in excel?

I have a range of the first five digits of telephone numbers and I need to categorize them into country name. so i need to compare very single digit of the telephone numbers before deciding the country name. is there any function to do that ?

### Answer

Use VLOOKUP with TRUE in "range lookup" as in this question

http://answers.yahoo.com/question/index?…

You can also use other suggestions there

### Sorting in excel ROW to COLUMN?

Hi,

I have some data in a column in which i want to sort as first row in first column, second row in second column third row in first column forth row in second column fifth row in first column sixth row in second column, and so on.

How can I do this?

Can anybody help?

### Answer

I think I see what you are looking for

There is an Excel file called "2Cols to ColRow" that will convert your table

Check it out here

http://www.anmars.com/Work/?P=596630201

Note that this uses formulas

If this didn't help, please let me know, I can sure do if you send me more details

### How can I convert from pipe delimited txt file to excel file?

Then convert that excel file to rss feed?

Or am I making this more difficult than it needs to be?

### Answer

Excel already have Text two Columns wizard, however, you can use this one

http://www.anmars.com/Work/?P=589970000

It is in pure formulas and can be used to extract unlimited columns with certain delimiter

Once you copy and paste the content into column A fill the formulas down to extract them

### How do I make a input Text field in Excel 2010 and how do I extract the value of it into other formula?

I'm trying to place a text field in my excel document that would be visible and editable when saving as .PDF or .CSV

So that when users enter a Value, my tables would adjust according to what they filled in.

When I go to the Developers tab, I can't seem to select the Text field option :/

Any ideas on how to do this?

### Answer

Developers toolbar, doen't have textbox control, because you have millions of them already in the sheet

each cell can be a textbox control

You can modify the layout/format of that sheet to make certain cell looks like a textbox

Or, you can use Control Toolbox textbox

http://folder8.com/View/?7U5WAWLP8K72

Let me know if that helped or not

### How to save excel file to text file without spaces?

I have tried everything. I have coordinates in an excel file like this: x1y3 all together with no spaces and in one column. Every time I save it as a text file it comes out "x 1 y 3" I cannot have spaces or " in the file. How do I fix this other than manually? I have 800 coordinates and I'm not about to fix this by hand.

### Answer

Save it as CSV file

CSV file = Comma Separated Values

Which will put , as separator

Then you just need to drag that csv file into a notepad window and save it as txt

good luck

### WORD, POWERPOINT, EXCEL CLASSES WANTED IN BOSTON OR NEARBY!!?

Hi,

Do you know whom teaches computer classes in Boston or near by?

If you teach or if you know someone, please let me know how much you charge for your time and where you are located.

I do not have computer, only a net book, if this could be used, fine!

Please let me know if you provide computer and space or not.

Please write me back because I need to take classes ASAP.

Thank you so much!!

### Answer

I actually do these classes here in my town, and also I do them online

If you can contact me here, or through Man@VBAXLMan.com

We can discuss the online option in this case

### Refresh a read-only spreadsheet?

I have a large spreadsheet that I use for construction bids.

On bid day, my partner opens up a read-only copy so that he can follow along and help compare bid prices with what we are currently using.

Is there a way for him to refresh his read-only copy of the excel worksheet without having to save the original and then close and re-open the read-only copy?

### Answer

When you say "Refresh" you mean "Calculate", correct?

If so, then F9 will do an entire calculation for all opened workbooks

If by "Refresh" you mean "PivotTable" refresh, then each PivotTable has its own "Refresh" button

Otherwise, please advise on what "Refresh" means

### Thinner bars than default settings in excel?

I have selected the thinnest bar available in Excel. Are there ways to download a custom width that is slightly thinner that would be noticeable on a print out? I have to do some graphing and the thinnest bars are still slightly too thick.. I dont want to use dashed lines either

### Answer

As far as I understood you

You can modify the width of these bars by going Right click then selecting Format

These options can do that for you

If not, then try to change the type into 3D, then you can modify gaps too

good luck

### Please Help! How would i draw this graph on excel 2010?

http://imgur.com/IMLLE

Sorry the pic isn't rotated, and it's kinda small...

I can't figure out how to graph it:S I don't know how to insert negative x values in the graph, and i don't know how to change the default x values of 1,2,3,4, etc. to different numbers...

Please help! best answer will get 10 points today cause i'm in a ruuuush!

Thanks!

### Answer

This one looks like a SIN(x) graph for me

just do 1, 2, 3, 4, etc in col A, then do

=Sin(A1)

in column B

Then graph it

hope this is not too late

### Correct VBA Code on microsoft visual basic?

please help, it is not working. Thanks.

VBA CODE:

Sub StemAndLeaf()

dataColumn = 1

'Clean everything out of the Stem worksheet.

Worksheets("Stem").Cells.Clear

'Look at the Data worksheet.

Worksheets("Data").Activate

'Find the maximum value.

rowPointer = 2

Do Until Cells(rowPointer, 1).Value = ""

rowPointer = rowPointer + 1

Loop

Maximum = Cells(rowPointer - 1, dataColumn).Value

'Set the divisor to strip off leaves.

divisor = 1

Do Until Maximum / divisor <= 10

divisor = divisor * 10

Loop

'If the first digit of the largest value is less than 5, then

'use a smaller divisor.

'Otherwise you could end up with four or fewer rows in the plot.

If Fix(Maximum / divisor) < 5 Then divisor = divisor * 10

'Calculate the top stem's value.

topStem = Fix(Maximum / divisor)

'Set up the Stem worksheet.

Worksheets("Stem").Activate

Cells(1, 1).Value = "Count"

Cells(1, 2).Value = "Stem"

Cells(1, 3).Value = "Leaves"

For rowPointer = 2 To topStem + 2

Cells(rowPointer, 2).Value = rowPointer - 2

Cells(rowPointer, 3).Value = "|"

Next rowPointer

'Calculate the counts.

'The following code is slower than it needs to be,

'but a faster code would be harder to read and understand.

Worksheets("Data").Activate

rowPointer = 2

Do Until Cells(rowPointer, dataColumn).Value = ""

measurement = Cells(rowPointer, dataColumn).Value

Stem = Fix(measurement / divisor)

Worksheets("Stem").Cells(Stem + 2, 1).Value = Worksheets("Stem").Cells(Stem + 2, 1).Value + 1

rowPointer = rowPointer + 1

Loop

'Calculate the shrink factor.

Worksheets("Stem").Activate

maximumCount = 0

For rowPointer = 2 To topStem + 2

If Cells(rowPointer, 1).Value > maximumCount Then

maximumCount = Cells(rowPointer, 1).Value

End If

Next rowPointer

shrinkFactor = Fix(maximumCount / 50)

If shrinkFactor < 1 Then shrinkFactor = 1

Cells(1, 4).Value = "Each digit represents" + Str(shrinkFactor) + " cases."

'Return to the data, and fill the leaves in light of the values in the data.

Worksheets("Data").Activate

rowPointer = 2

Do Until Cells(rowPointer, dataColumn).Value = ""

measurement = Cells(rowPointer, dataColumn).Value

Stem = Fix(measurement / divisor)

leaf = measurement - Stem * divisor

leaf = Fix(leaf * 10 / divisor)

Worksheets("Stem").Cells(Stem + 2, 3).Value = Worksheets("Stem").Cells(Stem + 2, 3).Value + Trim(Str(leaf))

rowPointer = rowPointer + shrinkFactor

Loop

'Get to the Stem worksheet.

Worksheets("Stem").Activate

End Sub

Read more: How to Make a Stem & Leaf Plot in Excel 2007 | eHow.com http://www.ehow.com/how_6408182_make-leaf-plot-excel-2007.html#ixzz1dsvrvNfw

### Answer

What is the problem? what error code generated?

as a start, you should have two sheets named "Data" and "Stem"

let me know, I will be glad to help, contact me here

### Beginner Programming Task Questions?

Hi all,

I have next to no programming knowledge but keen to learn.

My goal is to create a simple program ~ where it goes to a website, gets certain current information, puts it into MS excel and crunches some numbers for me (few further things i'd like after).

Assuming i'm starting with nothing what should I be looking at to make this happen? Programming language (im guessing C?+?) and what do i need to create a program using C+? anything else i need to know?

Many thanks.

### Answer

Microsoft Excel itself has a programming language called (Visual Basic for Application) aka VBA or macros

I would suggest that you start with that

In addition you may need to learn some HTML top read website directly

That been said, there is a code that enables you to log in certain website and do whatever you need to do with it, like search, register, sign in, etc

Get it here as a start

http://www.access-programmers.co.uk/foru…

here

http://vba-corner.livejournal.com/4623.h…

and here

http://www.excely.com/excel-vba/ie-autom…

If you stuck somewhere you may always contact me, most of my help to others are free.

### How do you graph data on Excel using two Y axes?

I have the values for the data on the left side of the graph but I want them on the right side. When I right the axis there isn't any 'properties' but Format Axis instead. I'm using Excel 2010 by the way.. Any idea on how to get the Y axis to the right side?

### Answer

You need to do like this

http://folder8.com/View/?W0V

### Microsoft Excel Certification Question?

Is it possible to just take a certification exam for excel (offered by microsoft) and nothing else?

### Answer

Yes

There is a certificate called MOS on Excel

Microsoft Office Specialist on Excel

http://www.certification-crazy.net/mous_…

### Question about MS Excel?

How come sometimes when I double-click on a cell in sends me all the way up to the top of the spreadsheet?

### Answer

Scawny is correct, I just need to add that double click on the black border will take you to the next empty/full cell found on that direction

if you double click on the right black border of the cell, it will take you the last full cell or blank

Exactly as you do CTRL + Arrow right

CTRL + Arrow Top equals double click on the top border of a cell

CTRL + Down arrow equals to double click on the bottom border

and so on

### I can't open an Excel file in any computer, is there a way to force it open?

I worked a couple of budgets in two separate Excel spreadsheets and save them in my laptop. Now that I want to go back to them, I can't open them. Doesn't show any window or anything. The program starts but the spreadsheets do not open. Any advice please? I need that information!

### Answer

I faced that before since I was using special characters (not English) in the name

Try rename that file (and any its parents folders)

Some times an app failed to open a file because the path provided has some unexpected chars

good luck

### How do I write a formula in Excel to figure out age and then if older?

Here is my question:

How would I a write a formula that figures out someone's age and then checks if that age is greater than 55? I know the birth date but am not sure on how to formulate. Nested function, if so how?

### Answer

One if should do the trick

Assuming the birth date is on cell A1, paste this in B1

=IF( TODAY()-A1>55*356, "Older than 55", "Younger than 55")

let me know if this helped

### How to make a double line graph on Excel?

I have the temperature of the days of the week high and low how do I put that into a double bar graph?

### Answer

I have a file that does that, have it here

http://Folder8.com/F/?Yan

file called "High_Low_Temperature Chart.xls"

let me know if that helped

### How to exchange text into random number and then exchange back?

I need a code so that when I run a macro in excel it will exchange items enclosed in a parenthesis to a random number. For example the text in cell A1 is: {I have a (cat) and a (dog)}. {I have (two) cats and (three) dogs}.

What I need the macro to do is that:

1. Look at the cell and search for curly braces

2. Look for text enclosed in a parenthesis

3. Replace the text with a random number

4. Replace the number with the original text

5. Then move on to the next curly braces and repeat the process

So it would look like this:

1. {I have a (cat) and a (dog)}. {I have (two) cats and (three) dogs}.

2. {I have a (1.8) and a (3.4)}. {I have (two) cats and (three) dogs}.

3. {I have a (cat) and a (dog)}. {I have (two) cats and (three) dogs}.

4. {I have a (cat) and a (dog)}. {I have (.9) cats and (1) dogs}.

5. {I have a (cat) and a (dog)}. {I have (two) cats and (three) dogs}.

All your help will be greatly appreciated.

### Answer

Try doing something like this

Range("A1").value = Replace(Range("A1").value, "(dog)", "(" & Int( Rand()*100) & ") dogs")

This is the main command that you will be needing

You can actually repeat it to cover all options like these:

Range("A1").value = Replace(Range("A1").value, "(dog)", "(" & Int( Rand()*100) & ") dogs")

Range("A1").value = Replace(Range("A1").value, "(mouse)", "(" & Int( Rand()*100) & ") mice")

Range("A1").value = Replace(Range("A1").value, "(cat)", "(" & Int( Rand()*100) & ") cats")

And then put it inside a loop to check for them, like this

X1=1

Max1 = range("A1" ).currentregion.rows.count

do until x1>max1

Range("A1").offset(X1 ).value = Replace(Range("A1").offset(X1 ).value, "(dog)", "(" & Int( Rand()*100) & ") dogs")

x1=x1+1

loop

let me know if you need more help

### How to assign a random number as an id to a marked text in a cell in excel?

What code can I use so that I can assign a random number as a the ID for a text in a cell. For example cell A1 has these sentences:

{I have (three) dogs and (two) cats}. My pets always fight. {My (dog) is afraid of my (cat) and they are both afraid of (mice)}.

I need an excel macro code so that it will assign a random number as an ID for the items enclosed in the parenthesis. I need this macro to assign a random number ID one at a time. In other words what I want the macro to do is to:

1. Search for curly braces in the cell

2. Get all the items enclosed in the curly braces that are enclosed in parenthesis

3. Assign a random number as an ID to the items in the parenthesis without touching the other text

4. Then move on to the next text enclosed in curly braces and continue until there are no more curly braces in the cell

So what the macro will do is that it will look at the contents of the selected cell. Then look for curly braces then look for words that are enclosed in parenthesis and then assign a random number as an ID for each then move on to the next curly braces.

The curly braces and the parenthesis may be changed to any marker.

All help is greatly appreciated.

### Answer

The main command you might need is:

Range("A1").value = Replace(Range("A1").value, "(dog)", "(" & Int( Rand()*100) & ") dogs")

Doing it repeatedly is the trick that you will be using to replace all options once found, note that "Replace" works only if that text is found

And then put it inside a loop to check for them, like this

X1=1

Max1 = range("A1" ).currentregion.rows.count

do until x1>max1

Range("A1").offset(X1 ).value = Replace(Range("A1").offset(X1 ).value, "(dog)", "(" & Int( Rand()*100) & ") dogs")

Range("A1").offset(X1 ).value = Replace(Range("A1").offset(X1 ).value, "(cat)", "(" & Int( Rand()*100) & ") cats")

Range("A1").offset(X1 ).value = Replace(Range("A1").offset(X1 ).value, "(mouse)", "(" & Int( Rand()*100) & ") mice")

x1=x1+1

loop

let me know if you need more help

### Simple financing in Excel for personal business?

Hey,

I've looked online for templates and such but didn't really come up with much.

My small business is in Repairs. So all I really need is an Excel spreadsheet (I have windows excel home and student 2010)

Could someone explain how i could make one with some basic formula.

Basically, I need a column for costs such as parts, and then the final invoice bill. And hopefully i can have a box to the side that will work out the final balance for that month/year.

Thanks,

~D

### Answer

I have a very simple sheet that should do exactly what you are looking for

You can download it for free here

http://folder8.com/F/?Yan

Called "SimpleANBox" in 2007/2010 format

### Ofiice Word and Excel documents icons are blank in Windows 7?

I had MS Office Starter(with only Word and Excel in it), I uninstalled it, and installed MS Office 2010 Professional Plus. The documents are opening good, but icons are blank... how to fix this, its annoying... Please help... Deleting Icon Cache didn't help... And EVEN System Restore won't help...

### Answer

I would suggest that you run Office installation setup again and select "Fix installation" or may be called "Repair"

This might generally fix it.

Otherwise, you can go a head and clear the temporary files and internet cache, these sometimes do some interesting errors like yours

good luck

### How do you insert external data into excel STARTER?

This question is specifically for Microsoft Excel STARTER. It's not like on other versions of excel where you have a data tab and can follow the steps from there.

I have already checked google also so please don't suggest pages on their or searching it on google. I have also checked the microsoft website.

BASICALLY, if you personally know how you insert data into Microsoft Excel STARTER I would really appreciate the steps on how to do it :) And will rate best answer for whoever tells me. Thanks

### Answer

There are actually several ways, basically using VBA or macros

If you know how to do that there are several macros out there

I would suggest one of these

http://www.anmars.com/Work/?P=598866300

to read directly from MySQL DB into Excel sheet

or use

http://social.msdn.microsoft.com/Forums/…

to read from sql or Access

Also

http://www.bygsoftware.com/Excel/SQL/Usi…

Again, those are macros (Using the back door for Excel) to insert these data

If none of them helped, let me know from where are you inserting the data and I would be glad to show you the way.

let me know if that helped

### How to get an ms- excel formula to get the nos in the months col?

There are 100 apples. i need to give it to 2 ppl, every month. Person 1 is the 1st pref. he has to exhaust his quota of 80 apples, only then i can give the rest to person 2, whose quota is 20 apples. Pls help me put a formula in excel. Thanks in advancce

Total apples 100

Quota of Apples for Person 1 80

Quota of Apples for Person 2 20

Sub1 Sub 2

Apples given in Month 1 15 0

Apples given in Month 2 15 0

Apples given in Month 3 15 0

Apples given in Month 4 15 0

Apples given in Month 5 15 0

Apples given in Month 6 5 10

Apples given in Month 7 0 10

### Answer

You just need to have the table set up correctly

first: put the total number of apples in certain column (Say E starting from E4)

Then in F4 paste this to get apples for person 1

=IF(E4 > 80, 80, E4)

Then in G4 to get apples for person 2

=IF(E4>80, E4-F4, 0)

Then fill those down to cover other months

let me know if that make sense or not

### Is there a way to change the auto-assigned value of empty cells in excel?

In excel, empty cells are treated as though their numeric value was 0, and as though they had no text value.

I'm using a custom function that runs through a range of data (which contains 0 values) and gives me a result based on the range's findings.

Currently, there are no blank cells before the end the range. Therefore, I can use CountA(range) to tell my function how many times to iterate (i.e. to continue until it reaches the end of the range).

I wish to instead specify to the program that it must continue to the end of the range (another problem) and ignore blank spaces.

Rather than re-writing all of my functions to ignore these empty cells, is there an excel option that will allow me to force excel to treat empty cells as a value other than 0?

### Answer

If I got you correctly you can use the formula

ISBLANK()

When you put it inside IF, you will will get if the cell is blank

CountA will not count blank cells either

let me know if that solves it or not

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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…

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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…

### 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

### 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

### 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

### 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

### 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

### 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

### [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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### 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

### Need to create an Excel file containing test questions and answers.?

We are in the process of automating a test. I need to create an Excel file that contains the questions, possible answer(s), correct answer(s), and some other things. For each question, I need to know how to input the possible and correct answer(s). Do I put each answer in a different field or in the same field. I'm not sure what type of database will be used for the project, but I know I can create the CSV in Excel. Any help would be appreciated.

### Answer

I like the idea

Now,

In a new sheet, put the fields like this

QID, Question body, Correct answer, Answer1, Answer2, etc, thing1, thing2, etc

This could be the easiest way

Means that cells A1 to H1 will have these heads

in the cells below A2 to H2 this can be

1, What is your name?, Name1, Name2, Name3, Name4, Free1, Free2, Free3

2, How old are you?, 15, 16, 17, 18, free1, free2, free3

and so on

Enjoy my profile, I am the VBAXLMan

### How to run my macros in excel?

how to run my macros in excel

before allowing users to view the worksheet ? as in I want the macro for "inputbox Username" to pop up as soon as someone opens the workbook

### Answer

Yes, it is Workbook_Open() that you need to put your code in to be started as the user opens the file

BUT

The only thing that you may face, is that user might have his macros are disabled, then all your work will be gone.

I faced this issue 7 years ago, and I came out with a good solution.

I will recommand you to use my tool, that will show the user a window asking him to enable his macros to start running.

It is here if you are interesting (It is called "Always Visible Sheet", first project in 2006 projects here)

http://www.projects.file1.net

I am thinking of making it free to download...

If you like it? I can send you a free copy, mail me for it

Enjoy my profile, I am the VBAXLMan

### I need help on excel?

guys i need help on how to calculate the average of the columns... one of the colums is worth 40% of the grade and the other one is worth 60%. now how do i find my overall average?? HELLPPPPP!!!

### Answer

That answer is missing the main issue

Now, lets say that your columns are D with 40%, E with 60%

In F1 paste this function

=D1 + E1

to get the 100%

Now in G1 paste this

=AVERAGE( F:F)

Enjoy my profile, I am the VBAXLMan

### Microsoft Excel: How to show picture if conditions met?

Hi there,

Is it possible to use some kind of formula to show a picture (which is located on another sheet) if specific conditions are met?

For example: I would like to show a green sign if a value is higher than 100 and a red sign if a value is below 100.

Thanks.

Stefan.

### Answer

If Conditional Formatting is not enough for you, (It can color the cell based on condition)

Then, it is a VBA

You will need to create a picture image from

Tools > Customize > Control toolbox

and add some macro to load an image based on a cell value

I will be glad to help, if you mail me here in Y! Answers

Enjoy my profile, I am the VBAXLMan

### How to create excel macro to fill in empty cells based on information in abolve cell?

I am looking to write macro that fills in the empty cells in certain range based on info of above info. Idea is to populate cells that have been left empty after pivot table results are copied as value in other worksheet.

It should execute starting from top and going down row by row in selected range.

Example: Row 1 has text "Formula", row two has empty cell. If macro detects empty cell it should populate it with info from cell above (e.g. Formula). It is kind of auto copy function

### Answer

It is amacro, right?

So, you will need to create two loops and check if a cell is empty, if so, fill the cell from the above cell, then:

Sub FillFromAbove()

For i=1 to 4 ' column A, B, C and D

For j=2 to 7 ' rows 2, 3, 4, 5, 6 and 7 , so the range is A2:D7

If Worksheets( "Sheet1").Range( "A1").Offset( j-1, i-1).Value ="" then Worksheets( "Sheet1").Range( "A1").Offset( j-1, i-1).Value = Worksheets( "Sheet1").Range( "A1").Offset( j-2, i-1).Value ' to fill from the above cell

Next j

Next i

End Sub

Wow, I am realy the VBAXLMan

### How do you conditional format a hyperlink in excel?

trying to hide links where data doesnt exist yet with white font

### Answer

You will need to use HYPERLINK function, (Not Insert Hyperlink)

along with IF function.

It is somthing like that

=IF( A1=1, "", HYPERLINK( "destination"))

So when cell A1 equals 1, then this cell will show nothing, but when A1 equals anything but 1, this cell will show the hyperlink you specify

Enjoy my profile, I am the VBAXLMan

### What are the kinds and data of excel?

What are the kinds and data of excel?

### Answer

I think you mean what is the types of entry in an Excel cell.

If so, then

1- Numerals, that is when you enter value of 15 in one cell

2- Texts, when you type your name in a cell

3- Date, (Actually date is saved as numeric values)

4- TRUE, or FALSE

5- Function, when you press the equal sign '=' then entyer your formula, like =1600/15 or =A1/A2

6- Error, When a formula (or an entry) generates an error.

Enjoy my profile, I am the VBAXLMan

### Less Excel spreadsheets?

I have 2 excel spreadsheets containing tables that i'd like to print. Is there any way of putting the two pages on one? I know i have to make the tables smaller in order to do so, but is there a way to do this all automatically?

### Answer

If you want to print two tables in two worksheets in one page, just forget it, you can not

IF you want to print two tables in one worksheet in one page automatically, you will need to use the macro.

I don't think there are here better than the VBAXLMan that will do this for you.

Mail me here in Y! Answers some more details to do this for you

Enjoy my profile, I am the VBAXLMan

### Excel setting??

in Excel work sheet, how do i create a drop down button that contains options to choose from?

### Answer

You have three ways

1- Is as the above answerer is suggestion (Although list in another sheet will work by naming that range using (Insert > Name > Define)), any way..

2- You can create a dorp-down combo by selecting the drop-down object from (View > Toolbars > Forms) (also Called Forms toolbar), then right click to go to its properties and assign cells to be the list and a cell to give you the item seleted.

3- You can do it in VBA of Excel, by showing the Control Toolbox toolbar, but this one need to controlled by the VBA in Excel not in Excel itself

Personally, I recommand you to use the second option, it is joyfull and almost programming

mail me here in Y! Answers any other details you want to know

Enjoy my profile, I am the VBAXLMan

### How can I??.....?

I'm creating a calendar on excel ...how would it be possible to add a background picture to the calendar? .....how do i do it step by step? can anyone please help me??

### Answer

You can

1- Put the picture as in (Format > Sheet > Background) and then remove the gridlines from (Tools > Options > Show gridlines)

2- Make the calendar itself as a picture using the camera option in Excel. (You will need to mail me for this help file, I can't attach files here)

In all of these ways, you can use my calendar that I would be proud to give freely, it works for all years all months

take it from the free applications section here

http://shiekhali.com/anmar/projects

Enjoy my profile, I am the VBAXLMan

### Excel formula to make input number its inverse?

I want to be able to input a number in a cell and automatically have it convert to its inverse (in that same cell). Is there a forumla for that?

### Answer

No formula can do this.

Formulas are have to be entered in a cell first to work.

So You will need a VBA to do this

put this VBA macro in the code area of any sheet you want to do this:

Private Sub Worksheet_Change(ByVal Target As Range)

ActiveCell.Value = 0 - Val(ActiveCell.Value)

End Sub

need more info? mail me here in Y! Answers

Enjoy my profile, I am the VBAXLMan

### How do you stop your pull down menus from scrolling?

I don't like the way the pull down menus have the unused ones hidden and i would like them all to be on. How do I fox that? I don't like turning them on manually. I am talking in Microsoft Word and Excel and the Start menu...thanks!

### Answer

- In Excel and Word (All Microsoft Office applications) go to

Tools > Customize > Options

and check the option that says "Always show full menus"

- In Windows Start Menu go to

Control Panel > Task bar and start menu > Start menu > Customize (For Classic Start Menu) and scroll down to the last option "Use Personalixed Menus" and uncheck it

Enjoy my profile, I am the VBAXLMan

### Matching with Ms Excel?

I have a spreadsheet which contains 5-6 columns of info, with about 200-300 rows. Column D contains totals. Some of the totals in column D will match with one another, just with a minus sign. Say if there is a 54300, then there is another -54300, just its with a minus sign (this is actually a ballance of a clearing account).

What I want is to write a formula or macro that would take column D value from the first row, search through all column D, find matching value, cut those two rows and paste in another sheet within the same file. Then repeat this with all the rows, cutting rows with matching value in column D, so that all the matching values are pasted one below another in another sheet. This way the original sheet will only contain unmatched items.

I work as an accountant so have to go through account statements like that very frequently. Having a formula or a macro like that would make my life a lot easier :)

Thanks for any responses!

Happy Xmas!

### Answer

You can solve it in one of these ways

1- Formula

2- Macro

I can post here the formula solution, because it is easier to understand.

Now, leave column E as seperator, go to F2 and paste this

=ABS( D2)

and fill it down to all the other cells. In G2, paste this

=LARGE( F:F, ROW()-1)

And fill the G2 down to all the cells below

Hay, it is your exact list, but sorted in Descending order

Yes

That is because VBAXLMan show you how to do it

You can cut and paste this G column cells to what ever you want

Enjoy it and..

Enjoy my profile, I am the VBAXLMan

### What is the best mobile phone to buy supporting word docs, excel and email?

I've been told that Qtek 9000 is the best but after 2 or 3 months it starts failling. Can anyone give me advise/suggestions?

Cheers

### Answer

If you want a cell phone wiht big screen, qwerty keyboard, wireless network, bluetooth.

Then The best one I found until now is Siemens SX66

I hav'nt seen such a big screen like this ever

VBAXLMan

### I have a problem in writing Excel formula?

Hi guys,

I have a problem in writing an excel formula .. IT looks very simple.. I wanna say that if the value in a specific cell is less than 1000, add 30 ..

I have tried =if(K5<1000,"30").. it never work

### Answer

Yes do it like this

=IF( A1<1000, A1 + 30, A1)

This will add 30 to the cell in A1 if it is less than 1000 and bring the origin cell value if not

Enjoy my profile, I am the VBAXLMan

### I have data in a column in an Excel spreadsheet and I need to use it in formulae in a row on another sheet.?

I don't want to have to type the formula over and over for each cell! Is this possible? Using the '$' sign doesn't work...

### Answer

You can use

1- The Copy + Paste Special (Transpose)

2- Use the COLUMN(), ADDRESS functions like this:

Your source column is in D in Sheet1, you want it in row 5 in sheet2, so in cell A5 in sheet2 paste this

=INDIRECT( "Sheet1!D"& COLUMN())

the function in cell A5 will call the value in cell D1 in sheet1 and so on.

You can change the sheet name of the source column by changing Sheet1 in the formula.

If you are not starting from D1, or you want the row to start from other than A5 then add the -1, +1 after the COLUMN() to modify it

3- Use the TRANSPOSE function

I think this will answer you

Enjoy my profile, I am the VBAXLMan

### Excel Spreadsheet opens up blank?

Coworker wanted me to double check her work and when I open the spreadsheet it comes up blank (just gray) not even showing the columns and rows. But looking at full screen I can see it. All other excel spreadsheets are fine.

### Answer

There are alot of tricks in Excel to do this, I just need you to check these one after another

1- Go to (Window > Unhide) and see if there are items shown in the dialog that appears

2- Go to (Tools > Options > Window options) and make sure you select (Row and column headers, gridlines, horizontal scroll bar, vertical scroll bar)

3- got to (Format > Sheet > Unhide) and check the hidden sheets

PS: If excel asked for password, then the file is protected, you will need it

Enjoy my profile, I am the VBAXLMan

### Is there any formula to convert numbers to words in "Excel Program" other than Add-ins. Pls answer me.

### Answer

Microsoft do have a function like this, it is a macro, so you may need to copy it first to your VBE module window

Check this out

http://www.ozgrid.com/VBA/CurrencyToWord…

Enjoy my profile, I am the VBAXLMan

### MS Excel fomular for link sht?

=SUMIF(INDRECT(C$4&"!$A:$A"),$6,INDIRECT…

An expert told me to use the above fomular to link 3 work sht into one main sht for summary purpose. I can't contact him now.

Can anyone tell me :

1) How to COPY the data in the 3 work sht and use the above fomular to pPASTE it on the main sht.

2) please explain the meaning of the fomular in layman term.

Thank you very much for help.

WT Tan

### Answer

Hello my friend, it is me who send you the formula.

Now what is the problem?

mail me

VBAXLMan

### How to convert web files in excel?

or how to covert txt file from dos to excel

### Answer

Excel can read *.csv files

So just make your list a comma seperated list, like

V1,V2,F4,450

and so on, then

open the file in Excel, and it is all here in a sible sheet

Enjoy my profile, I am the VBAXLMan

### Hey, Excel users?

What has happened to my Excel file?

I suddenly can't enter any data. The damned cursor moves where ever it wants to and won't accept an entry in the selected cell.

No idea what question to ask in Excel Help.

Thanks.

### Answer

Mske sure that

1- Your sheet is not protected against changing (See Tools > Protection > Protect worksheet)

2- You are not openning a shared file that is already opened by someone else

3- You are not openening a file that is accedently closed

4- Copy the file in another folder with another name and try again

5- You don't have a virus in that particular file

mail me for any details here

Enjoy my profile, I am the VBAXLMan

### Where can I find an excel spreadsheet of question and answers?

I am looking for question and answers for students for basic tests and exams for things like chemistry, geology, science. Just basic stuff like:

What is oxygen? An element that makes up our atmosphere.

I have many text files of info but they are taking for ever to manually copy/ paste into Excel (which is what my test creation program imports from).

### Answer

I made somthing like this (Based on text files database) that will read these files and make the 10 questions with the 3 random ordered choices in VBA of Powerpoint, check this link

http://shiekhali.com/anmar/projects.html

and go down until the "Ayad Project" in 2002 and see the screenshots for it.

It is VBA in PowerPoint that will calculate the result of points, I can mail it to you and show you how to customize it if you are interesting, mail me here in Y! Answers

Enjoy my profile, I am the VBAXLMan

### How do I get file attributes such as picture size type etc into an excel database using Visual Basic 6.0?

I'm making and index of my CD's in Excel and Everytime I get a new CD I need to right click and get the properties and copy them in excel. Thats why I want to make an application in VB which will do the job for me.

### Answer

I would rather do the program in VBA of Excel, it is close to plain VB, besides you don't need a setup, just open the Excel file and run the macro (VBA subroutine)

Now the answer is using a special DLL to get the picture properties, there are alot of libraries sites out there in the net like:

vbaccelerator.com

a1vbcode.com

and much much more, i don't remeber all now.

If you got the declerations part, you can easily use it in the VBA of Excel

mail me for more info, i would be glad to help

Enjoy my profile, I am the VBAXLMan

### This is regarding Concatenate function in Excel:?

I am trying to concatenate a Currency value ( $ 233,456,389) with a

percentage ( 74 %) in Excel using this function :

=CONCATENATE("$ ", TRUNC(B313)," @ ",TRUNC(C314)," % ")

I would like to have it as $ 233,456,389 @ 74%

Instread I am getting $ 233456389 @ 74% (currency value without commas)

Can someone help me in getting the commas in the currency display ?

### Answer

Add this to the TRUNC function

TEXT(B313 ,"###,###")

Means the TRUNC would be

TRUNC(TEXT( B313, "###,###"))

and your final function will be

=CONCATENATE("$ ", TRUNC(TEXT( B313, "###,###"))," @ ",TRUNC(C314)," % ")

Enjoy my profile, I am the VBAXLMan

### In the Excel Sheet(Microsoft Excel),I am not getting the 'FILE'option in the menu bar.How can i restore it?.

The excel sheet is starting with 'EDIT' from the left side.

### Answer

The easy way is as follow:

- Go to View > Toolbars > Customize

- in that window make sure you are in the tab that named "Toolbars"

- Select "Worksheet Menu Bar" which is the last one in that list of toolbars

- Now press the "Reset" at the right

- Enjoy it

Enjoy my profile, I am the VBAXLMan

### I am not able to tick the 'chart menu bar' in ('tools'-'customise')Excel sheet.How it is possible?

This is to bring back the 'file' option in menu in Excel Sheet.Presently it is not displayed.Display starts from 'EDIT'

### Answer

I answered you,

Check the answer for this question in this link

http://answers.yahoo.com/question/index?…

Enjot mt profile, I am the VBAXLMan

### Excel Macros help!!?

If you have the following macro:

Sub CleanupGridReport_1()

'

' CleanupGridReport_1 Macro

' Macro recorded 12/6/2006 by me

'

'

Sheets("GridReport_1").Select

Range("V22:V29,Y22:Y29,a33:d33D32").Sele…

Selection.ClearContents

Range("I32").Select

End Sub

Can someone tell me what are the steps I have to follow, starting from >>

Sheets("Grid_Report_1")

So, I start the macro, then what do I do?

### Answer

Line 1: Sheets("GridReport_1").Select

Is going to select the sheet names GridReport_1

Line 2: Range("V22:V29,Y22:Y29,a33:d33...

Is to select these ranges of cells at the same time (Highlight them)

Line 3: Selection.ClearContents

Will clears the content of the selection (Contents means only whats inside the cell, not Formats or commints

Line 4: Range("I32").Select

Selects the cell I32

Thats it

good luck

Enjoy my profile, I am the VBAXLMan

### Hey! is there any Excel expert who can solve this.....? are u here Mr VBAXLMAN..?.?

i would like to create a line chart in ms excel....where "runs" will be ploted in y axis, or vertical axis and "overs" in horizontal axis....

runs will ploted in 50 runs interval and overs will be in 10 overs interval....u might have seen this in cricket...

wait .....it's not so easy one....i've a few conditions

1) the resultant line will be displayed in a single line,,,,i repeat ..in a single line....as u would have noticed in cricket score analysis.

2)it will start from the (0,0) point...that means 0 run at 0 over...

and will travel with the increasing runs and respected overs...(as it is made in graph papers.)

3)i'm using msexcel 2002....so pls solve it in 2002 or older....

i think i've expressed it enough....

if u 've done it then try this for two teams for there score analysis .....obviously there will two seperate line for two teams , say India & Pakistan or Australia & NewZealand...

every good answer will get a thumbs up and the best answer ......u know better

### Answer

I got your e-mail.

The solution is all in the table that you will create for it

Wherever hard you think the chart is, the solution is in the table

Excel charts are created from tables, means that you can not (Never) create a chart without its data.

So we are (You and me) have to see how to build the table that will represent the chart

I can't e-mail you through Yahoo, you have not confirmed your e-mail there...

send me your e-mail address, I can not e-mail you

Enjoy my profile, I am the VBAXLMan

### How do you generate a PO number in an excel spreadsheet?

I have a purchase order form in excel that I want to automatically generate a purchase order number every time it is opened. Is this at all possible and if so how would I do it?

### Answer

It is in macro (Did you heared of VBA?)

You will need to create the macro that will run at each start and adding new value to the old one.

It is something like this

Paste these lines in the ThisWorkbook object in your VBA window (Open excel, then press ALT+F11)

Private Sub Workbook_Open()

Starting

End Sub

Sub Starting()

Sheet1.Range("B1").Value = VAL(Sheet1.Range("B1").Value) +1

End Sub

Assuming that the cell B1 in sheet "Sheet1 is the cell with PO number

mail me for more info

Enjoy my profile, I am the VBAXLMan

### Excel Query: Closing Down the file you are in using a macro?

Hi,

Can anyone help?

I have an excel spreadsheet with various macro buttons attached. What I need is a piece of additional code where, once entered straight into vb, and one of the macro buttons is pressed, the file that they are in closes.

Thanks in advance

### Answer

Closing Excel file has some details

1- Closing the file (the file that have the macro) is in this line

ThisWorkbook.Close TRUE

TRUE for saving file before close

2- Closing the Active file, the one that is selected in the Window menu is

ActiveWorkbook.Close TRUE

3- Closing Active window will not all times closes the file (You may have two windows for the same file) it is like this

ActiveWindow.Close

4- Closing Excel is another issue, you will need to save all opened file to let Excel close without asking for saving files:

- If you have one file opened, save it then exit Excel, like this

ThisWorkbook.Save

Application.Quit

- It you have more than one file opened, then you can make a loop to save all opened files, like this

For i= Workbooks.Count to 1 step -1

Workbooks( i ).save

Next i

Application.Quit

I think this will solve your issue

Enjoy my profile, I am the VBAXLMan

### An Excel search question?

I am trying to make an excel worksheet simply count the number of each persons names in a, say 10x10 grid randomly filled with names. Can it be done? And how!? I just want a list underneath of, for example "John - 10; Dave - 3; Kim - 28" depending on how many boxes each person's name appears in. I have tried DCOUNT but probably incorrectly, as it brought back totally random figures that dont make sense. Please help!!

### Answer

You will need COUNTIF

it goes like this

=COUNTIF( A1:F10, "*John*")

I recommand using * to allow finding the cell with John, and cell with John - 10

Enjoy my profile, I am the VBAXLMan

### I have an excel file and I have forgotten the password. Is there a way to recover the password.?

I have checked the crack softwares on the internet. I am not ready to pay $40 for just 1 file. But this file contains some important tax info and I need to make some changes urgently. Is there any way I get recover the password.

### Answer

I have one and ready to share it

I just want to ask, which password you are talking about

Excel do have at least three password levels

mail me here for it

Enjoy my profile, I am the VBAXLMan

### How to find the largest non-empty row of an Excel column with VBA code?

### Answer

I guess you mean the largest value in a range of cells, right?

If so, then you can do one of the following:

1- Use another cell with the function of

=Max( B:B)

then call that cell using the object [Range]

MaximumValue = Range("A1").Value

2- Use the powerfull feature WorksheetFunction that will enables you to use some of Excel built-in functions in VBA, example:

MaximumValue = WorksheetFunction.Max( "Sheet1$B:$B")

Or...

You may want to find which row have the largest no of non-empty cells.

If so, then you will need som fomrula combination, I will need you to mail me here if you are looking for that in particular.

I also will need you to answer some questions like (Do these rows are one after one? means they don't have empty rows in between?)

mail me here for any further info

Enjoy my profile, I am the VBAXLMan

### In excel,when I try to enter formula it is not applied on the cell.Pls suggest me?

Excel

### Answer

Sometimes, Excel do autoformat to the result if one of the references are date or if the result can be converted to a date, so just go to Format--> Cells and make sure in the Number tab the Number value are selected from the list.