This should be easy

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kypreo - 22 Nov 2005 03:24 GMT

here is a simple formula....
=A1+2...... this assumes the cell ur looking at is A3
i want the lookup to happen from another worksheet.

eg
=sheet2!A1+2 but this doesn't work.. anyone got a work around ?

i have a worksheet full with similar functions.. a calendar type thing to
compare annual leave with rosters days on.

Reply to this Message

Biff - 22 Nov 2005 04:26 GMT

Hi!

>=sheet2!A1+2 but this doesn't work.. anyone got a work around ?

What does: "but this doesn't work" mean?

Do you get an error? An incorrect result?

Biff

> here is a simple formula....
> =A1+2...... this assumes the cell ur looking at is A3
[quoted text clipped - 6 lines]
> i have a worksheet full with similar functions.. a calendar type thing to
> compare annual leave with rosters days on.

Reply to this Message

Kypreo - 22 Nov 2005 04:54 GMT

i get #Value! error..... meaning it cannot find a value.
i should mention that the data is all text....not numeric....although, i
dont see why it would make a difference.

basically what i want, is to have a sort of Anchor cell, then look down 2
cells down from that original cell

simply changing the lookup point manually wont do.. needs to be done within
a formula

> Hi!
>
[quoted text clipped - 16 lines]
> > i have a worksheet full with similar functions.. a calendar type thing to
> > compare annual leave with rosters days on.

Reply to this Message

Gord Dibben - 22 Nov 2005 04:54 GMT

Kypreo

Kypreo

What doesn't work?

=sheet2!A1+2 is a valid formula.

Do you have a sheet2?

Does sheet2 A1 have a value to add to?

Where is the formula =sheet2!A1+2 located?

Gord Dibben Excel MVP

>here is a simple formula....
>=A1+2...... this assumes the cell ur looking at is A3
[quoted text clipped - 5 lines]
>i have a worksheet full with similar functions.. a calendar type thing to
>compare annual leave with rosters days on.

Reply to this Message

Kypreo - 22 Nov 2005 05:16 GMT

SHEET 2 DATA
        AA    AE    AG    AH
1-Jan-05    Sat    W    W    W    W
2-Jan-05    Sun    W    W    W    W
3-Jan-05    Mon    Public    Public    Public    Public
4-Jan-05    Tue    Annual        Annual
5-Jan-05    Wed    Annual        Annual
6-Jan-05    Thu    Annual        Annual
7-Jan-05    Fri    Annual        Annual
8-Jan-05    Sat    W    W    W    W
9-Jan-05    Sun    W    W    W    W
10-Jan-05    Mon
11-Jan-05    Tue
12-Jan-05    Wed
13-Jan-05    Thu
14-Jan-05    Fri

SHEET 1 DATA
A B C D
1 7.30 8.00 8.30 9.00 9.30 11.30 12.00 12.30 3.30 4.00 4.30
2 AG
3 WW
4 AA
5 AH

FORMULA FOR SHEET 1 CELL B2
*this is the only way i got it to work
=IF(Sheet2!E4="Annual","x",IF(Sheet2!E5="Annual","x",IF(Sheet2!E6="Annual","x","y")))

FORMULA FOR SHEET 1 CELL B4
=IF(Sheet2!C4="Annual","x",IF(Sheet2!C5="Annual","x",IF(Sheet2!C6="Annual","x","y")))

this gets really lengthy for 365 days a year for 15 people.

if possible to place the
IF(Sheet2!C12="Annual","x",IF(Sheet2!C13="Annual
to
IF(Sheet2!$C5+7="Annual","x",IF(Sheet2!$C6+7="Annual
*for the start of each week so that it looks 7 cells down from the
previous entry*

> Kypreo
>
[quoted text clipped - 21 lines]
> >i have a worksheet full with similar functions.. a calendar type thing to
> >compare annual leave with rosters days on.

Reply to this Message

Rowan Drummond - 22 Nov 2005 05:28 GMT

Have a look at the indirect function in help. If you want to return the
value of cell A3 you could use:
=Indirect("A" &(1+2))

Hope this helps
Rowan

> here is a simple formula....
> =A1+2...... this assumes the cell ur looking at is A3
[quoted text clipped - 5 lines]
> i have a worksheet full with similar functions.. a calendar type thing to
> compare annual leave with rosters days on.

Reply to this Message