Sheet Name by cell reference in formula
Mark Draa
summary report
that pulls the values from one chosen sheet with a simple cell references,
like this:
=Sheet1!B4
I'd like to change this formula to refer to a cell that contains the sheet
name I want to reference to refer to. In other words, I want to put the
sheet name ("Sheet 1", for example) in cell A1 and have my formula say
something like:
=$A$1!B4
and get the same answer as the original reference.
I've tried
=INDIRECT($A$1)!B4 - doesn't work
=CELL("contents",$A$1)!B4 = doesn't work
I've looked through the help file, and through the ms knowledge base, but
can't find this particular issue. Does anyone know how this is done?
Thanks!
Mark Draa
Metro Benefits, Inc.
Gary Brown
HTH,
Gary L. Brown
Kinneson Consulting
Gary....@Kinneson.com
www.Kinneson.com
"Mark Draa" <mark...@metrobenefits.com> wrote in message
news:yPD37.33495$rh.7...@news6.giganews.com...
George Simms
Try: =INDIRECT(A1&"!B4")
All the Best
George Simms
Microsoft MVP - Excel
Newcastle upon Tyne
England.
"Mark Draa" wrote in message ...
David McRitchie
Close but you need quotes.
A2: 'Sheet1
=IF($A2="","",INDIRECT($A2&"!B4"))
The following pages might be of interest to you
Build TOC Another Approach (secondary page to BuildTOC)
http://www.geocities.com/davemcritchie/excel/buildtoc2.htm
Mostly on VBA look for topic "Some Worksheet Examples using INDIRECT"
Worksheet VBA Coding
http://www.geocities.com/davemcritchie/excel/sheets.htm
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Macros: http://www.geocities.com/davemcritchie/excel/excel.htm
Search Page: http://www.geocities.com/davemcritchie/excel/search.htm
"Mark Draa" <mark...@metrobenefits.com> wrote in message news:yPD37.33495$rh.7...@news6.giganews.com...
Conrad Carlberg
A2. (No quote marks around either value.) Now the formula:
=INDIRECT(A1 & A2)
returns whatever value is in cell B4 of Sheet1.
C^2
Conrad Carlberg
"Mark Draa" <mark...@metrobenefits.com> wrote in message
news:yPD37.33495$rh.7...@news6.giganews.com...
Mark Draa
> =INDIRECT(A1&"!B4")
This does indeed work, but introduces an unintended complication. You can't
copy this formula and have the "B4" reference change relatively. That means
I would have to edit each formula in every row (there are about 500 rows).
That would take more time than was saved by the sheet name reference, even
though I would only have to do it once.
Currently, I edit each of the formulas for a single row (there are about 13
of them) to do the summary every month. I change two instances of the sheet
name in each formula. Then I just copy down the formula to cover all of the
rows. I was attempting to save time by changing the formula to have the
sheetname referenced, so I would only have to change it in one place.
Is there another way to accomplish this formula that preserves the relative
reference?
Thanks again,
David McRitchie
I had assumed you would be picking the same cell from various sheets.
To continue to pick cells off of the same sheet try:
=INDIRECT(A1&"!B" & ROW() + 3)
or some other adjustment on the current row.
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Macros: http://www.geocities.com/davemcritchie/excel/excel.htm
Search Page: http://www.geocities.com/davemcritchie/excel/search.htm
"Mark Draa" <mark...@metrobenefits.com> wrote in message news:jSE37.34412$rh.7...@news6.giganews.com...
Mark Draa
incremented numbers that make correspond to the row numbers I'm trying to
get, I can change the formula to:
=INDIRECT($A$1&"!b"&TEXT(1,G5))
and the reference will change when copied. This is a little unelegant, but
it works. I'm still looking for an all-in-one solution in case anybody has
one.
Thanks,
Mark Draa
Metro Benefits, Inc.
Mark Draa <mark...@metrobenefits.com> wrote in message
news:jSE37.34412$rh.7...@news6.giganews.com...
J.E. McGimpsey
=INDIRECT(A1 & "!B" & Row())
Add or subtract to Row() as necessary (e.g., if the formula's in row 5,
use Row()-1)
In article <jSE37.34412$rh.7...@news6.giganews.com>,
"Mark Draa" <mark...@metrobenefits.com> wrote:
--
J.E. McGimpsey ar...@zptvzcfrl.pbz
ROT13 encoding, decode for real mail
Mark Draa
The row is the same, I just need to preserve the ability to copy the formula
down the entire range, so 'ROW()' should work just fine.
Wonderful. Thanks!
Mark Draa
Metro Benefits, Inc.
David McRitchie <dmcri...@msn.com> wrote in message
news:#yx3OW7CBHA.1836@tkmsftngp02...
George Simms
Just adding to your other solutions, to save having to work out adding and
subtracting rows, if you want to refer to Cell B4 B5 B6...etc. Just drag
fill this formula.
=INDIRECT($A$1&"!B" & ROW(4:4) )
All the Best
George Simms
Microsoft MVP - Excel
Newcastle upon Tyne
England.
"Mark Draa" <mark...@metrobenefits.com> wrote in message
news:jSE37.34412$rh.7...@news6.giganews.com...
Mark Draa
complicated one, a SUMIF:
=SUMIF('6-30-01'!$A:$A,A5,'6-30-01'!$D:$D)
6-30-01 is the sheet name that yield the data, and the client code to search
for is in a5 of the current sheet, the list of client codes is in column A
of the searched sheet, and on match, it is pulling the value from column D
of the searched sheet. If I apply the logic of the simple formulae to this
one, my first attempt yields (my sheetname reference cell is B1):
=SUMIF(INDIRECT($B$1&"!$A:$A,"& ADDRESS(column()-1, row()),$b$1&"!$D:$D"))
My formula exists in for b5, and so to compare the client code in a5, I'm
using Column()-1 and Row() to get the right reference.
Of course, this doesn't work. I'm thinking that the second reference of the
sheetname is what is tripping me up. The INDIRECT function appears to want
everything contained within it's parentheses. The right answer probably has
two INDIRECT calls in it, but it's above my current level of comprehension.
Any ideas?
Mark Draa
Metro Benefits, Inc.
Mark Draa <mark...@metrobenefits.com> wrote in message
news:53F37.35043$rh.7...@news6.giganews.com...
David McRitchie
I think this is what you are looking for with SUMIF and INDIRECT:
=SUMIF(INDIRECT("'"&$B$1&"'!$A:$A"),"A5,INDIRECT("'"&$B$1&"'!$D:$D"))
If your formulas do not automatically recalculate you may have to use
CTRL+ALT+F9
I created if first on the actual sheet
=SUMIF(A:A,F1,$D:$D)
=SUMIF(INDIRECT("'"&$F$2&"'!A:A"),"b",INDIRECT("'"&$F$2&"'!$D:$D"))
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Macros: http://www.geocities.com/davemcritchie/excel/excel.htm
Search Page: http://www.geocities.com/davemcritchie/excel/search.htm
"Mark Draa" <mark...@metrobenefits.com> wrote in message news:uNF37.36551$rh.7...@news6.giganews.com...
George Simms
Do you need the ADDRESS function?
=SUMIF(INDIRECT($B$1&"!A:A"),A5,INDIRECT($B$1&"!D:D"))
Each needs a separate reference.
All the Best
George Simms
Microsoft MVP - Excel
Newcastle upon Tyne
England.
"Mark Draa" <mark...@metrobenefits.com> wrote in message
news:uNF37.36551$rh.7...@news6.giganews.com...
David McRitchie
=SUMIF(INDIRECT("'"&$B$1&"'!$A:$A"),A5,INDIRECT("'"&$B$1&"'!$D:$D"))
Mark Draa
Thanks, that is indeed the correct formula. I used the Address function
because I thought I had to calculate the reference when I was using the
INDIRECT function, that's all. The users' penchant for using dates as sheet
names complicates matters a bit as well. In the reference cell, you need to
format it as text, then enter two single-quotes, the date, and a final
single quote.
I'm a 20-yr Lotus veteran trying to learn Excel in a new position. I have
to tell you that I did look in vain for examples of this technique before
posting here, and couldn't really find anything onpoint. Of course, that's
not really the way I learned Lotus either - Its just that I don't have the
patience I did 20 years ago! :-)
I really appreciate your help!
Mark
George Simms <Geor...@email.msn.com> wrote in message
news:u27S3k8CBHA.1260@tkmsftngp04...
George Simms
Pleased to assist. If you need help with a formula or VBA David has an
very good web site, also check out Chip Pearson's site:
http://www.cpearson.com/excel/topic.htm
both are packed with examples you might find useful.
You will also find links there other Excel sites.
All the Best
George Simms
Microsoft MVP - Excel
Newcastle upon Tyne
England.
"Mark Draa" wrote in message ...
Philip Coxson
=INDIRECT($A$1&"!"&CELL("address",B4))
HTH,
Phil.
Mark Draa wrote:
========================
<< A side note, by creating an additional column (G, in the below example) of
incremented numbers that make correspond to the row numbers I'm trying to
get, I can change the formula to:
=INDIRECT($A$1&"!b"&TEXT(1,G5))
and the reference will change when copied. This is a little unelegant, but
it works. I'm still looking for an all-in-one solution in case anybody has
one.
Thanks,
Mark Draa
Metro Benefits, Inc.
>>
<snip>
Marc Van Meerbeek
f.e.:
Try =INDIRECT($A$1&"!B4")
In the attachment you can find what you need.
In the "summary" sheet you may try (f.e.) to overwrite "January" with
"February" (= identical to the name of the corresponding sheet, otherwise
you will get a failurenotice) and you will see the amounts changing.
I hope this is what you were looking for.
Mark Draa schreef:
--
Met vriendelijke groeten
Marc
**************************************
Marc Van Meerbeek
inspecteur financiën
Boekhandelstraat 2
B-3000 Leuven
tel. (32)016/211.574
fax. (32)016/211.599
Marc.Van...@leuven.be
**************************************
J.E. McGimpsey
opened, at least not by regulars, and they waste bandwidth as they are
replicated on news servers around the world. If you want to send an
attachment to someone, do it by email.
In this case, your text was probably good enough to answer the question.
Thanks!
In article <3B545A29...@leuven.be>,
Marc Van Meerbeek <marc.van...@leuven.be> wrote:
> Sorry I forgot to attach
>
> I think you need to use the "indirect"-formula to obtain what you are looking
> for.
> f.e.:
> Try =INDIRECT($A$1&"!B4")
>
> In the attachment you can find what you need.
> In the "summary" sheet you may try (f.e.) to overwrite "January" with
> "February" (= identical to the name of the corresponding sheet, otherwise you
> will get a
> failurenotice) and you will see the amounts changing.
> I hope this is what you were looking for.
>
> Mark Draa schreef:
>
> --
> Met vriendelijke groeten
>
>
> Marc
>
> **************************************
> Marc Van Meerbeek
> inspecteur financiën
> Boekhandelstraat 2
> B-3000 Leuven
>
> tel. (32)016/211.574
> fax. (32)016/211.599
> Marc.Van...@leuven.be
>
> **************************************
>
> ---------------------------------------------------------------------
> [Image]