ANmarS - Search results

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

We are moving ...

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

56 Q & A posts found in January

Wed
Jan 21
2009

I have no space in my local disk? Help?

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

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

How do i use this memory?


Answer

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

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

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

So you can make use of it

VBAXLMan

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

Searching across sheets in Excel?

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


Answer

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

Enjoy it

VBAXLMan is here to feed your Excel needs

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

Windows Dvd Maker & Movie maker continuously shutting down?

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

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


Answer

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

Good luck with that

VBAXLMan

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

What are the available Note-Taking Apps for Windows?

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


Answer

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

VBAXLMan is back

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

Urgent! MS Excel Macro help needed!?

Hello

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

Any tips or code on how I can accomplish this?

Thank you very much.


Answer

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

Enjoy it, it is a powerful feature

VBAXLMan is back

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

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

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


Answer

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

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

Enjoy my profile, VBAXLMan is back

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

Excel Formula - I need help!?

Hi,

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

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

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

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

Thanks!


Answer

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

And copy it down to the end of your list

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

VBAXLMan is back

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

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


Answer

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

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

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

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

VBAXLMan

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

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

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


Answer

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

It is free, try it

VBAXLMan

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

Microsoft Paint - custom sizing printing?

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


Answer

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

VBAXLMan

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

Excel 2007 document protection & locked cells?

Hi,

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

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

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

Many Thanks,
Mark


Answer

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

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

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

Enjoy my profile, I am the VBAXLMan here

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

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

Hi,

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

Thanks


Answer

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

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

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

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

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

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

VBAXLMan is back to feed your Excel needs

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

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

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


Answer

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

do the same for the other 3 tables (folders)

VBAXLMan is back

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

Get answers from millions of real people.?

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


Answer

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

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

VBAXLMan is here again to feed your Excel needs

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

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


Answer

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

this will give column B 0 if column A is zero

VBAXLMan

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

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

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


Answer

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

VBAXLMan is back

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

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

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

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

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

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

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

Set rst = Nothing
Set con = Nothing

End If
End Sub


Answer

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

VBAXLMan

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

Can someone help me with excel?

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

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

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



thankyouuuuu ! (:
17


Answer

Part one:
1- true
2- false

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

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

VBAXLMan

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

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

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


Answer

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

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

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

good luck

VBAXLMan

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

How do you print comments on microsoft excel 2007?

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


Answer

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

VBAXLMan is back

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

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

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

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

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

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


Answer

Hmmmm
You need to do these steps as they are the most popular reasons for slowing down
1- Clear history and cookies
2- Delete temporary internet files
Both 1 and 2 are in Internet options from Control panel
3- Delete "Temp" folder content (and not the folder), it is in your C: > documents and settings > YOURUSERNAME > Local settings > Temp, this will solve a lot of problems
4- Restart computer and try again

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


VBAXLMan

Monday 1/19/2009 4:53:33 PM
Mon
Jan 19
2009

Excel % formula non-blanks?

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


Answer

Sure, COUNTA will do it for you
like this

=COUNTA( A:A)/735

Format that cell as Percentage to get the %

VBAXLMan is back

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

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

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

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


Answer

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

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

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

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

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

VBAXLMan is back to feed your Excel needs

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

Nesting IF and SUMIF?

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

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


Answer

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

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

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

Do the changes as your table and enjoy it

VBAXLMan is back to feed you Excel needs

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

Can you help me with my Excel question?

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

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

and so on, and so on...

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

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



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


Answer

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

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

then copy those three down

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

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

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

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

My formula looks like this

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

I want it to be

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

etc.

Help will be more appreciated.


Answer

Sure, do it like this

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

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

And fill it down

Yes, I know, I am the VBAXLMan here

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

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

Excel Formula


Answer

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

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

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

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

VBAXLMan is back

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

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

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


Answer

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

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

VBAXLMan is here to feed your Excel needs

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

Why can i not open some files?

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


Answer

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

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

good luck

VBAXLMan

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

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

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

Any help appreciated.

MGB

Any help appreciated.


Answer

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

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

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

VBAXLMan is back to feed your Excel needs

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

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

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


Answer

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

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

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

Then in another cell, say C1 type 5

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

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

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

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

try applying your criteria and see

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

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

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


Answer

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

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

VBAXLMan

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

Students often tell me that they use excel to figure ?

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

How might you do this?


Answer

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

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

Try it, it is interesting feature

VBAXLMan

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

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


Answer

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

This is the fast solution

VBAXLMan

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

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

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


Answer

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

otherwise you need to know what format your phone accepts

VBAXLMan

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

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

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


Answer

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

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

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

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

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

VBAXLMan

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

How can I sort a ROW alphabetically in Excel 2000?

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


Answer

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

That is after you made the proper selection


VBAXLMan is back

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

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

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


Answer

Right click on the Task bar and select Tile

Or Arrange depending on the OS you have and the version

VBAXLMan is back

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

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

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

any help ??


Answer

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

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

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

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

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


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

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

Getting an equation from points on excel 2007 ?!?

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


Answer

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

And do your titles here

VBAXLMan is back

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

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


Answer

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


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

VBAXLMan is back

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

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

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


Answer

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

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

VBAXLMan

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

2 questions regarding memory stick and excel file?

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

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


Answer

Sounds like a virus to me

Make sure you have a good antivirus

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

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

VBAXLMan

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

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

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


Answer

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

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

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

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

VBAXLMan is back to feed your Excel needs

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

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

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


Answer

I did
1234567890 1234567890
And Excel converts it into
1234567890 1234500000

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

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


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

VBAXLMan is back

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

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

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


Answer

Yes, using macros, for example:

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

And put this in a new Module after you insert it

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

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

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

VBAXLMan is back to feed your Excel needs

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

Excel, Update sheets automatically?

Hi,

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

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

Thanks.


Answer

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

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

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

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

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

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

VBAXLMan

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

How to build loops in excel?


Answer

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

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

VBAXLMan

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

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


Answer

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

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

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

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

good luck

VBAXLMan is back

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

I need excel spreadsheet help for my new company?

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

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

Thanks!


Answer

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

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

and this in F4
=D4*E4

That is all

VBAXLMan is back to feed your Excel needs

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

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

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


Answer

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

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

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

I can not see any other way

VBAXLMan

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

I really need help with Excel.?

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


Answer

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

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

VBAXLMan

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

Ms Excel macros problem ??? ?

it give the error

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

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

what can i do ???

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


Answer

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

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

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

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

Click "Enable macros"

VBAXLMan

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

Problem opening Excel files after crash?

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


Answer

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

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

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

VBAXLMan

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

Can i back calculate a formula using excel?

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

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


Answer

Your formula is cut

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

VBAXLMan

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

How to view more columns in Excel 2007?

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


Answer

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

VBAXLMan is back

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

Tags

History