I am using external data to populate one spreadsheet, and then I link to this
data in several other spreadsheets. The only problem, is that on occasion,
the format of the external data changes (eg. 1 less column of data), and this
in turn upsets my other spreadsheets as they are linked to the spreadsheet
which is populated with the external data. I have tried to play around with
some lookups, but as I will have to lookup the month (the row heading) and
the column heading, I cannot seem to create anything that is meaningful.
Hoping someone can help
Thank you

Ideally, you would avoid having an unpredicatble structure change in your
workbooks that must interact with one another. After the fact, have you
experimented with named ranges yet?

I have a workbook that adds lines into a lookup table so that I can add data
without having to manually alter the table or insert the line myself (I like
gadgets). I use a named range as a placeholder so that I can expand the table
and insert the new line as the last row (which varies each time a line is
added). I can always refer to the named cell -1 and I automatically have the
last row. I would imagine this would work for columns equally as well. How
complex is your dat set? If you physically remove a column from a workbook,
naming the ranges in advance should work by itself and then referring to the
ranges or for example "MyNamedRange.Column". If you are simply writing the
data into different columns, then maybe you could implement a process to name
the ranges, either by command button, or automatically on the workbook close
event, etc., and then the main workbook would find the right data with no
problem.

Is your data strictly in columns? Does it have predictable column headings,
so that a procedure could figure out how to name the ranges consistently?
Does the column length vary?

I think this is doable.

Roy




--
(delete .nospam)




"ac512" wrote:

I am using external data to populate one spreadsheet, and then I link to this
data in several other spreadsheets. The only problem, is that on occasion,
the format of the external data changes (eg. 1 less column of data), and this
in turn upsets my other spreadsheets as they are linked to the spreadsheet
which is populated with the external data. I have tried to play around with
some lookups, but as I will have to lookup the month (the row heading) and
the column heading, I cannot seem to create anything that is meaningful.
Hoping someone can help
Thank you

Thanks for your response Roy; much appreciated.

In terms of the data I am using, it has month as the row heading, and then
it has many column headings, with sub-headings as well. EG. one column
heading may be "paid volumes" and then this has 2 sub-headings called
"actual" and "year-to-date". Then there may be another column heading called
"paid budget", with the same 2 sub-headings.

Your suggestion seems like a good one, except it may be a little complex
with the sub-headings. Also, I don't know how to write a procedure that
would name the ranges.


"Roy Wagner" wrote:

Ideally, you would avoid having an unpredicatble structure change in your
workbooks that must interact with one another. After the fact, have you
experimented with named ranges yet?

I have a workbook that adds lines into a lookup table so that I can add data
without having to manually alter the table or insert the line myself (I like
gadgets). I use a named range as a placeholder so that I can expand the table
and insert the new line as the last row (which varies each time a line is
added). I can always refer to the named cell -1 and I automatically have the
last row. I would imagine this would work for columns equally as well. How
complex is your dat set? If you physically remove a column from a workbook,
naming the ranges in advance should work by itself and then referring to the
ranges or for example "MyNamedRange.Column". If you are simply writing the
data into different columns, then maybe you could implement a process to name
the ranges, either by command button, or automatically on the workbook close
event, etc., and then the main workbook would find the right data with no
problem.

Is your data strictly in columns? Does it have predictable column headings,
so that a procedure could figure out how to name the ranges consistently?
Does the column length vary?

I think this is doable.

Roy




--
(delete .nospam)




"ac512" wrote:

I am using external data to populate one spreadsheet, and then I link to this
data in several other spreadsheets. The only problem, is that on occasion,
the format of the external data changes (eg. 1 less column of data), and this
in turn upsets my other spreadsheets as they are linked to the spreadsheet
which is populated with the external data. I have tried to play around with
some lookups, but as I will have to lookup the month (the row heading) and
the column heading, I cannot seem to create anything that is meaningful.
Hoping someone can help
Thank you

A quick way to learn new tricks in Excel is to do it with the macro recorder.
It will generate the frame work and you can alter it to achieve the
additional functionality.

Here is the macro recorder output for naming a small block of cells. You
would replace the fixed range selection A1:D10 and MyNamedRange with
variables you calulate in code.

Range("A1:D10").Select
ActiveWorkbook.Names.Add Name:="MyNamedRange", RefersToR1C1:= _
"=Sheet1!R1C1:R10C4"

Roy
--
(delete .nospam)




"ac512" wrote:

Thanks for your response Roy; much appreciated.

In terms of the data I am using, it has month as the row heading, and then
it has many column headings, with sub-headings as well. EG. one column
heading may be "paid volumes" and then this has 2 sub-headings called
"actual" and "year-to-date". Then there may be another column heading called
"paid budget", with the same 2 sub-headings.

Your suggestion seems like a good one, except it may be a little complex
with the sub-headings. Also, I don't know how to write a procedure that
would name the ranges.


"Roy Wagner" wrote:

Ideally, you would avoid having an unpredicatble structure change in your
workbooks that must interact with one another. After the fact, have you
experimented with named ranges yet?

I have a workbook that adds lines into a lookup table so that I can add data
without having to manually alter the table or insert the line myself (I like
gadgets). I use a named range as a placeholder so that I can expand the table
and insert the new line as the last row (which varies each time a line is
added). I can always refer to the named cell -1 and I automatically have the
last row. I would imagine this would work for columns equally as well. How
complex is your dat set? If you physically remove a column from a workbook,
naming the ranges in advance should work by itself and then referring to the
ranges or for example "MyNamedRange.Column". If you are simply writing the
data into different columns, then maybe you could implement a process to name
the ranges, either by command button, or automatically on the workbook close
event, etc., and then the main workbook would find the right data with no
problem.

Is your data strictly in columns? Does it have predictable column headings,
so that a procedure could figure out how to name the ranges consistently?
Does the column length vary?

I think this is doable.

Roy




--
(delete .nospam)




"ac512" wrote:

I am using external data to populate one spreadsheet, and then I link to this
data in several other spreadsheets. The only problem, is that on occasion,
the format of the external data changes (eg. 1 less column of data), and this
in turn upsets my other spreadsheets as they are linked to the spreadsheet
which is populated with the external data. I have tried to play around with
some lookups, but as I will have to lookup the month (the row heading) and
the column heading, I cannot seem to create anything that is meaningful.
Hoping someone can help
Thank you

Thanks again for your prompt response Roy. I will give a few of your
suggestions a go.


"Roy Wagner" wrote:

A quick way to learn new tricks in Excel is to do it with the macro recorder.
It will generate the frame work and you can alter it to achieve the
additional functionality.

Here is the macro recorder output for naming a small block of cells. You
would replace the fixed range selection A1:D10 and MyNamedRange with
variables you calulate in code.

Range("A1:D10").Select
ActiveWorkbook.Names.Add Name:="MyNamedRange", RefersToR1C1:= _
"=Sheet1!R1C1:R10C4"

Roy
--
(delete .nospam)




"ac512" wrote:

Thanks for your response Roy; much appreciated.

In terms of the data I am using, it has month as the row heading, and then
it has many column headings, with sub-headings as well. EG. one column
heading may be "paid volumes" and then this has 2 sub-headings called
"actual" and "year-to-date". Then there may be another column heading called
"paid budget", with the same 2 sub-headings.

Your suggestion seems like a good one, except it may be a little complex
with the sub-headings. Also, I don't know how to write a procedure that
would name the ranges.


"Roy Wagner" wrote:

Ideally, you would avoid having an unpredicatble structure change in your
workbooks that must interact with one another. After the fact, have you
experimented with named ranges yet?

I have a workbook that adds lines into a lookup table so that I can add data
without having to manually alter the table or insert the line myself (I like
gadgets). I use a named range as a placeholder so that I can expand the table
and insert the new line as the last row (which varies each time a line is
added). I can always refer to the named cell -1 and I automatically have the
last row. I would imagine this would work for columns equally as well. How
complex is your dat set? If you physically remove a column from a workbook,
naming the ranges in advance should work by itself and then referring to the
ranges or for example "MyNamedRange.Column". If you are simply writing the
data into different columns, then maybe you could implement a process to name
the ranges, either by command button, or automatically on the workbook close
event, etc., and then the main workbook would find the right data with no
problem.

Is your data strictly in columns? Does it have predictable column headings,
so that a procedure could figure out how to name the ranges consistently?
Does the column length vary?

I think this is doable.

Roy




--
(delete .nospam)




"ac512" wrote:

I am using external data to populate one spreadsheet, and then I link to this
data in several other spreadsheets. The only problem, is that on occasion,
the format of the external data changes (eg. 1 less column of data), and this
in turn upsets my other spreadsheets as they are linked to the spreadsheet
which is populated with the external data. I have tried to play around with
some lookups, but as I will have to lookup the month (the row heading) and
the column heading, I cannot seem to create anything that is meaningful.
Hoping someone can help
Thank you