Using Worksheet names in formulae

L

Louiseuk25

Hi,

How can I pull data from multiple worksheets based on the worksheet name.

Is it possible to use the worksheet name as a variable within functions such
as sumif or vlookup?

If you need me to provide any further info, do let me know!

Thanks,

Louise
 
T

Tyro

Sure you can. The easiest way is for you to start your formula and when you
want to reference a cell on another worksheet, click that sheet's tab,
select the cell or range and press Enter. Excel will insert the sheetname
and cell address(es) for you. Or you can type the sheet name followed by an
exclamation point before the cell address as in Sheet2!A1. If the sheet name
has space(s) in it, the name must be in single quotes as in 'My Sheet'!A1

Tyro
 
L

Louiseuk25

Thanks Tyro, but not quite what I meant.

eg if I have 10 worksheets 'My Sheet'!1 through to 10, how can I write a
formula to state 'If worksheet name is equal to 'My Sheet'!5, then add up the
values in column c?'

Thanks,

Lou
 
P

Pete_UK

Suppose you have put "My Sheet 1" in A1 of another sheet (without the
quotes), then you could make use of the INDIRECT function, with a
formula like this:

=SUMIF(INDIRECT("'"&A1&"'!C2:C100"),"Yes",INDIRECT("'"&A1&"'!
D2:D100"))

Just change the text in A1 to get a different result.

Hope this helps.

Pete
 
T

Tyro

Perhaps this might work for you
This formula gets the active worksheet name and compares it to "My Sheet"
and if it is "My Sheet", sums the first 100 cells in column C. If not "My
Sheet" returns the empty cell

=IF(RIGHT(CELL("filename"),LEN(CELL("filename"))-SEARCH("]",CELL("filename")))="My
Sheet",SUM(C1:C100),"")

Tyro
 
Top