Using offset in series values of a chart
I have 1 named range created with the offset function on sheet(1):
Simple to use as the x-as categories in a chart.
For the y-axis I want to use data which can be offset 1, 2, up to 20 columns.
Yes I can create 20 named ranges but to do that for 20 sheets in my workbook
Therefor my question:
Can I use the offset function in the series values, like:
In VBA it it can be done with:
..SeriesCollection(2).Values = Month.Offset(0, 17)
But then I have to create one macro for each graph...again cumbersome.
T...Offset of icons in exe files
is there a way to retrieve the offset of the icon section in an exe
Thanks for any clue!
For what purpose? To change the icon? Are you trying to load/extract an
Icon from the exe?
See if LoadResource and FindResource will help you out. Otherwise a little
more info about what you are trying to do will help.
"Kai Sandner" <email@example.com> wrote in message
> is there a way to retrieve the offset of the icon section in an exe
> ...Using Offset
if i have A1 through A5 containing 5,8,8,3,2 and add a new value each day
going straight down like A6 = 7 for tomorrow, if i want cell B1 to return
the value of the last cell in column A, how can i get OFFSET to find the
last value entered in column A?
You can do it without using OFFSET if you wish.
If values are numeric per your example.........
=MATCH(9.99999999999999E+307,A:A) returns row number of last numeric cell in
=ADDRESS(MATCH(9.99999999999999E+307,A:A),1) returns address of last numeric
cell in column A
=LOOKUP(9.99999999999999E+307,A:A) returns the last valu...Compaction Failure
I recently had a compaction process failure because when I was presented
with the dialogue box that asked if I wanted to let OE perform a compaction,
I mistakenly clicked on "OK" instead of "Cancel". For whatever reason (maybe
because OE was online and my PC was busy) the compaction failed and reported
that file "folders.dbx" was in use.
Fortunately I backup (copy) my "OE Identity" every time I start my PC so I
had a recent copy of my files and folders. However, rather than lose the
most recent emails - the ones that had arrived since the backup...Text Boxes Randomly Offset After Saving
Hi, I'm using Excel 2007 and I'm using text boxes in a visual hierarchy
system on a spreadsheet. When I save, everything looks nice and orderly but
when I open the spreadsheet again, some text boxes have jumped around and the
lines that were snapped to them don't look like they're snapped anymore.
Then when I move the text boxes a little bit, the lines immediately snap to
them again. Some text boxes are even all the way in the bottom corner,
I've tried grouping and ungrouping text boxes but it doesn't seem to do
much. I am using the "...creating columns with data from offset rows
I have 5 "channels" of data that I'm bringing into a spreadsheet. Each of
these channels is represented by numbers in two consecutive rows of column C
that I then need to combine to one number. Example, C5 & C6 / C7 & C8 / C9
& C10 / C11 & C12 / C13 & C14 then starting over with C15 & C16.
I want to combine these sets of cells into new columns D thru H.
Column D for instance would consist of:
D5 C5 * 100 + C6
D6 C15 * 100 + C16
D7 C25 * 100 + C26
Column E will consist of
E5 C7 * 100 +C8
E6 C17 *100 +C18
E7 C27 *100 +C28
and so on
What...SUMIF with OFFSET
I am trying to find a way to use SUMIF with OFFSET on a range of variable
length. My data is organized in this manner:
Each row represents one fee charged to a client on a particular date.
Column E contains the names of the fees that are charged. Each client has
at least one entry for Fee1 through Fee5. The number of rows for any
particular client may increase during the year.
A blank row will separate clients from each other (Row 18 in this example).
There may be more than one blank row between clients.
Rows 9 - 17 represent fees charged to one client (Client 1)....Offset Question #3
Was trying to do this myself, thought the logic was there but
obviously I'm missing something still.
Basically want to have a chart automatically update to show additional
rows (therefore months) depending on the current month.
Table goes from B6:H18 (Row 6 Contains titles:
Rows 7:18 contain the appropriate data for each month, column B
containing all months.
Was hoping to have the chart basically recognise when a new months of
data should be shown (e.g. currently May so only show data up to row
=OFFSET(Ma...Offset secondary axis
1. How do I move secondary axis so that it'll be displayed together
with the primary axis?
2. Is it possible to offset secondary axis so that it seems to be
continuing from primary axis?
the reason is because i'm plotting a value for 'H/D' which goes from 0
to infinity,.. but I'll break it at 'H/D'=5, and start with its
so the H/D = 5 equals to D/H = 0.2 and
H/D = infinity equals to D/H = 0
pls kindly help... or any other suggestion...
Posted ...Offset Problem
This formula linked to another spreadsheet previously worked, but a ne
workbook has been created and the formula now returns a "0" value to th
No errors etc are shown with the formula. The source date value is (eg
123 yet the link returns 0.
Any help or advice will be appreciated.
morrida3's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2726
View this thread: http://www.excelforum.com/sh...formula to look up ref in one sheet and offset in another
I am trying to create a formula which will use a product code typed into a
cell in the same sheet, look up this code in a separate file, and give me the
sales figure which is offset from this code.
I.e. (if this helps)
This is the formula I have at the moment just using the other file:
=OFFSET('[Budget Tracker Master Copy 2010.xlsm]Total by Product'!$EJ$5,739,3)
but I want the code which is in EJ5 to come from my current sheet which is
in cell B8.
The range to look up in the Budget Tracker sheet would be $B$5:$MS$5
Hope this makes sense! Thanks
=offset() i...Vlookup and offset
I would like to lookup a users ID in a table and check if they have a
paticular qualification, a normal VLOOKUP will do that but then I would like
to refer to a date in the header to see when they gained the qualification
so the cell can determine whether they are qualified based on the current
date. I could do this without checking the date but that would disguise the
lack of skills for the rest of the past records as soon as the skill was
I think I need to combine an IF, VLOOKUP and an OFFSET but am at a loss as
to how to put it all together.
Hope someone can help.
...Broken/offset X-axis on a time scale
I am trying draw line charts with a broken/offset x-axis
with the x-axis being on a time-scale.
I am mainly trying to show time kinetics of immune data at
first few time points (2, 5, 9 and 12 weeks) and then
split the graph and show data for same individual at 1, 2,
and 3 years down the line.
One way I am trying to do this is by changing the dates
for the week time points to months so that that portion of
the graph is stretched out and then manually labelling the
time points as weeks and years. But the graph dosent look
Could you let me know if there is a better way ...Target cell by "offset" of named intersection?
How do I refer to a cell by using an offset reference to another?
My current targets are referred to by intersection off named columns and
eg: =monthtotal_JUL stock_bought gives me intersection of row
"monthtotal" and column "stock_bought", but now I want another reference to
cells above the "monthtotal" row (still in the stock_bought column) without
having to name the rows separately.
I played with the R1C1 convention ( R[-5] etc) but I'm not sure it's
compatible with the intersecting name reference, or if it is, just where to
put the offset w...Is there a way to offset the y-axis of a chart without having the.
I'm making a xy scatter graph for my boss. She wants me to offset the y-axis
to the right a little, but does not want anything to the left of it. Is
there a way to do this in Excel?
Do you want to make the Plot Area narrower? Select the plot area and drag the little
Or do you want the minimum of the X axis to be larger, a positive number instead of
zero? Double click on the X axis, click on the Scale tab, and choose appropriate
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.co...offset not working
I have the following that does work:
I have the following which does not work:
D and C 24 obviously stay the same.
My value for B24 is 'Revenue Assumptions'!D76
Any idea why the offset is not working when using for its reference point
the written name of a cell?
"BorisS" <BorisS@discussions.microsoft.com> wrote in message
>I have the following that does work:
> =OFFSET('Revenue Assumptions'!D76,D2...Find a number then offset
In cell Z34, how do I find a number from 1 to 12 (say 9 which could
be anywhere in column B5:B30) then OFFSET from that to find the result
to put in Z34?
e.g."=FIND(9) in B5:B30 then OFFSET(9,20,2)"
"Barneypo@gmail.com" <firstname.lastname@example.org> wrote in message
> In cell Z34, how do I find a number from 1 to 12 (say 9 which could
> be anywhere in column B5:B30) then OFFSET from that to...Use vlookup within offset?
Can I use a vlookup within an offset formula to define the reference? I want
to find a value in a column on a tab, go down one, and record the anwser.
On sheet2 in cell c7 I type the formula:
b7 is defined by another lookup formula so it's variable depending on an
I've used index/match successfully for this purpose, but vlookup gives me
"the formula you typed contains an error." with no explanation of the error.
What I'm doing is converting a column of entries on sheet1 to a horizontal...how to know the offset of title in text file
here ,I have a text file. In my text file,have some paragraph and each
paragraph has a Title.
How Can I know what each paragraph's offset and is in the text file and
length is .
It is appreciated for someone answer!
You have to know what defines a paragraph. If it is a special character,
you can parse the file looking for said character.
"terrcy" <email@example.com> wrote in message
> Dear All:
> here ,I have a text file. In my text file,have some paragraph and each
> paragraph has a...How do I offset TWO columns behind another TWO columns?
I have created a bar chart with 4 colums of information. I would lik
two of the columns to be behind the other two colums. Can anyone hel
me with how to do this.
I have tried creating the chart then offsetting but obviously all th
coumns offset behind each other.
Thanks in anticipation
Moxy1980's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2807
View this thread: http://www.excelforum.com/showthread.php?threadid=47908
Set the two columns you want in front to be on the s...Offset and If Statements
I am using an Offset forumula to bring back data in a rank ordered format.
I am using the following formula, which is working fine:
However, the file that this is in is going to be used as a template, for an
automated workbook (to be used with many different sets of data). On other
occasions though the list (locationranklist) will be shorter than on the
Where data is then not included (because the list is shorter) it brings back
a zero. i do not want to be able to see zero's as charts are being produced
fro...Offsetting x axis chart labels
Please help - I'm really stuck with an approaching deadline...
I'm doing a column chart with negative and positive bars, with the
axis crossing at 0. My x axis labels need to be far enough down fro
the columns so that they don't overlap the negative ones, but th
offset feature only goes up to 1000 and this isn't enough. I've trie
hardcoding spaces into the source cells for the labels to offset the
further but although it works fine on screen and aligns the tex
perfectly, in print preview and when actually printing the labels don'
Any help gratefully appre...Data in table, may need to convert to columns with OFFSET?
I have data in the following format:
Column A is a numerical range from a2-a70
Row 1 is a numerical range of data from b1 - cc1
The data in between "row 1" and "column a" is the recorded data I need
(This data is the recording of energy directed at a cellphone antenna
and Column A is the 'angle' at which the energy strikes the widget,
with row 1 being the amount of energy fired at the widget. The result
is shown in the corresponding cell.)
(Column A) (B) (C) (D) (E) (F)
angle/energy 1 2 3 4 5
-3 (resu...Excel radial diagram text offset
I have a radial diagram. In this diagram the value arrays are displayed in
relation to there total sum. The values of the arrays are displayed as
absolute number not in percent of the total sum.
How can I get the total sum of all the arrays to be displaed in the diagram
without to be forced to calculate it separately?
A chart does no calculations for you. If you want a value in the chart, you
must calculate it, and point the chart at the calculation.
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_...OFFSET Formula Trouble
Maybe someone can help me with this formula:
=OFFSET('NM On time'!$B$25,0,0,COUNTA('NM On time'!$B$25:$J$25),9)
I have defined a name for this formula, to utilize as a criteria rang
in a graph. Don't ask me why I can't just grab the data I need, I'
doing this for someone else, has something to do with altering th
data. Anyways, my problem is that this formula is meant to grab
"header" for a list of store numbers like so...
95 | 60 | 46 | 50 | 8 | 98 | 90 | 65 | 54
There are nine store numbers, 9 columns across.
Obviously, there is just 1 row contai...