ANmarS - Search results

Excel/VBA expert since Sep 1997 (20 years 9 months)

We are moving ...

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

12 Q & A posts tagged with auto

Dec 12

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


Q1 B
Q2 A
Q3 A

Monday 12/12/2011 1:15:48 AM
May 11

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.


It looks like you have custom external links,
something like this…
This lesson will show you how to get values from several sheets

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


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

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


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
or even like this


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

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.


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


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

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?


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
Then copy it down to get the length of all cells characters

Then do a regular SUM on column D

Hope this will help


Sunday 5/2/2010 6:53:36 AM
Apr 2

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!


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


Friday 4/2/2010 2:59:51 PM
Apr 26

In Microsoft Excel, how does protecting a worksheet affect the autofilter function?


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


Sunday 4/26/2009 4:36:54 PM
Apr 25

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!


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…

Option 4:
Use some macros to do that

Option 4 requires some work and sample files, reply me here if you are interested


Saturday 4/25/2009 7:05:09 AM
Apr 21

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?


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…
3- Make sure you have the Print scale to 100%


Tuesday 4/21/2009 7:22:25 AM
Apr 9

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


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…

Then do the SUM using the same context
=SUM( INDIRECT( bla bla bla))

Lets assume you have the sheet name in cell A1, and you want to sum the row 1 from that sheet.
So paste this in cell B1
=SUM( INDIRECT( A1&"!"&ROW()&":"&ROW() )

Then copy it down to cell B2, B3 and B4 to make these cells get the total of the corresponding rows from the sheets in the range A1, A2, A3 and A4 respectively

Hope this helped

If not, mail me here

VBAXLMan is here to fill your Excel needs

Thursday 4/9/2009 2:01:12 PM
Apr 6

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


Autofilter requires no empty spaces between rows
That could be the reason

Make sure that you disable / enable the Auto filter


Monday 4/6/2009 7:58:05 AM
Jan 18

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

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.


Any help appreciated.


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