PC Pals Forum

Technical Help & Discussion => Windows PCs & Software: Help, News & Discussion => Topic started by: DJ on January 24, 2006, 22:54

Title: Excel Formula Query
Post by: DJ on January 24, 2006, 22:54

Hi All,

I need to be able to reference a sheet name in my formula, dependant on what the user enters in a cell. Let me explain.

Say I have a workbook with Sheets named Summary, Jan, Feb and Mar.

The formula I could use on a Summary sheet is like...

Code: [Select]


=Jan!A1 + A2


But I want to control this by the users data entry in a cell adjacent to this, so that whatever is entered in a cell replacess the "Jan" bit.

So if the data is entered in cell D1 I thought I could use

Code: [Select]


='D1'A1 + A2


But it doesn't seem to work - can anyone shed any light? I hope I have explained this ok.

Basically the sheets can be named anything and so the forumla to calculate this needs to be altered each time - so referencing to another cell would be ideal.

Thanks

DJ

Title: Excel Formula Query
Post by: GillE on January 25, 2006, 01:26

Without knowing exactly what your parameters are, my meagre knowledge is insufficient :( .  Nevertheless, I'm sufficiently confident (after a night out dancing and at the pub :oops:) to suggest the answer might lie in the 'And' argument.

Gill

Title: Excel Formula Query
Post by: DJ on January 25, 2006, 09:33

All I want to do is replace the Sheet name (Jan!) with a cell reference (e.g. D1).  So that whatever is typed in D1 is a sheet name.

DJ

Title: Excel Formula Query
Post by: TR on January 25, 2006, 18:00

DJ,

Does this seem right  :wink:

A formula to put the sheet?s name into a cell

=RIGHT(CELL(?filename?,A1),LEN(CELL(?filename?,A1))-FIND(?]?,CELL(?filename?,A1)))

The CELL() function with the ?filename? argument will return the full path of the workbook plus the sheet name. The workbook will be enclosed in brackets []. This formula finds the right bracket (]) and returns everything after that.

Note that if the workbook has not been saved, there is no path, and this formula returns a #Value error.

TR

Or have I got the wrong end of the stick again  :twisted:

Title: Excel Formula Query
Post by: DJ on January 26, 2006, 19:11

Nope - thats exactly the right end of the stick!!

Thanks TR

DJ

Title: Excel Formula Query
Post by: Simon on January 26, 2006, 22:03

Oh, well done, Terry!  We know who to come to with Excel queries now.  :thumb:

Title: Excel Formula Query
Post by: TR on January 26, 2006, 22:57

Lucky Guess I guess  8)



But then again  :wink:


Phewwww another bluff   :nerd:


Where's the sticky out toungue when you want it  :twisted:

Title: Excel Formula Query
Post by: Simon on January 26, 2006, 23:19

What, this one?   :tongue: