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

6 Q & A posts tagged with ROW

Sat
Nov 19
2011

Microsoft excel selection movement behavior?

in excel, there is an option to choose which direction the selection will move after pressing enter on the current cell (i.e. down, right, etc). i frequently have to change this option because i require different behaviors depending on which worksheet i am working on. is this only a global option or is it possible to set this behavior specific to a particular worksheet?


Answer

Yes, it is global

Now there is a trick that you can use

- Select a range of cells, say B5:F12
- Now in your selection, you can edit first cell
- Then move using Enter, Shift+Enter, Tab, Shift+Tab
- Enter, moves cursor down (assuming default settings in Options)
- Shift+Enter, moves it up
- Tab, moves it to the right
- Shift+Tab, moves it to the left
- Notice that you cursor will not go out of your selection earlier
- As long as you don't use Arrow keys, this will be valid

Again , these assuming you have default settings

Bottom line, using these keys will help you decide where to go after every cell edit

I think that is the only way around your situation

let me know if it helped

Saturday 11/19/2011 5:18: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
Thu
Apr 9
2009

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


Answer

Actually
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
http://file1.net/lesson.asp?id=fjame5p10…

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
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
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
Jul 17
2006

My rogers goes through Internet explorer I do not want it to go that way I want the rogers browser?

Can you tell me how to change to the rogers browser?


Answer

go to Control Panel > Add or remove programs > Set program access and defaults
and change the Internet browser to what you want

note: you must have rogers browser installed before you do that

XLMan

Monday 7/17/2006 3:53:30 PM

Tags

History