Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Kypreo
 
Posts: n/a
Default This should be easy

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.

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default This should be easy

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

"Kypreo" wrote in message
...
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.



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Kypreo
 
Posts: n/a
Default This should be easy

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


"Biff" wrote:

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

"Kypreo" wrote in message
...
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.




  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Gord Dibben
 
Posts: n/a
Default This should be easy

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

On Mon, 21 Nov 2005 19:24:02 -0800, Kypreo
wrote:

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.


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Kypreo
 
Posts: n/a
Default This should be easy

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="Annu al
*for the start of each week so that it looks 7 cells down from the
previous entry*


"Gord Dibben" wrote:

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

On Mon, 21 Nov 2005 19:24:02 -0800, Kypreo
wrote:

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.





  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Rowan Drummond
 
Posts: n/a
Default This should be easy

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

Kypreo wrote:
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
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Is there an easy way to tie workbooks together? Marc New Users to Excel 1 August 6th 05 03:09 AM
Easy VB code question Anthony Excel Discussion (Misc queries) 2 July 8th 05 08:45 PM
OsCommerce - Easy Populate Script - CSV/TXT Conversion Problem. PriceTrim Excel Discussion (Misc queries) 3 July 5th 05 05:27 PM
new user with easy question? not easy for me speakeztruth New Users to Excel 5 June 3rd 05 09:40 PM
is there an easy way to move parts of cells around jvoortman Excel Discussion (Misc queries) 0 January 22nd 05 12:08 AM


All times are GMT +1. The time now is 03:16 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"