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 found in year 2007

Wed
Jan 24
2007

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

Wednesday 1/24/2007 8:48:19 AM
Wed
Jan 24
2007

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

Wednesday 1/24/2007 4:12:51 AM
Wed
Jan 24
2007

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

Wednesday 1/24/2007 3:38:57 AM
Thu
Jan 18
2007

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

Thursday 1/18/2007 11:01:39 AM
Thu
Jan 18
2007

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

Thursday 1/18/2007 8:52:04 AM
Thu
Jan 18
2007

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

Thursday 1/18/2007 7:59:31 AM
Mon
Jan 8
2007

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

Monday 1/8/2007 11:11:58 AM
Mon
Jan 8
2007

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

Monday 1/8/2007 9:52:52 AM
Mon
Jan 8
2007

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

Monday 1/8/2007 5:51:56 AM
Mon
Jan 8
2007

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

Monday 1/8/2007 12:55:55 AM
Sun
Jan 7
2007

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

Sunday 1/7/2007 9:07:50 PM
Sun
Jan 7
2007

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

Sunday 1/7/2007 5:25:17 PM

Tags

History