Updating charts with fixed cells

[falcon4]

I have some charts whose values and axis labels comes from cells in 4 rows. (10 cells in each row).

I have a macro which will insert a new column of 4 cells, moving the existing cells right.

my goal is to have the chart reflect only the most current set of data (10 cells) ignoring the oldest, and updating each time the macro adds a new set.

When I execute the macro the chart currently follows the same sets of cells as they move right, thus it never changes.

the selections under the values and labels have $ references, but they do not react like i expect

Thanks for any ideas,

Gary

 

[SkipVought]

Hi,

I almost NEVER use insert!!!

Append the data to the END (left) of the table and then SORT into order horizontally.

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[SkipVought]

The other matter is, where are you copying data from?

Copy 'n' Paste is something else that I rarely do. I GET data from other places, but I rarely COPY and PASTE, which is usually time consuming AND prone to error.

I get data from other places using sheet formulas, PivotTables, or MS Query. faq68-5829

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[falcon4]

I'm inserting because i'm creating empty cells to be populated with data collected from the operating floor.

it does not exist yet in electronic form until entered by the technician. Inserting allows older data to trailoff the sheet to the right, and still avaialble if needed at a later time.

I found a link on dynamic ranges that may work. But they reference a range of cells all in one column, i need the range in a row.

 

[SkipVought]

"I'm inserting because..." does not matter. You can still SORT this column into place.

If you want to go the Dynamic Range Name, post your example (probably the OFFSET formula) and describe your sheet layout.

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[falcon4]

this was copied from microsofts site

Name Definition
Date =OFFSET(Sheet1!$A$1,COUNTA($A:$A)-30,0,30
Price =OFFSET(Sheet1!$B$1,COUNTA($B:$B)-30,0,30)

These formulas tell Excel to start at the 30th row from the end of the populated area and create a range encompassing 30 rows and 1 column.

i have 4 rows labels
JOB
FPY1
FPY2
RTY
each column will start to be populted with data starting with the JOB number filled in. as each job is completed i would execute the macro adding a new set of cells for the next job.

 

[SkipVought]

You did not tell me what COLUMN and ROW each of these starts in.

If any of these are not in row 1, what is in row 1.

Does EVERY CELL have a data value for the row headings you posted?

Some sample data (copy 'n' paste) would be helpful.

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[falcon4]

JOB 250 198 212 211 232 233
FPY1 0.8 0.5 1 0.7 0.8 0.9
FPY2 0.3 0.9 0.9 0.7 0.8 0.9
RTY 0.24 0.45 0.9 0.49 0.64 0.81
This data starts in row 11, and Column B (B11 contents is JOB)

Row 1 contains some Product line information, nothing that changes on any basis. Likewise down thru to row 11.

Yes, there will be a changing value for each of the row labels at update

Gary

 

[SkipVought]

Insert > Name > Define...[/n]
Names in workbook JOB

Referrs to:
[tt]
=OFFSET(Sheet1!$B$11,0,1,1,10)
[/tt]
Assuming that your sheet name is Sheet1, meaning
[tt]
OFFSET(
Sheet1!$B$11 anchor reference
,0 ZERO rows offset
,1 ONE column offset
,1 ONE row deep
,10) TEN columns wide
[/tt]
Hit the ADD button.

Then enter the NEXT name in the Names in Workbook and CHANGE the ROW OFFSET from ZERO to ONE, etc...
In your Chart > Source Data > Series Tab

Values box Sheet1!JOB

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[falcon4]

Thanks Skip,


So far works great, and will save alot of updating time later.

Gary