Author Topic: Excel Formula Query (Read 1188 times)

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

GillE · « **Reply #1 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

) to suggest the answer might lie in the 'And' argument.

Gill

DJ · « **Reply #2 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

TR · « **Reply #3 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:

DJ · « **Reply #4 on:** January 26, 2006, 19:11 »

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

Thanks TR

DJ

Simon · « **Reply #5 on:** January 26, 2006, 22:03 »

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

TR · « **Reply #6 on:** January 26, 2006, 22:57 »

Lucky Guess I guess 8)

But then again :wink:

Phewwww another bluff

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

Simon · « **Reply #7 on:** January 26, 2006, 23:19 »

What, this one?