Hi,
I have twelve sheets in the book called "Jan", "Feb" to "Dec". Now I will
use value in the two sheets by this format:

Row1 will contain columns, "jan", "feb", and etc.

Row2 should be like "=JAN!b2", "=FEB!b2", etc till "=Dec!b2".

Now the question is, how can I use reference, but not directly put in the
sheetname such as "Jan!b2"?
In another word, what I want to use is like "=Worksheets(cells(...))!b2".

Thanks for the help!

try ROW2

=INDIRECT("'" & A1 & " '!b2")



Jim wrote:
Hi,
I have twelve sheets in the book called "Jan", "Feb" to "Dec". Now I will
use value in the two sheets by this format:

Row1 will contain columns, "jan", "feb", and etc.

Row2 should be like "=JAN!b2", "=FEB!b2", etc till "=Dec!b2".

Now the question is, how can I use reference, but not directly put in the
sheetname such as "Jan!b2"?
In another word, what I want to use is like "=Worksheets(cells(...))!b2".

Thanks for the help!

=INDIRECT("'" & A1 & " '!b" & ROW())

I don't know how to do the row3, row4 part, because the "!B2" part is
fixed by the quote.

Anyone have more inputs?

Try in A2:

=OFFSET(INDIRECT(A$1&"!"&"A1"),ROW(2:2)-1,COLUMN(INDIRECT("B2"))-1)

and drag/copy across to Dec and down as far as necessary.

This should give you B2 from each sheet in row 2, B3 from each sheet in
row 3, etc.

Hope this works for you,

Declan

Hi,
Thanks for your reply.

So if the worksheet "Jan" got a new column inserted, let's say that
Jan!b2 changed to Jan!c2, can any worksheet function change the
reference automatically like the jan!b2 could?

I mean that if a column is inserted to worksheet "Jan" at column #1,
then Jan!b2 will automatically change to "Jan!C2". But if I use
function Indirect and Offset, the bias part will remain the same, as I
just tested.

How can I get the benefit of quick input and still keep the reference
integrity?

Any help or idea would be appreciated!




DOR wrote:
Try in A2:

=OFFSET(INDIRECT(A$1&"!"&"A1"),ROW(2:2)-1,COLUMN(INDIRECT("B2"))-1)

and drag/copy across to Dec and down as far as necessary.

This should give you B2 from each sheet in row 2, B3 from each sheet in
row 3, etc.

Hope this works for you,

Declan

I don't know how that you can be impervious to insertions without
having a direct reference to the source sheet somewhere, and the
original question specifically asked that there not be a direct
reference. If we were allowed a direct reference to the required
source sheet column, then we could just put =Jan!B2 in the Jan column,
copy down and we would not need any indirect references or offsets. If
we are allowed even one direct reference for each source sheet, the
whole problem is greatly simplified.

As far as I know, only direct references are changed when the reference
cell is moved. I don't see how or why Excel would try to adjust
indirect references. One of the purposes of indirect references is to
keep the reference constant even when the originally referenced cell
moves - INDIRECT("B2") will always refer to B2, regardless of
insertions.

It seems to me that if you need to be impervious to insertions, you
need to use direct references.

Do you agree?

BTW, does the original questioner know that if he uses =Jan!B2 and the
Jan sheet is renamed to January, that his reference is also changed,
automatically, to =January!B2 ...?

Hi,

Thanks for the reply.

It's a spreadsheet for maintaining some chronicle data, and somewhere
in the future the model need to be updated and insert a few columns
(liek a new annual dataset) here and there.

That's why a few of the summary spreadsheets would need be updated
according to the raw data, and a few other summary pages would be using
Indirect reference.

The nightmare part is that every time a new column (such as a new
annual dataset) is added, a new summary page need to be added to show
that column compare to other dataset.
Because of this reason, we can't use indirect reference. it will
always point to the exact place, but we want it to point to the exact
data (e.g., after new insert, from Jan!b2 to Jan!c2).
We for sure could update the Indirect reference function parameter,
but when there are more than 20 summary pages, this will be a huge
workload too.
If we use direct reference, the new summary page would have to be
inputed by hand (a few hundred columns), and we can't use Indirect
reference for the new summary page.

Anyway to use Indirect reference but still get the "impervious to
insertions" feature?


thanks

Are you sure that your whole workbook is structured well for your
application? My own experience is that monthly sheets are more trouble
that they are worth. I find it a lot easier to organize data by type
into different sheets (e.g., one sheet for product master, another for
customer master, another for sales transactions, etc. etc.) and
summarize elsewhere, using SUMIF, SUMPRODUCT, COUNTIF and whatever else
I need. If all your data of similar type were in one sheet, it would
be easy to extract data for Jan, Feb, etc., since the month would be
derived from a value in a cell rather than a sheet name. I much prefer
to put different types of data on different sheets, than data for
different times, whether they be months or years. Dividing data by
time makes summarization more difficult than it needs to be. If you
need to compare data from the many different sheets you have, then it
seems to me that each sheet contains the same type of data. If that is
so, maybe that data should be on the same sheet to start with.

I can tell you that every time I have been confronted with a workbook
with sheets for each mont, I have improved its usability and integrity
by revamping it to organize the data by type rather than time.

If you would like to send me a sample of your spreadsheet I might be
able to make some suggestion that would enable you to achieve what you
want.

Regards

Declan O'R