This should be easy

K

Kypreo

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.
 
K

Kypreo

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
 
G

Gord Dibben

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
 
K

Kypreo

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*
 
R

Rowan Drummond

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
 
Top