Results 1 to 10 of 10
Thread: Excel 03 worksheet

20090731, 15:54 #1
 Join Date
 Jul 2009
 Location
 North Carolina, the Beach
 Posts
 6
 Thanks
 0
 Thanked 0 Times in 0 Posts
I am unable to reference a worksheet in the same workbook with muliple cells. I would like to use the autofil if possible, also.
Any suggestions?

20090731, 16:01 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Welcome to the Lounge!
The easiest way to refer to cells on another sheet in a formula is to point to them. Let's say that you are on Sheet1 and you want to sum cells A1:E1 on Sheet2. Here is a stepbystep description:
 Select the cell on Sheet1 where you want the formula.
 Type = to start a formula.
 Type SUM( to specify that you want to sum cells.
 Click on the sheet tab of Sheet2. This will activate Sheet2.
 Select cells A1:E1.
 Type ) and press Enter or click the red check mark to confirm the formula.
The cell where you entered this should now contain the formula
=SUM(Sheet2!A1:E1)
If you fill this down, the cell references will be adjusted automatically.
Post back with more details if this doesn't solve your problem.

20090803, 08:41 #3
 Join Date
 Jul 2009
 Location
 North Carolina, the Beach
 Posts
 6
 Thanks
 0
 Thanked 0 Times in 0 Posts
thank You so much.
Unfortunately I am not working with numbers I can sum, I am working with dates.
Wkshet 1 (A:18) are housing addresses and wkshet 2 (E2,E3) are renovation dates and reactivated dates with the corresponding address in (A1). They are not lined up in a way that I can copy and paste, and I recently took an 03 excel class, but nothing is coming back to me... I checked all the books from, class.. I am missing something
Bianca
[quote name='HansV' post='787036' date='31Jul2009 16:01']Welcome to the Lounge!
The easiest way to refer to cells on another sheet in a formula is to point to them. Let's say that you are on Sheet1 and you want to sum cells A1:E1 on Sheet2. Here is a stepbystep description:
 Select the cell on Sheet1 where you want the formula.
 Type = to start a formula.
 Type SUM( to specify that you want to sum cells.
 Click on the sheet tab of Sheet2. This will activate Sheet2.
 Select cells A1:E1.
 Type ) and press Enter or click the red check mark to confirm the formula.
The cell where you entered this should now contain the formula
=SUM(Sheet2!A1:E1)
If you fill this down, the cell references will be adjusted automatically.
Post back with more details if this doesn't solve your problem.[/quote]

20090803, 08:44 #4
 Join Date
 Jul 2009
 Location
 North Carolina, the Beach
 Posts
 6
 Thanks
 0
 Thanked 0 Times in 0 Posts
Thank you so much for your help. Unfortunately I am not working with numbers I can sum, working with dates.
In wksht 1 (A:18) housing addresses and wkshet 2 (E:2,E:3) are renovations dates and reactivated dates with the corresponding addresses.
my goal is to get all the dates from wk sht 2 into the proper columns and rows in wkst 1.
I recently took an 03 excel class, but nothing is coming back to me...I know I am missing something. Again thank you.
[quote name='HansV' post='787036' date='31Jul2009 16:01']Welcome to the Lounge!
The easiest way to refer to cells on another sheet in a formula is to point to them. Let's say that you are on Sheet1 and you want to sum cells A1:E1 on Sheet2. Here is a stepbystep description:
 Select the cell on Sheet1 where you want the formula.
 Type = to start a formula.
 Type SUM( to specify that you want to sum cells.
 Click on the sheet tab of Sheet2. This will activate Sheet2.
 Select cells A1:E1.
 Type ) and press Enter or click the red check mark to confirm the formula.
The cell where you entered this should now contain the formula
=SUM(Sheet2!A1:E1)
If you fill this down, the cell references will be adjusted automatically.
Post back with more details if this doesn't solve your problem.[/quote]

20090803, 08:49 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Could you post a small workbook that shows the data layout, with an indication of what you want to accomplish? Use dummy data.

20090803, 09:00 #6
 Join Date
 Jul 2009
 Location
 North Carolina, the Beach
 Posts
 6
 Thanks
 0
 Thanked 0 Times in 0 Posts
[quote name='HansV' post='787308' date='03Aug2009 08:49']Could you post a small workbook that shows the data layout, with an indication of what you want to accomplish? Use dummy data.[/quote]
ok, I will try to post...
on wkshet 1 (BkMnr) there are housing addresses, on wkshet 2 (renovations) there are corresponding dates. My goal is to get dates from wksht 2 to wksht 1 or reference and/or combined on wkshet 3...if possible...

20090803, 09:27 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Thank you. If I understand your description correctly, you can use the following formula in cell D2 on BkMnr:
=IF(ISERROR(MATCH($A2,Renovations!B:B,0)),"",INDEX (Renovations!G:G,MATCH($A2,Renovations!B:B,0)+1))
and in cell I2:
=IF(ISERROR(MATCH($A2,Renovations!B:B,0)),"",INDEX (Renovations!G:G,MATCH($A2,Renovations!B:B,0)+2))
The IF(ISERROR(...) part ensures that the formula will return a blank if there is no matching address.
You can fill down both formulas as far as needed.
I have attached your workbook with these formulas.
Note: there was a space after the address in cell A5 on BkMnr, this caused the MATCH to fail; I have corrected this.

20090803, 10:59 #8
 Join Date
 Jul 2009
 Location
 North Carolina, the Beach
 Posts
 6
 Thanks
 0
 Thanked 0 Times in 0 Posts
WOW!!!
That is excellent. Thank you.
Thank is a very difficult formula.
I have 3 other workbooks just like that.
Can I copy this formula and changed some of the locations(columns and rows)?
[quote name='HansV' post='787315' date='03Aug2009 09:27']Thank you. If I understand your description correctly, you can use the following formula in cell D2 on BkMnr:
=IF(ISERROR(MATCH($A2,Renovations!B:B,0)),"",INDEX (Renovations!G:G,MATCH($A2,Renovations!B:B,0)+1))
and in cell I2:
=IF(ISERROR(MATCH($A2,Renovations!B:B,0)),"",INDEX (Renovations!G:G,MATCH($A2,Renovations!B:B,0)+2))
The IF(ISERROR(...) part ensures that the formula will return a blank if there is no matching address.
You can fill down both formulas as far as needed.
I have attached your workbook with these formulas.
Note: there was a space after the address in cell A5 on BkMnr, this caused the MATCH to fail; I have corrected this.[/quote]

20090803, 11:13 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Yes, you can copy and adapt the formula. In
=IF(ISERROR(MATCH($A2,Renovations!B:B,0)),"",INDEX (Renovations!G:G,MATCH($A2,Renovations!B:B,0)+1))
$A2 is the cell containing the address to look up.
Renovations!B:B is the column on the second sheet containing the addresses.
Renovations!G:G is the column on the second sheet containing the dates.
+1 is used because the renovation date is one row down from the address.
In the other formula, +2 is used because the reactivation date is two rows down from the address.
I hope this helps you with adapting the formula.

20090803, 12:06 #10
 Join Date
 Jul 2009
 Location
 North Carolina, the Beach
 Posts
 6
 Thanks
 0
 Thanked 0 Times in 0 Posts
Thank You, you helped me tremendously!
[quote name='HansV' post='787334' date='03Aug2009 11:13']Yes, you can copy and adapt the formula. In
=IF(ISERROR(MATCH($A2,Renovations!B:B,0)),"",INDEX (Renovations!G:G,MATCH($A2,Renovations!B:B,0)+1))
$A2 is the cell containing the address to look up.
Renovations!B:B is the column on the second sheet containing the addresses.
Renovations!G:G is the column on the second sheet containing the dates.
+1 is used because the renovation date is one row down from the address.
In the other formula, +2 is used because the reactivation date is two rows down from the address.
I hope this helps you with adapting the formula.[/quote]