How to AUTOMATICALLY complete an Excel array (or matrix ?) from information
in the left column and top row; each cell to hold an incrementally changed
formula ?
That item/array/matrix is in the 13th sheet of a workbook whose other 12
sheets are monthly expenditures in categories.
The ITEM should be filled with pasted links from the totals in the 12 sheets
to create annual totals.
Thus the left column contains the links for total expenditures by category
for January
and the top row contains twelve links for totals in one category for each
month.

I can (and have) set this up manually, but it seems to me that Excel should
be able to fill in the series automatically, expecially for the rows where
the only variable item in the link formula is the month name.

I am interested because this whole thing has to be redone for each new year.

If I had been able to I would have attached the workbook whose completion I
want.

Hi

I assume that the layout is identical on all sheets.
In which case, the headings in Column A of the Summary sheet are not
required.
Enter in B2 of Summary
=INDIRECT("'"&B$1&"'!B"&ROW())
and copy across and down
--

Regards
Roger Govier

"Peese" <(E-Mail Removed)> wrote in message
news:8C79E8D4-4745-4EA6-B1B6-(E-Mail Removed)...
> How to AUTOMATICALLY complete an Excel array (or matrix ?) from
> information
> in the left column and top row; each cell to hold an incrementally changed
> formula ?
> That item/array/matrix is in the 13th sheet of a workbook whose other 12
> sheets are monthly expenditures in categories.
> The ITEM should be filled with pasted links from the totals in the 12
> sheets
> to create annual totals.
> Thus the left column contains the links for total expenditures by category
> for January
> and the top row contains twelve links for totals in one category for each
> month.
>
> I can (and have) set this up manually, but it seems to me that Excel
> should
> be able to fill in the series automatically, expecially for the rows where
> the only variable item in the link formula is the month name.
>
> I am interested because this whole thing has to be redone for each new
> year.
>
> If I had been able to I would have attached the workbook whose completion
> I
> want.

Thanks Roger
The layout is the same for each of the 12 months, but obviously the name of
each sheet is different.
At this point it is not obvious to me that your solution will work, but I am
excited at the prospect that it might, and will most certainly report back
after I have tried it.
I anticipate that it will be a great breakthrough for me if it works.

"Roger Govier" wrote:

> Hi
>
> I assume that the layout is identical on all sheets.
> In which case, the headings in Column A of the Summary sheet are not
> required.
> Enter in B2 of Summary
> =INDIRECT("'"&B$1&"'!B"&ROW())
> and copy across and down
> --
>
> Regards
> Roger Govier
>
> "Peese" <(E-Mail Removed)> wrote in message
> news:8C79E8D4-4745-4EA6-B1B6-(E-Mail Removed)...
> > How to AUTOMATICALLY complete an Excel array (or matrix ?) from
> > information
> > in the left column and top row; each cell to hold an incrementally changed
> > formula ?
> > That item/array/matrix is in the 13th sheet of a workbook whose other 12
> > sheets are monthly expenditures in categories.
> > The ITEM should be filled with pasted links from the totals in the 12
> > sheets
> > to create annual totals.
> > Thus the left column contains the links for total expenditures by category
> > for January
> > and the top row contains twelve links for totals in one category for each
> > month.
> >
> > I can (and have) set this up manually, but it seems to me that Excel
> > should
> > be able to fill in the series automatically, expecially for the rows where
> > the only variable item in the link formula is the month name.
> >
> > I am interested because this whole thing has to be redone for each new
> > year.
> >
> > If I had been able to I would have attached the workbook whose completion
> > I
> > want.
>
>

When I paste that expression into the top left empty cell of the table,
'internet' appears in the cell, although the edit line still shows
=INDIRECT("'"&B$1&"'!B"&ROW()). If pasted anywhere else, '0.00' appears.
There is obviously a problem; #1. my ignorance; #2 the table does NOT start
at A1.
I thought I might help solve it by telling you that the first empty cell is
C3; The first three columns of row 2 are : =Jan!$B$4; =Jan!$C$4; =Jan!$D$4
and the first three rows of column B are : =Jan!$B$4; =Feb!$B$4; =Mar!$B$4
Could you please adapt your 'formula' to fit those details ?

"Peese" wrote:

> Thanks Roger
> The layout is the same for each of the 12 months, but obviously the name of
> each sheet is different.
> At this point it is not obvious to me that your solution will work, but I am
> excited at the prospect that it might, and will most certainly report back
> after I have tried it.
> I anticipate that it will be a great breakthrough for me if it works.
>
> "Roger Govier" wrote:
>
> > Hi
> >
> > I assume that the layout is identical on all sheets.
> > In which case, the headings in Column A of the Summary sheet are not
> > required.
> > Enter in B2 of Summary
> > =INDIRECT("'"&B$1&"'!B"&ROW())
> > and copy across and down
> > --
> >
> > Regards
> > Roger Govier
> >
> > "Peese" <(E-Mail Removed)> wrote in message
> > news:8C79E8D4-4745-4EA6-B1B6-(E-Mail Removed)...
> > > How to AUTOMATICALLY complete an Excel array (or matrix ?) from
> > > information
> > > in the left column and top row; each cell to hold an incrementally changed
> > > formula ?
> > > That item/array/matrix is in the 13th sheet of a workbook whose other 12
> > > sheets are monthly expenditures in categories.
> > > The ITEM should be filled with pasted links from the totals in the 12
> > > sheets
> > > to create annual totals.
> > > Thus the left column contains the links for total expenditures by category
> > > for January
> > > and the top row contains twelve links for totals in one category for each
> > > month.
> > >
> > > I can (and have) set this up manually, but it seems to me that Excel
> > > should
> > > be able to fill in the series automatically, expecially for the rows where
> > > the only variable item in the link formula is the month name.
> > >
> > > I am interested because this whole thing has to be redone for each new
> > > year.
> > >
> > > If I had been able to I would have attached the workbook whose completion
> > > I
> > > want.
> >
> >

Hi

What I was assuming was the following

A B C
1 Jan Feb
2 Heading 1
3 Heading 2

Heading in this case will be the same Cost centre headings that appear on
all Sheets.
Sheets are assumed to be named Jan, Feb, Mar etc.

If they are not named that, just put in B1:M1 etc, the actual name of each
of your 12 sheets

Now, enter the formula I posted in cell B2 and copy across and down, and you
should see the information pulled from each sheet

--

Regards
Roger Govier

"Peese" <(E-Mail Removed)> wrote in message
news:3B08385E-9184-40F4-9FD1-(E-Mail Removed)...
> When I paste that expression into the top left empty cell of the table,
> 'internet' appears in the cell, although the edit line still shows
> =INDIRECT("'"&B$1&"'!B"&ROW()). If pasted anywhere else, '0.00' appears.
> There is obviously a problem; #1. my ignorance; #2 the table does NOT
> start
> at A1.
> I thought I might help solve it by telling you that the first empty cell
> is
> C3; The first three columns of row 2 are : =Jan!$B$4; =Jan!$C$4; =Jan!$D$4
> and the first three rows of column B are : =Jan!$B$4; =Feb!$B$4; =Mar!$B$4
> Could you please adapt your 'formula' to fit those details ?
>
> "Peese" wrote:
>
>> Thanks Roger
>> The layout is the same for each of the 12 months, but obviously the name
>> of
>> each sheet is different.
>> At this point it is not obvious to me that your solution will work, but I
>> am
>> excited at the prospect that it might, and will most certainly report
>> back
>> after I have tried it.
>> I anticipate that it will be a great breakthrough for me if it works.
>>
>> "Roger Govier" wrote:
>>
>> > Hi
>> >
>> > I assume that the layout is identical on all sheets.
>> > In which case, the headings in Column A of the Summary sheet are not
>> > required.
>> > Enter in B2 of Summary
>> > =INDIRECT("'"&B$1&"'!B"&ROW())
>> > and copy across and down
>> > --
>> >
>> > Regards
>> > Roger Govier
>> >
>> > "Peese" <(E-Mail Removed)> wrote in message
>> > news:8C79E8D4-4745-4EA6-B1B6-(E-Mail Removed)...
>> > > How to AUTOMATICALLY complete an Excel array (or matrix ?) from
>> > > information
>> > > in the left column and top row; each cell to hold an incrementally
>> > > changed
>> > > formula ?
>> > > That item/array/matrix is in the 13th sheet of a workbook whose other
>> > > 12
>> > > sheets are monthly expenditures in categories.
>> > > The ITEM should be filled with pasted links from the totals in the 12
>> > > sheets
>> > > to create annual totals.
>> > > Thus the left column contains the links for total expenditures by
>> > > category
>> > > for January
>> > > and the top row contains twelve links for totals in one category for
>> > > each
>> > > month.
>> > >
>> > > I can (and have) set this up manually, but it seems to me that Excel
>> > > should
>> > > be able to fill in the series automatically, expecially for the rows
>> > > where
>> > > the only variable item in the link formula is the month name.
>> > >
>> > > I am interested because this whole thing has to be redone for each
>> > > new
>> > > year.
>> > >
>> > > If I had been able to I would have attached the workbook whose
>> > > completion
>> > > I
>> > > want.
>> >
>> >

Thanks again, Roger
Your assumption matches the layout of my totals SS, but not of the monthly
sheets.
There the data does not start until row 6.
I originally entered your formula into C3 instead of B2 (shame on me !).
When I enter it into B2, there is obviously reproduction of content from
January's sheet but the fit is still not right because the monthly data
starts in row 6. Can you please tweak the formula to accomodate that fact ?
We are obviously making good progress. Thank you.

"Roger Govier" wrote:

> Hi
>
> What I was assuming was the following
>
> A B C
> 1 Jan Feb
> 2 Heading 1
> 3 Heading 2
>
> Heading in this case will be the same Cost centre headings that appear on
> all Sheets.
> Sheets are assumed to be named Jan, Feb, Mar etc.
>
> If they are not named that, just put in B1:M1 etc, the actual name of each
> of your 12 sheets
>
> Now, enter the formula I posted in cell B2 and copy across and down, and you
> should see the information pulled from each sheet
>
> --
>
> Regards
> Roger Govier
>
> "Peese" <(E-Mail Removed)> wrote in message
> news:3B08385E-9184-40F4-9FD1-(E-Mail Removed)...
> > When I paste that expression into the top left empty cell of the table,
> > 'internet' appears in the cell, although the edit line still shows
> > =INDIRECT("'"&B$1&"'!B"&ROW()). If pasted anywhere else, '0.00' appears.
> > There is obviously a problem; #1. my ignorance; #2 the table does NOT
> > start
> > at A1.
> > I thought I might help solve it by telling you that the first empty cell
> > is
> > C3; The first three columns of row 2 are : =Jan!$B$4; =Jan!$C$4; =Jan!$D$4
> > and the first three rows of column B are : =Jan!$B$4; =Feb!$B$4; =Mar!$B$4
> > Could you please adapt your 'formula' to fit those details ?
> >
> > "Peese" wrote:
> >
> >> Thanks Roger
> >> The layout is the same for each of the 12 months, but obviously the name
> >> of
> >> each sheet is different.
> >> At this point it is not obvious to me that your solution will work, but I
> >> am
> >> excited at the prospect that it might, and will most certainly report
> >> back
> >> after I have tried it.
> >> I anticipate that it will be a great breakthrough for me if it works.
> >>
> >> "Roger Govier" wrote:
> >>
> >> > Hi
> >> >
> >> > I assume that the layout is identical on all sheets.
> >> > In which case, the headings in Column A of the Summary sheet are not
> >> > required.
> >> > Enter in B2 of Summary
> >> > =INDIRECT("'"&B$1&"'!B"&ROW())
> >> > and copy across and down
> >> > --
> >> >
> >> > Regards
> >> > Roger Govier
> >> >
> >> > "Peese" <(E-Mail Removed)> wrote in message
> >> > news:8C79E8D4-4745-4EA6-B1B6-(E-Mail Removed)...
> >> > > How to AUTOMATICALLY complete an Excel array (or matrix ?) from
> >> > > information
> >> > > in the left column and top row; each cell to hold an incrementally
> >> > > changed
> >> > > formula ?
> >> > > That item/array/matrix is in the 13th sheet of a workbook whose other
> >> > > 12
> >> > > sheets are monthly expenditures in categories.
> >> > > The ITEM should be filled with pasted links from the totals in the 12
> >> > > sheets
> >> > > to create annual totals.
> >> > > Thus the left column contains the links for total expenditures by
> >> > > category
> >> > > for January
> >> > > and the top row contains twelve links for totals in one category for
> >> > > each
> >> > > month.
> >> > >
> >> > > I can (and have) set this up manually, but it seems to me that Excel
> >> > > should
> >> > > be able to fill in the series automatically, expecially for the rows
> >> > > where
> >> > > the only variable item in the link formula is the month name.
> >> > >
> >> > > I am interested because this whole thing has to be redone for each
> >> > > new
> >> > > year.
> >> > >
> >> > > If I had been able to I would have attached the workbook whose
> >> > > completion
> >> > > I
> >> > > want.
> >> >
> >> >
>

Roger
You have given me a formula that seems to relate back directly to every
total on every worksheet. But for that to work, surely you need to know where
those totals are on each monthly sheet (rather than the irrelevant
information I gave in my last post). The totals are in B4, C4, D4 etc. Can
you tweak your formula for me, taking that information into account ? My
efforts have failed.

I am now confident that your method will work and am grateful for being
shown that technique, but it DOES beg the question of a method to
autocomplete a table with reference to its own contents only. I had expected
to be shown how to partially or fully trigger autocompletion of the summary
sheet FROM THE CONTENTS OF THE LEFT COLUMN AND TOP ROW. Do you know if such a
function exists ?

"Peese" wrote:

> Thanks again, Roger
> Your assumption matches the layout of my totals SS, but not of the monthly
> sheets.
> There the data does not start until row 6.
> I originally entered your formula into C3 instead of B2 (shame on me !).
> When I enter it into B2, there is obviously reproduction of content from
> January's sheet but the fit is still not right because the monthly data
> starts in row 6. Can you please tweak the formula to accomodate that fact ?
> We are obviously making good progress. Thank you.
>
> "Roger Govier" wrote:
>
> > Hi
> >
> > What I was assuming was the following
> >
> > A B C
> > 1 Jan Feb
> > 2 Heading 1
> > 3 Heading 2
> >
> > Heading in this case will be the same Cost centre headings that appear on
> > all Sheets.
> > Sheets are assumed to be named Jan, Feb, Mar etc.
> >
> > If they are not named that, just put in B1:M1 etc, the actual name of each
> > of your 12 sheets
> >
> > Now, enter the formula I posted in cell B2 and copy across and down, and you
> > should see the information pulled from each sheet
> >
> > --
> >
> > Regards
> > Roger Govier
> >
> > "Peese" <(E-Mail Removed)> wrote in message
> > news:3B08385E-9184-40F4-9FD1-(E-Mail Removed)...
> > > When I paste that expression into the top left empty cell of the table,
> > > 'internet' appears in the cell, although the edit line still shows
> > > =INDIRECT("'"&B$1&"'!B"&ROW()). If pasted anywhere else, '0.00' appears.
> > > There is obviously a problem; #1. my ignorance; #2 the table does NOT
> > > start
> > > at A1.
> > > I thought I might help solve it by telling you that the first empty cell
> > > is
> > > C3; The first three columns of row 2 are : =Jan!$B$4; =Jan!$C$4; =Jan!$D$4
> > > and the first three rows of column B are : =Jan!$B$4; =Feb!$B$4; =Mar!$B$4
> > > Could you please adapt your 'formula' to fit those details ?
> > >
> > > "Peese" wrote:
> > >
> > >> Thanks Roger
> > >> The layout is the same for each of the 12 months, but obviously the name
> > >> of
> > >> each sheet is different.
> > >> At this point it is not obvious to me that your solution will work, but I
> > >> am
> > >> excited at the prospect that it might, and will most certainly report
> > >> back
> > >> after I have tried it.
> > >> I anticipate that it will be a great breakthrough for me if it works.
> > >>
> > >> "Roger Govier" wrote:
> > >>
> > >> > Hi
> > >> >
> > >> > I assume that the layout is identical on all sheets.
> > >> > In which case, the headings in Column A of the Summary sheet are not
> > >> > required.
> > >> > Enter in B2 of Summary
> > >> > =INDIRECT("'"&B$1&"'!B"&ROW())
> > >> > and copy across and down
> > >> > --
> > >> >
> > >> > Regards
> > >> > Roger Govier
> > >> >
> > >> > "Peese" <(E-Mail Removed)> wrote in message
> > >> > news:8C79E8D4-4745-4EA6-B1B6-(E-Mail Removed)...
> > >> > > How to AUTOMATICALLY complete an Excel array (or matrix ?) from
> > >> > > information
> > >> > > in the left column and top row; each cell to hold an incrementally
> > >> > > changed
> > >> > > formula ?
> > >> > > That item/array/matrix is in the 13th sheet of a workbook whose other
> > >> > > 12
> > >> > > sheets are monthly expenditures in categories.
> > >> > > The ITEM should be filled with pasted links from the totals in the 12
> > >> > > sheets
> > >> > > to create annual totals.
> > >> > > Thus the left column contains the links for total expenditures by
> > >> > > category
> > >> > > for January
> > >> > > and the top row contains twelve links for totals in one category for
> > >> > > each
> > >> > > month.
> > >> > >
> > >> > > I can (and have) set this up manually, but it seems to me that Excel
> > >> > > should
> > >> > > be able to fill in the series automatically, expecially for the rows
> > >> > > where
> > >> > > the only variable item in the link formula is the month name.
> > >> > >
> > >> > > I am interested because this whole thing has to be redone for each
> > >> > > new
> > >> > > year.
> > >> > >
> > >> > > If I had been able to I would have attached the workbook whose
> > >> > > completion
> > >> > > I
> > >> > > want.
> > >> >
> > >> >
> >

Hi

Send me your file directly, and I will take a look
To mail direct, send to
roger at technology4u dot co dot uk
Do the obvious with at and dot

--

Regards
Roger Govier

"Peese" <(E-Mail Removed)> wrote in message
news:CE92C1BA-DC75-44BE-9E37-(E-Mail Removed)...
> Roger
> You have given me a formula that seems to relate back directly to every
> total on every worksheet. But for that to work, surely you need to know
> where
> those totals are on each monthly sheet (rather than the irrelevant
> information I gave in my last post). The totals are in B4, C4, D4 etc. Can
> you tweak your formula for me, taking that information into account ? My
> efforts have failed.
>
> I am now confident that your method will work and am grateful for being
> shown that technique, but it DOES beg the question of a method to
> autocomplete a table with reference to its own contents only. I had
> expected
> to be shown how to partially or fully trigger autocompletion of the
> summary
> sheet FROM THE CONTENTS OF THE LEFT COLUMN AND TOP ROW. Do you know if
> such a
> function exists ?
>
> "Peese" wrote:
>
>> Thanks again, Roger
>> Your assumption matches the layout of my totals SS, but not of the
>> monthly
>> sheets.
>> There the data does not start until row 6.
>> I originally entered your formula into C3 instead of B2 (shame on me !).
>> When I enter it into B2, there is obviously reproduction of content from
>> January's sheet but the fit is still not right because the monthly data
>> starts in row 6. Can you please tweak the formula to accomodate that fact
>> ?
>> We are obviously making good progress. Thank you.
>>
>> "Roger Govier" wrote:
>>
>> > Hi
>> >
>> > What I was assuming was the following
>> >
>> > A B C
>> > 1 Jan Feb
>> > 2 Heading 1
>> > 3 Heading 2
>> >
>> > Heading in this case will be the same Cost centre headings that appear
>> > on
>> > all Sheets.
>> > Sheets are assumed to be named Jan, Feb, Mar etc.
>> >
>> > If they are not named that, just put in B1:M1 etc, the actual name of
>> > each
>> > of your 12 sheets
>> >
>> > Now, enter the formula I posted in cell B2 and copy across and down,
>> > and you
>> > should see the information pulled from each sheet
>> >
>> > --
>> >
>> > Regards
>> > Roger Govier
>> >
>> > "Peese" <(E-Mail Removed)> wrote in message
>> > news:3B08385E-9184-40F4-9FD1-(E-Mail Removed)...
>> > > When I paste that expression into the top left empty cell of the
>> > > table,
>> > > 'internet' appears in the cell, although the edit line still shows
>> > > =INDIRECT("'"&B$1&"'!B"&ROW()). If pasted anywhere else, '0.00'
>> > > appears.
>> > > There is obviously a problem; #1. my ignorance; #2 the table does NOT
>> > > start
>> > > at A1.
>> > > I thought I might help solve it by telling you that the first empty
>> > > cell
>> > > is
>> > > C3; The first three columns of row 2 are : =Jan!$B$4; =Jan!$C$4;
>> > > =Jan!$D$4
>> > > and the first three rows of column B are : =Jan!$B$4; =Feb!$B$4;
>> > > =Mar!$B$4
>> > > Could you please adapt your 'formula' to fit those details ?
>> > >
>> > > "Peese" wrote:
>> > >
>> > >> Thanks Roger
>> > >> The layout is the same for each of the 12 months, but obviously the
>> > >> name
>> > >> of
>> > >> each sheet is different.
>> > >> At this point it is not obvious to me that your solution will work,
>> > >> but I
>> > >> am
>> > >> excited at the prospect that it might, and will most certainly
>> > >> report
>> > >> back
>> > >> after I have tried it.
>> > >> I anticipate that it will be a great breakthrough for me if it
>> > >> works.
>> > >>
>> > >> "Roger Govier" wrote:
>> > >>
>> > >> > Hi
>> > >> >
>> > >> > I assume that the layout is identical on all sheets.
>> > >> > In which case, the headings in Column A of the Summary sheet are
>> > >> > not
>> > >> > required.
>> > >> > Enter in B2 of Summary
>> > >> > =INDIRECT("'"&B$1&"'!B"&ROW())
>> > >> > and copy across and down
>> > >> > --
>> > >> >
>> > >> > Regards
>> > >> > Roger Govier
>> > >> >
>> > >> > "Peese" <(E-Mail Removed)> wrote in message
>> > >> > news:8C79E8D4-4745-4EA6-B1B6-(E-Mail Removed)...
>> > >> > > How to AUTOMATICALLY complete an Excel array (or matrix ?) from
>> > >> > > information
>> > >> > > in the left column and top row; each cell to hold an
>> > >> > > incrementally
>> > >> > > changed
>> > >> > > formula ?
>> > >> > > That item/array/matrix is in the 13th sheet of a workbook whose
>> > >> > > other
>> > >> > > 12
>> > >> > > sheets are monthly expenditures in categories.
>> > >> > > The ITEM should be filled with pasted links from the totals in
>> > >> > > the 12
>> > >> > > sheets
>> > >> > > to create annual totals.
>> > >> > > Thus the left column contains the links for total expenditures
>> > >> > > by
>> > >> > > category
>> > >> > > for January
>> > >> > > and the top row contains twelve links for totals in one category
>> > >> > > for
>> > >> > > each
>> > >> > > month.
>> > >> > >
>> > >> > > I can (and have) set this up manually, but it seems to me that
>> > >> > > Excel
>> > >> > > should
>> > >> > > be able to fill in the series automatically, expecially for the
>> > >> > > rows
>> > >> > > where
>> > >> > > the only variable item in the link formula is the month name.
>> > >> > >
>> > >> > > I am interested because this whole thing has to be redone for
>> > >> > > each
>> > >> > > new
>> > >> > > year.
>> > >> > >
>> > >> > > If I had been able to I would have attached the workbook whose
>> > >> > > completion
>> > >> > > I
>> > >> > > want.
>> > >> >
>> > >> >
>> >

Thank you. It is on its way.
Joe

"Roger Govier" wrote:

> Hi
>
> Send me your file directly, and I will take a look
> To mail direct, send to
> roger at technology4u dot co dot uk
> Do the obvious with at and dot
>
> --
>
> Regards
> Roger Govier
>
> "Peese" <(E-Mail Removed)> wrote in message
> news:CE92C1BA-DC75-44BE-9E37-(E-Mail Removed)...
> > Roger
> > You have given me a formula that seems to relate back directly to every
> > total on every worksheet. But for that to work, surely you need to know
> > where
> > those totals are on each monthly sheet (rather than the irrelevant
> > information I gave in my last post). The totals are in B4, C4, D4 etc. Can
> > you tweak your formula for me, taking that information into account ? My
> > efforts have failed.
> >
> > I am now confident that your method will work and am grateful for being
> > shown that technique, but it DOES beg the question of a method to
> > autocomplete a table with reference to its own contents only. I had
> > expected
> > to be shown how to partially or fully trigger autocompletion of the
> > summary
> > sheet FROM THE CONTENTS OF THE LEFT COLUMN AND TOP ROW. Do you know if
> > such a
> > function exists ?
> >
> > "Peese" wrote:
> >
> >> Thanks again, Roger
> >> Your assumption matches the layout of my totals SS, but not of the
> >> monthly
> >> sheets.
> >> There the data does not start until row 6.
> >> I originally entered your formula into C3 instead of B2 (shame on me !).
> >> When I enter it into B2, there is obviously reproduction of content from
> >> January's sheet but the fit is still not right because the monthly data
> >> starts in row 6. Can you please tweak the formula to accomodate that fact
> >> ?
> >> We are obviously making good progress. Thank you.
> >>
> >> "Roger Govier" wrote:
> >>
> >> > Hi
> >> >
> >> > What I was assuming was the following
> >> >
> >> > A B C
> >> > 1 Jan Feb
> >> > 2 Heading 1
> >> > 3 Heading 2
> >> >
> >> > Heading in this case will be the same Cost centre headings that appear
> >> > on
> >> > all Sheets.
> >> > Sheets are assumed to be named Jan, Feb, Mar etc.
> >> >
> >> > If they are not named that, just put in B1:M1 etc, the actual name of
> >> > each
> >> > of your 12 sheets
> >> >
> >> > Now, enter the formula I posted in cell B2 and copy across and down,
> >> > and you
> >> > should see the information pulled from each sheet
> >> >
> >> > --
> >> >
> >> > Regards
> >> > Roger Govier
> >> >
> >> > "Peese" <(E-Mail Removed)> wrote in message
> >> > news:3B08385E-9184-40F4-9FD1-(E-Mail Removed)...
> >> > > When I paste that expression into the top left empty cell of the
> >> > > table,
> >> > > 'internet' appears in the cell, although the edit line still shows
> >> > > =INDIRECT("'"&B$1&"'!B"&ROW()). If pasted anywhere else, '0.00'
> >> > > appears.
> >> > > There is obviously a problem; #1. my ignorance; #2 the table does NOT
> >> > > start
> >> > > at A1.
> >> > > I thought I might help solve it by telling you that the first empty
> >> > > cell
> >> > > is
> >> > > C3; The first three columns of row 2 are : =Jan!$B$4; =Jan!$C$4;
> >> > > =Jan!$D$4
> >> > > and the first three rows of column B are : =Jan!$B$4; =Feb!$B$4;
> >> > > =Mar!$B$4
> >> > > Could you please adapt your 'formula' to fit those details ?
> >> > >
> >> > > "Peese" wrote:
> >> > >
> >> > >> Thanks Roger
> >> > >> The layout is the same for each of the 12 months, but obviously the
> >> > >> name
> >> > >> of
> >> > >> each sheet is different.
> >> > >> At this point it is not obvious to me that your solution will work,
> >> > >> but I
> >> > >> am
> >> > >> excited at the prospect that it might, and will most certainly
> >> > >> report
> >> > >> back
> >> > >> after I have tried it.
> >> > >> I anticipate that it will be a great breakthrough for me if it
> >> > >> works.
> >> > >>
> >> > >> "Roger Govier" wrote:
> >> > >>
> >> > >> > Hi
> >> > >> >
> >> > >> > I assume that the layout is identical on all sheets.
> >> > >> > In which case, the headings in Column A of the Summary sheet are
> >> > >> > not
> >> > >> > required.
> >> > >> > Enter in B2 of Summary
> >> > >> > =INDIRECT("'"&B$1&"'!B"&ROW())
> >> > >> > and copy across and down
> >> > >> > --
> >> > >> >
> >> > >> > Regards
> >> > >> > Roger Govier
> >> > >> >
> >> > >> > "Peese" <(E-Mail Removed)> wrote in message
> >> > >> > news:8C79E8D4-4745-4EA6-B1B6-(E-Mail Removed)...
> >> > >> > > How to AUTOMATICALLY complete an Excel array (or matrix ?) from
> >> > >> > > information
> >> > >> > > in the left column and top row; each cell to hold an
> >> > >> > > incrementally
> >> > >> > > changed
> >> > >> > > formula ?
> >> > >> > > That item/array/matrix is in the 13th sheet of a workbook whose
> >> > >> > > other
> >> > >> > > 12
> >> > >> > > sheets are monthly expenditures in categories.
> >> > >> > > The ITEM should be filled with pasted links from the totals in
> >> > >> > > the 12
> >> > >> > > sheets
> >> > >> > > to create annual totals.
> >> > >> > > Thus the left column contains the links for total expenditures
> >> > >> > > by
> >> > >> > > category
> >> > >> > > for January
> >> > >> > > and the top row contains twelve links for totals in one category
> >> > >> > > for
> >> > >> > > each
> >> > >> > > month.
> >> > >> > >
> >> > >> > > I can (and have) set this up manually, but it seems to me that
> >> > >> > > Excel
> >> > >> > > should
> >> > >> > > be able to fill in the series automatically, expecially for the
> >> > >> > > rows
> >> > >> > > where
> >> > >> > > the only variable item in the link formula is the month name.
> >> > >> > >
> >> > >> > > I am interested because this whole thing has to be redone for
> >> > >> > > each
> >> > >> > > new
> >> > >> > > year.
> >> > >> > >
> >> > >> > > If I had been able to I would have attached the workbook whose
> >> > >> > > completion
> >> > >> > > I
> >> > >> > > want.
> >> > >> >
> >> > >> >
> >> >
>

Hi Joe

File sorted and returned.
I hadn't appreciated that your data was going across the columns on the
Monthly sheets.

The solution (for the benefit of others) was to enter in cell B2
=INDEX(INDIRECT("'"&B$1&"'!$4:$4"),ROW())
and copy down through cells B3:B25 and B28:B29
then copy across through columns C:M

Row 1 of the Data sheet held Month Names (Sheet names)

I also set up another sheet for you Joe, showing how you could enter all
data on a single sheet with Date, Amount and Heading and to be able to
produce a summary with a Pivot Table to report for the 12 months of the
year. Just another alternative
--

Regards
Roger Govier

"Peese" <(E-Mail Removed)> wrote in message
news:450A5730-902A-4C85-9AB6-(E-Mail Removed)...
> Thank you. It is on its way.
> Joe
>
> "Roger Govier" wrote:
>
>> Hi
>>
>> Send me your file directly, and I will take a look
>> To mail direct, send to
>> roger at technology4u dot co dot uk
>> Do the obvious with at and dot
>>
>> --
>>
>> Regards
>> Roger Govier
>>
>> "Peese" <(E-Mail Removed)> wrote in message
>> news:CE92C1BA-DC75-44BE-9E37-(E-Mail Removed)...
>> > Roger
>> > You have given me a formula that seems to relate back directly to every
>> > total on every worksheet. But for that to work, surely you need to know
>> > where
>> > those totals are on each monthly sheet (rather than the irrelevant
>> > information I gave in my last post). The totals are in B4, C4, D4 etc.
>> > Can
>> > you tweak your formula for me, taking that information into account ?
>> > My
>> > efforts have failed.
>> >
>> > I am now confident that your method will work and am grateful for being
>> > shown that technique, but it DOES beg the question of a method to
>> > autocomplete a table with reference to its own contents only. I had
>> > expected
>> > to be shown how to partially or fully trigger autocompletion of the
>> > summary
>> > sheet FROM THE CONTENTS OF THE LEFT COLUMN AND TOP ROW. Do you know if
>> > such a
>> > function exists ?
>> >
>> > "Peese" wrote:
>> >
>> >> Thanks again, Roger
>> >> Your assumption matches the layout of my totals SS, but not of the
>> >> monthly
>> >> sheets.
>> >> There the data does not start until row 6.
>> >> I originally entered your formula into C3 instead of B2 (shame on me
>> >> !).
>> >> When I enter it into B2, there is obviously reproduction of content
>> >> from
>> >> January's sheet but the fit is still not right because the monthly
>> >> data
>> >> starts in row 6. Can you please tweak the formula to accomodate that
>> >> fact
>> >> ?
>> >> We are obviously making good progress. Thank you.
>> >>
>> >> "Roger Govier" wrote:
>> >>
>> >> > Hi
>> >> >
>> >> > What I was assuming was the following
>> >> >
>> >> > A B C
>> >> > 1 Jan Feb
>> >> > 2 Heading 1
>> >> > 3 Heading 2
>> >> >
>> >> > Heading in this case will be the same Cost centre headings that
>> >> > appear
>> >> > on
>> >> > all Sheets.
>> >> > Sheets are assumed to be named Jan, Feb, Mar etc.
>> >> >
>> >> > If they are not named that, just put in B1:M1 etc, the actual name
>> >> > of
>> >> > each
>> >> > of your 12 sheets
>> >> >
>> >> > Now, enter the formula I posted in cell B2 and copy across and down,
>> >> > and you
>> >> > should see the information pulled from each sheet
>> >> >
>> >> > --
>> >> >
>> >> > Regards
>> >> > Roger Govier
>> >> >
>> >> > "Peese" <(E-Mail Removed)> wrote in message
>> >> > news:3B08385E-9184-40F4-9FD1-(E-Mail Removed)...
>> >> > > When I paste that expression into the top left empty cell of the
>> >> > > table,
>> >> > > 'internet' appears in the cell, although the edit line still shows
>> >> > > =INDIRECT("'"&B$1&"'!B"&ROW()). If pasted anywhere else, '0.00'
>> >> > > appears.
>> >> > > There is obviously a problem; #1. my ignorance; #2 the table does
>> >> > > NOT
>> >> > > start
>> >> > > at A1.
>> >> > > I thought I might help solve it by telling you that the first
>> >> > > empty
>> >> > > cell
>> >> > > is
>> >> > > C3; The first three columns of row 2 are : =Jan!$B$4; =Jan!$C$4;
>> >> > > =Jan!$D$4
>> >> > > and the first three rows of column B are : =Jan!$B$4; =Feb!$B$4;
>> >> > > =Mar!$B$4
>> >> > > Could you please adapt your 'formula' to fit those details ?
>> >> > >
>> >> > > "Peese" wrote:
>> >> > >
>> >> > >> Thanks Roger
>> >> > >> The layout is the same for each of the 12 months, but obviously
>> >> > >> the
>> >> > >> name
>> >> > >> of
>> >> > >> each sheet is different.
>> >> > >> At this point it is not obvious to me that your solution will
>> >> > >> work,
>> >> > >> but I
>> >> > >> am
>> >> > >> excited at the prospect that it might, and will most certainly
>> >> > >> report
>> >> > >> back
>> >> > >> after I have tried it.
>> >> > >> I anticipate that it will be a great breakthrough for me if it
>> >> > >> works.
>> >> > >>
>> >> > >> "Roger Govier" wrote:
>> >> > >>
>> >> > >> > Hi
>> >> > >> >
>> >> > >> > I assume that the layout is identical on all sheets.
>> >> > >> > In which case, the headings in Column A of the Summary sheet
>> >> > >> > are
>> >> > >> > not
>> >> > >> > required.
>> >> > >> > Enter in B2 of Summary
>> >> > >> > =INDIRECT("'"&B$1&"'!B"&ROW())
>> >> > >> > and copy across and down
>> >> > >> > --
>> >> > >> >
>> >> > >> > Regards
>> >> > >> > Roger Govier
>> >> > >> >
>> >> > >> > "Peese" <(E-Mail Removed)> wrote in message
>> >> > >> > news:8C79E8D4-4745-4EA6-B1B6-(E-Mail Removed)...
>> >> > >> > > How to AUTOMATICALLY complete an Excel array (or matrix ?)
>> >> > >> > > from
>> >> > >> > > information
>> >> > >> > > in the left column and top row; each cell to hold an
>> >> > >> > > incrementally
>> >> > >> > > changed
>> >> > >> > > formula ?
>> >> > >> > > That item/array/matrix is in the 13th sheet of a workbook
>> >> > >> > > whose
>> >> > >> > > other
>> >> > >> > > 12
>> >> > >> > > sheets are monthly expenditures in categories.
>> >> > >> > > The ITEM should be filled with pasted links from the totals
>> >> > >> > > in
>> >> > >> > > the 12
>> >> > >> > > sheets
>> >> > >> > > to create annual totals.
>> >> > >> > > Thus the left column contains the links for total
>> >> > >> > > expenditures
>> >> > >> > > by
>> >> > >> > > category
>> >> > >> > > for January
>> >> > >> > > and the top row contains twelve links for totals in one
>> >> > >> > > category
>> >> > >> > > for
>> >> > >> > > each
>> >> > >> > > month.
>> >> > >> > >
>> >> > >> > > I can (and have) set this up manually, but it seems to me
>> >> > >> > > that
>> >> > >> > > Excel
>> >> > >> > > should
>> >> > >> > > be able to fill in the series automatically, expecially for
>> >> > >> > > the
>> >> > >> > > rows
>> >> > >> > > where
>> >> > >> > > the only variable item in the link formula is the month name.
>> >> > >> > >
>> >> > >> > > I am interested because this whole thing has to be redone for
>> >> > >> > > each
>> >> > >> > > new
>> >> > >> > > year.
>> >> > >> > >
>> >> > >> > > If I had been able to I would have attached the workbook
>> >> > >> > > whose
>> >> > >> > > completion
>> >> > >> > > I
>> >> > >> > > want.
>> >> > >> >
>> >> > >> >
>> >> >
>>