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

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

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

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
"Tyro" wrote:

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

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

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

On Feb 12, 5:21*pm, Louiseuk25
wrote:
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



"Tyro" wrote:
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

"Louiseuk25" wrote in message
...
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- Hide quoted text -

- Show quoted text -

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

"Louiseuk25" wrote in message
...
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
"Tyro" wrote:

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

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