Results 1 to 10 of 10

20030427, 01:41 #1
 Join Date
 Apr 2002
 Location
 Northern Territory, Australia
 Posts
 471
 Thanks
 16
 Thanked 0 Times in 0 Posts
Lookup Function  Tips please? (Excel 97)
Hello
Could someone point me in the direction of a place to learn about using the Lookup function in excel? I am also battling with understanding relative and absolute. I am sure some kind person has taken the time to write some clear and concise instructions out there on the Web? Even some examples would be useful.
Also, is there a way to see graphically, in Excel, how a formula relateds to cells? Sorry if this is a stupid question.
Kerry

20030427, 01:53 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Lookup Function  Tips please? (Excel 97)
Hi Kerry, about your last question: select Tools  Auditing  Show Auditing Toolbar. This toolbar contains several buttons that can show how cells are related.
Click Trace Precedents to see the "parent cells" of the active cell (cells referred to in the formula of the active cell). Click the button again to see the "grandparents" etc.
Click Trace Dependents to see the "child cells" of the active cell (cells whose formula refer to the active cell). Click the button again to see the "grandchildren" etc.
There are also buttons to remove precedents arrows or dependents arrows or all arrows.

20030427, 02:17 #3
 Join Date
 Apr 2002
 Location
 Northern Territory, Australia
 Posts
 471
 Thanks
 16
 Thanked 0 Times in 0 Posts
Re: Lookup Function  Tips please? (Excel 97)
Thanks Hans.

20030427, 02:21 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Lookup Function  Tips please? (Excel 97)
About absolute and relative: this determines what happens if you copy and paste a formula to another cell, or use the fill grip in the lower right corner of a cell to copy the formula of the active cell down or to the right.
An absolute reference will not be changed by Excel, while a relative reference will be adapted. To make a reference absolute, put $ signs in front of the row and or column, or press F4. A example may help.
<table border=1><td align=right>=$A$1+$B$1</td><td align=right>=$A$1+B1</td><td align=right>=$A$1+$B$1</td><td align=right>=$A$1+B2</td><td align=right>=$A$1+$B$1</td><td align=right>=$A$1+B3</td></table>
In the table above, the formula =$A$1+$B$1 was entered in the top left cell, then filled down. As you can see, the cells in the second and third row of the left column have exactly the same formula.
The formula =$A$1+B1 was entered in the top right cell, then filled down. As you can see, the reference to A1 stays the same (because of the $ signs), but the reference to B1 is adapted to B2 and B3, respectively.
This example also shows that you can mix absolute and relative references in one formula. You can even mix them within the reference to one cell: in $A1, the reference to the column is absolute, but the reference to the row is relative. In fact the F4 key mentioned above will cycle between the four possible ways of referring to a cell:
<table border=1><td>Formula</td><td>Column</td><td>Row</td><td align=right>=A1</td><td>relative</td><td>relative</td><td align=right>=$A$1</td><td>absolute</td><td>absolute</td><td align=right>=A$1</td><td>relative</td><td>absolute</td><td align=right>=$A1</td><td>absolute</td><td>relative</td></table>
Note: references within a workbook (even to cells in another worksheet) are relative by default, whereas references to cells in another workbook are absolute by default.

20030427, 02:30 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Lookup Function  Tips please? (Excel 97)
The online help for Excel 97 does a reasonable job of explaining worksheet functions. If you search Google using (for instance) excel lookup function, you'll get lots of hits. On educational sites (.edu) you have a good chance of finding explanations and examples.
Note: Excel has several lookup functions: LOOKUP, VLOOKUP and HLOOKUP (besides, MATCH and INDEX are related functions). In many cases, VLOOKUP and HLOOKUP are better.

20030427, 02:43 #6
 Join Date
 Apr 2002
 Location
 Northern Territory, Australia
 Posts
 471
 Thanks
 16
 Thanked 0 Times in 0 Posts
Re: Lookup Function  Tips please? (Excel 97)
Once again you have been extremely helpful Hans. This is a great explanation!
As for the lookup functions  yes I will have a search on Google.
Much appreciated.
Regards Kerry

20030427, 23:53 #7
 Join Date
 Jan 2001
 Location
 Redcliff, Alberta, Canada
 Posts
 4,066
 Thanks
 2
 Thanked 5 Times in 5 Posts
Re: Lookup Function  Tips please? (Excel 97)
Further to absolute and relative functions, an analogy that works well if you are explaining it to other people is giving someone directions.
Absolute directions  means you give the address. Boom, that is where they are going.
Relative directions  means you give the directions as follows; go two blocks north and turn left. Where you end up is relative to where you started from.
Cheers[b]Catharine Richardson (WebGenii)
WebGenii Home Page
Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

20030428, 13:03 #8
 Join Date
 Mar 2003
 Location
 Beddau, Mid Glamorgan, Wales
 Posts
 289
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Lookup Function  Tips please? (Excel 97)
(Edited by HansV to make url live  see <!post=Tags to make a URL live,98472>Tags to make a URL live<!/post> and <!help=19>Help 19<!/help>)
Kerry:
You might try Peter Noneley's Excel Function Dictionary spreadsheet available free at http://homepage.ntlworld.com/noneley/
Tony.Regards,
Tony
[s] [/s]
www.SylviArtist.com

20030428, 13:16 #9
 Join Date
 Apr 2002
 Location
 Northern Territory, Australia
 Posts
 471
 Thanks
 16
 Thanked 0 Times in 0 Posts
Re: Lookup Function  Tips please? (Excel 97)
Thankyou Tony  This is absolutely FANTASTIC! I knew there would be some excellent stuff out there! Dont you just love this forum? Everyone is so helpful. I only hope I can offer something back soon.
Regards
Kerry

20030428, 13:46 #10
 Join Date
 Mar 2003
 Location
 Beddau, Mid Glamorgan, Wales
 Posts
 289
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Lookup Function  Tips please? (Excel 97)
Kerry  You're welcome. Presumably you already have, since you're a star!
Hans  Thanks for 'enlivening' my post. I'm fairly new to the lounge, so not aware of all its facilities yet. May I add my congratulations to all the others you have received.
Tony.Regards,
Tony
[s] [/s]
www.SylviArtist.com