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

47 Q & A posts found in April

Fri
Apr 30
2010

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

Friday 4/30/2010 5:59:00 PM
Wed
Apr 28
2010

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

Wednesday 4/28/2010 12:53:03 PM
Mon
Apr 26
2010

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

Monday 4/26/2010 11:56:54 PM
Mon
Apr 26
2010

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

Monday 4/26/2010 7:24:31 PM
Sun
Apr 18
2010

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

Sunday 4/18/2010 10:24:46 PM
Fri
Apr 16
2010

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

Friday 4/16/2010 8:40:37 AM
Thu
Apr 15
2010

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

Thursday 4/15/2010 8:08:20 AM
Tue
Apr 13
2010

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

Tuesday 4/13/2010 7:47:09 AM
Mon
Apr 12
2010

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

Monday 4/12/2010 5:10:35 AM
Mon
Apr 12
2010

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

Monday 4/12/2010 2:35:47 AM
Sun
Apr 11
2010

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

Sunday 4/11/2010 6:46:54 PM
Sun
Apr 11
2010

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

Sunday 4/11/2010 4:40:21 PM
Sun
Apr 11
2010

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

Sunday 4/11/2010 4:36:44 PM
Sun
Apr 11
2010

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

Sunday 4/11/2010 3:16:34 PM
Sun
Apr 11
2010

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…

Sunday 4/11/2010 7:47:41 AM
Sat
Apr 10
2010

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

Saturday 4/10/2010 10:08:03 PM
Sat
Apr 10
2010

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

Saturday 4/10/2010 8:21:33 PM
Sat
Apr 10
2010

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

Saturday 4/10/2010 7:45:00 PM
Sat
Apr 10
2010

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

Saturday 4/10/2010 2:36:50 PM
Sat
Apr 10
2010

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

Saturday 4/10/2010 2:22:57 PM
Sat
Apr 10
2010

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

Saturday 4/10/2010 10:33:10 AM
Sat
Apr 10
2010

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

Saturday 4/10/2010 3:58:16 AM
Fri
Apr 9
2010

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

Friday 4/9/2010 9:02:55 AM
Thu
Apr 8
2010

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

Thursday 4/8/2010 5:19:22 PM
Thu
Apr 8
2010

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

Thursday 4/8/2010 5:15:25 PM
Thu
Apr 8
2010

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

Thursday 4/8/2010 4:50:31 PM
Thu
Apr 8
2010

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

Thursday 4/8/2010 12:20:38 PM
Tue
Apr 6
2010

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

Tuesday 4/6/2010 9:09:48 AM
Tue
Apr 6
2010

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…

Tuesday 4/6/2010 7:34:07 AM
Tue
Apr 6
2010

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

Tuesday 4/6/2010 4:38:23 AM
Mon
Apr 5
2010

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…

Monday 4/5/2010 6:17:17 PM
Mon
Apr 5
2010

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

Monday 4/5/2010 4:45:47 PM
Mon
Apr 5
2010

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…

Monday 4/5/2010 4:33:00 PM
Mon
Apr 5
2010

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"

Monday 4/5/2010 4:15:18 PM
Mon
Apr 5
2010

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

Monday 4/5/2010 10:52:34 AM
Mon
Apr 5
2010

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

Monday 4/5/2010 9:10:47 AM
Mon
Apr 5
2010

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

Monday 4/5/2010 6:31:43 AM
Sun
Apr 4
2010

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

Sunday 4/4/2010 5:08:39 PM
Sun
Apr 4
2010

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

Sunday 4/4/2010 11:13:43 AM
Sun
Apr 4
2010

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

Sunday 4/4/2010 12:16:30 AM
Sat
Apr 3
2010

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

Saturday 4/3/2010 1:34:04 PM
Fri
Apr 2
2010

Microsoft word disks?

So my sister has these installation disks for microsoft word, power point, and excel, and she installed them on her laptop. Would I be able to use them on my computer with out her losing hers?


Answer

If you got your own serial number that you bought from Microsoft, yes

Otherwise, it is consider stealing, and no one will tell you to do so

VBAXLMan

Friday 4/2/2010 5:53:54 PM
Fri
Apr 2
2010

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

Friday 4/2/2010 5:44:51 PM
Fri
Apr 2
2010

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

Friday 4/2/2010 3:25:51 PM
Fri
Apr 2
2010

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

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

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

Friday 4/2/2010 1:34:37 PM
Fri
Apr 2
2010

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

Friday 4/2/2010 12:56:16 PM

Tags

History