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

Hi,
Based on database theory, the complex model should be normalized. But
in this specific situation, what I want is input data once, then use
references to compare them in different decision models.

The typical way to do it is same month by years, same year data for
seasonal patterns, etc.

The complexity would be resolved easily by programmig a C++ or C#
application to generate the spreadsheets in fly.

Do you think that would worth it? How to adjust the business (cost,
benefit, future usages, etc.)?

Glad to hear you mention data modeling and normalization. Now I know
that you recognize the issue and the principles!

Could you not put all the raw data once on one sheet or sheets by type
of data, and then reference (extract, summarize, using formulas) the
appropriate time periods from the different models? I have done
seasonal analysis with Excel and this is the way I have done it.

I agree that you might have some difficulty bringing the results of
different models back to one comparison sheet if you have to use
indirect references while still maintaining integrity against
insertions etc., but could you not standardize on where your input and
output data are on each model and not move them - and must you use
indirect references?

I have difficulty assessing or visualizing the problem you have without
actually seeing your spreadsheet and understanding better what you are
trying to accomplish, and I cannot comment on the advisability of using
another language, because I don't know what skills you have on board,
but it seems to me that revamping the organization of the data could
help.

Declan O'R