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

4 Q & A posts tagged with ABS

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
Fri
Apr 17
2009

What does the $ do in an excel spreadsheet?

What is the function of the $ sign in an excel spreadsheet?


Answer

It is called the Absolute and Relation symbol
Here is a free lesson about what it is doing
http://file1.net/lesson.asp?id=GlcNf6q11…

VBAXLMan

Friday 4/17/2009 9:44:03 PM
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
Wed
Dec 20
2006

Matching with Ms Excel?

I have a spreadsheet which contains 5-6 columns of info, with about 200-300 rows. Column D contains totals. Some of the totals in column D will match with one another, just with a minus sign. Say if there is a 54300, then there is another -54300, just its with a minus sign (this is actually a ballance of a clearing account).
What I want is to write a formula or macro that would take column D value from the first row, search through all column D, find matching value, cut those two rows and paste in another sheet within the same file. Then repeat this with all the rows, cutting rows with matching value in column D, so that all the matching values are pasted one below another in another sheet. This way the original sheet will only contain unmatched items.
I work as an accountant so have to go through account statements like that very frequently. Having a formula or a macro like that would make my life a lot easier :)

Thanks for any responses!

Happy Xmas!


Answer

You can solve it in one of these ways
1- Formula
2- Macro
I can post here the formula solution, because it is easier to understand.
Now, leave column E as seperator, go to F2 and paste this
=ABS( D2)
and fill it down to all the other cells. In G2, paste this
=LARGE( F:F, ROW()-1)
And fill the G2 down to all the cells below
Hay, it is your exact list, but sorted in Descending order

Yes
That is because VBAXLMan show you how to do it

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

Enjoy it and..

Enjoy my profile, I am the VBAXLMan

Wednesday 12/20/2006 5:36:13 PM

Tags

History