3 quick questions

[x-amount]

1. How do I select a row, column or cell from a range?

say, if i've got range "Jolly_Roger" which is cells "a1:ad4567",

how do I select the first column,

or the cell 3 rows down and 16 columns across?

Can I do that via quoting the range?

i.e. is there a phrase for "the first column of the Jolly_Roger range"
and

2. If Ive got a list of filenames in column a, how can I include theses filename in a reference?

for example.

Each business unit has a forecast sheet which summarises their annual budgets, on sheet1 in their respective workbook. I am making a summary of these sumamries.

If i have the simple BU name in column a, i will have the filename of the budget for that BU in the corresponding row in column b.


If i have a data validation box which has only the BU names as valid entries, I want to the vlookup the filename/location from the BU name (from the above paragraph set-up), and then use this reference in my formula.

I think the INDIRECT function may play a part in this, but Im not too sure.

3. I have 3 cells on different sheets in the same workbook. I want them all to have the SAME value, but I want to be able to change the value in each location.

I have the idea of a worksheet_change thingy which update the other cells in turn, and whack a validation drop down on each cell. Is this my best soultion. I am apprehensive about this method, as I already have a worksheet_change on some of my worksheets for soemthing different. Can I have more than one "changed cell" test by simply nesting my if's?

Actually, these may not be so quick as I had imagined!

I'm actually leaving the office now, so i'll greatfully implement and comment on any solutions tomorrow!

TVMIA

 

[Mark W.]

1. How do I select a row, column or cell from a range?

say, if i've got range "Jolly_Roger" which is cells "a1:ad4567",

how do I select the first column,

or the cell 3 rows down and 16 columns across?

Can I do that via quoting the range?

i.e. is there a phrase for "the first column of the Jolly_Roger range"
and

{=A:A Jolly_Roger}
{=INDEX(Jolly_Roger,0,1)}

{=OFFSET(Jolly_Roger,3,,,16)}

Note: These are array formulas which must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula. For more on array formulas see the Excel Help topic for "About array formulas and how to enter them".

 

[phantom1975]

Answer to Question 1

Private Sub Test()
    'Indicate the value that is 3 rows down, 16 rows across from the top left cell
    MsgBox Range("Jolly_Roger").Item(3, 16).Value
    'Indicate the column number of the first column in the range
    MsgBox Range("Jolly_Roger").Column
End Sub

 

[Aladin Akyurek]

1. How do I select a row, column or cell from a range?

say, if i've got range "Jolly_Roger" which is cells "a1:ad4567",

how do I select the first column,

or the cell 3 rows down and 16 columns across?

Can I do that via quoting the range?

i.e. is there a phrase for "the first column of the Jolly_Roger range"
and

INDEX(Joly_Roger,4,0)

accesses the 4th row of Joly_Roger, which is equivalent to...

A3:AD3

INDEX(Joly_Roger,0,1)

accesses all rows of the 1st column within Joly_Roger, which is equivalent to...

A1:A4567

INDEX(Joly_Roger,0,1):INDEX(Joly_Roger,3,16) or

A1:INDEX(Joly_Roger,3,16)

gives you the range...

A1:P3

2. If Ive got a list of filenames in column a, how can I include theses filename in a reference?

for example.

Each business unit has a forecast sheet which summarises their annual budgets, on sheet1 in their respective workbook. I am making a summary of these sumamries.

If i have the simple BU name in column a, i will have the filename of the budget for that BU in the corresponding row in column b.


If i have a data validation box which has only the BU names as valid entries, I want to the vlookup the filename/location from the BU name (from the above paragraph set-up), and then use this reference in my formula.

I think the INDIRECT function may play a part in this, but Im not too sure.

This depends a bit on what you in column A. If you want to work with closed books (INDIRECT requires the target file to be open), consider using INDIRECT.EXT from the morefunc add-in.

 

[just_jon]

3. I have 3 cells on different sheets in the same workbook. I want them all to have the SAME value, but I want to be able to change the value in each location.

I have the idea of a worksheet_change thingy which update the other cells in turn, and whack a validation drop down on each cell. Is this my best soultion. I am apprehensive about this method, as I already have a worksheet_change on some of my worksheets for soemthing different. Can I have more than one "changed cell" test by simply nesting my if's?

Yes, you should be able to have multiple checks like --

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("A1")) Is Nothing
    ' do if A1 events...
Else if Not Intersect(Target, Range("B1:C100")) Is Nothing
    ' do other events...
Else
    ' do nothing
endif
Application.EnableEvents = True
End