Quick Questions

[td]

I have been tasked with creating an excel template for tracking time per
specific jobs. I have 2 quick questions I hope someone can help me with.
First. I have some standard formulas that total up certain columns in each
row. These formulas will repeat in each row but I need to change the row
number in each formula when I go from one row to the next. Is there anyway I
can automate this so that everytime I start a new row I can copy from the
row above but not have to change the row numbers? That it would
automatically know we are in a new row and use the corresponding row number.
Let me give this example.


row1 Cell E1 has formula =D1-B1-C1
row2 I want to do the same thing in row2 but not have to change
from the1 to a 2.

Whats the fastest way to do this task?


My second question is I would like to create a drop down list from another
sheet within this workbook that has a GL number and name associated with it.
I would like to have the drop down list show the GL account name but when I
click on it, it actually enters the GL account#. Is that possible? How?


Thanks,


Thomas

 

[Pete_UK]

To answer your first question, select cell E1, click <copy> (or CTRL-C
or Edit | Copy), then move the cursor to E2 and press the <Enter> key.
You will see that the formula has changed automatically to = D2-B2-C2.
This is the normal way that Excel behaves when you <copy> a cell with
a formula in - if you want the row numbers to remain static you should
precede them with a $ symbol (see Absolute Addressing in Excel Help).

For your second question you can use data validation to set up a drop-
down list. Debra Dalgleish explains how here:

http://www.contextures.com/xlDataVal01.html

You can then use VLOOKUP to get matching data from a table elsewhere
in your workbook. Debra also has a section on this function.

Hope this helps.

Pete

 

[td]

Thank you Pete!! You're awesome!


To answer your first question, select cell E1, click <copy> (or CTRL-C
or Edit | Copy), then move the cursor to E2 and press the <Enter> key.
You will see that the formula has changed automatically to = D2-B2-C2.
This is the normal way that Excel behaves when you <copy> a cell with
a formula in - if you want the row numbers to remain static you should
precede them with a $ symbol (see Absolute Addressing in Excel Help).

For your second question you can use data validation to set up a drop-
down list. Debra Dalgleish explains how here:

http://www.contextures.com/xlDataVal01.html

You can then use VLOOKUP to get matching data from a table elsewhere
in your workbook. Debra also has a section on this function.

Hope this helps.

Pete

 

[Pete_UK]

Thanks for the feedback, but it's Debra's site that is awesome.

Pete