Hello,
Is it possible to refer to the first row of a defined name in excel 2003?
Currently, I have a whole worksheet defined as the name "DB" (the worksheet name is Cyclic_DB)
So, I was thinking along the lines of
=DB!$1:$1
but obviously this is for referencing to a worksheet...
In xl2007, I would have had my worksheet (Cyclic_DB) in a table
and use DB[row][col] but it seems there's no function like that in xl2003.
Please help me.
Thank you :)
Referring to first row of the defined name
-
- StarLounger
- Posts: 61
- Joined: 29 Jul 2010, 14:52
Referring to first row of the defined name
I don't have one
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Referring to first row of the defined name
Warning: DB is not a very good choice for a defined name, because it is also the name of an Excel worksheet function:
=OFFSET(reference,rows,cols,height,width)
First cell in the range: =OFFSET(DB,0,0,1,1)
First row in the range: =OFFSET(DB,0,0,1)
First column in the range: =OFFSET(DB,0,0,,1)
If you want to refer to a specific cell within a range, you can use the OFFSET function. OFFSET has the syntaxDB function
Returns the depreciation of an asset for a specified period using the fixed-declining balance method.
Syntax
DB(cost,salvage,life,period,month)
=OFFSET(reference,rows,cols,height,width)
First cell in the range: =OFFSET(DB,0,0,1,1)
First row in the range: =OFFSET(DB,0,0,1)
First column in the range: =OFFSET(DB,0,0,,1)
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 61
- Joined: 29 Jul 2010, 14:52
Re: Referring to first row of the defined name
Oh.... I will rename my name then :)
The formula works perfectly as expected.
Thank you very much HansV!
You just solved a project in 9 minutes;;
I feel kinda useless and happy at the same time (a weird feeling)
Thanks :D
The formula works perfectly as expected.
Thank you very much HansV!
You just solved a project in 9 minutes;;
I feel kinda useless and happy at the same time (a weird feeling)
Thanks :D
I don't have one
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Referring to first row of the defined name
Experience comes over time - I've worked with Excel since version 1.0 (for Apple Macintosh) in 1985!
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 61
- Joined: 29 Jul 2010, 14:52
Re: Referring to first row of the defined name
Wow... That was before I was even born lol.
Master, teach me the way of MS Excel xD (bribe)
Master, teach me the way of MS Excel xD (bribe)
I don't have one
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Referring to first row of the defined name
As with most software: be inquisitive, dare to experiment, read the help file, don't hesitate to ask questions here, and... save often!
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 818
- Joined: 24 Jan 2010, 15:56
Re: Referring to first row of the defined name
FYI, you can also use INDEX which has the advantage of not being volatile:
=INDEX(named_range, 0, 1)
would refer to the first row.
(basic syntax is index(range, row, column) but you can use 0 to return an entire row or column)
=INDEX(named_range, 0, 1)
would refer to the first row.
(basic syntax is index(range, row, column) but you can use 0 to return an entire row or column)
Regards,
Rory
Rory
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Re: Referring to first row of the defined name
Not sure about the bit about the help fileHansV wrote:As with most software: be inquisitive, dare to experiment, read the help file, don't hesitate to ask questions here, and... save often!
Nathan
There's no place like home.....
There's no place like home.....
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Referring to first row of the defined name
The Excel and Excel VBA help files could be a lot better than they are (they seem to get worse with each new version - the documentation for the early Mac versions of Excel was superb), but despite that they still contain a wealth of information - it pays to follow the "See Also" links, you often learn something new.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 689
- Joined: 27 Jan 2010, 16:45
- Location: Ottawa, Ontario, Canada
Re: Referring to first row of the defined name
Hi HansHansV wrote:Experience comes over time - I've worked with Excel since version 1.0 (for Apple Macintosh) in 1985!
Did you get to work with its predecessor, Multiplan?
Regards
Don
Don
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Referring to first row of the defined name
Yes, I did, but I didn't like it much. Neither did I like the first version of Word for the Mac.Don Wells wrote:Did you get to work with its predecessor, Multiplan?
(I also used VisiCalc and WordStar, I think on a CP/M machine...)
Best wishes,
Hans
Hans