In another post I got an Offset answer, but what I ultimately want to do and
can't seem to get to is:

On Sheet YTD, look up Cell A10 (Smith) in the range "Budget", (Smith is
found in cell A13 in the range), then calculate this offset for the Row A13
in the range "Budget"

=SUM(OFFSET(A13,0,0,1,A1-4))

And change the row number based on the look up.
TIA!

Oops - I wrote the Offset wrong, s/b =SUM(OFFSET(E13,0,0,1,A1-4))

I can't seem to get match or index to work.

"Karin" wrote:

In another post I got an Offset answer, but what I ultimately want to do and
can't seem to get to is:

On Sheet YTD, look up Cell A10 (Smith) in the range "Budget", (Smith is
found in cell A13 in the range), then calculate this offset for the Row A13
in the range "Budget"

=SUM(OFFSET(A13,0,0,1,A1-4))

And change the row number based on the look up.
TIA!

I had asked this question inside of another question and received the
following answer, but I can't get it to work. (I felt that the "new"
question went well beyond the first question and should have it's own
posting.)

Jim Rech" wrote:

Karin, play with something like this:

=SUM(OFFSET(indirect(vlookup(...)),0,0,1,MOD(A1-4,12)))

Use VLOOKUP to get the name of the range and INDIRECT to make Excel find the
range with that name.

=SUM(OFFSET(E13,0,0,1,A1-4))

What's in A1?


--
Biff
Microsoft Excel MVP


"Karin" wrote in message
...
Oops - I wrote the Offset wrong, s/b =SUM(OFFSET(E13,0,0,1,A1-4))

I can't seem to get match or index to work.

"Karin" wrote:

In another post I got an Offset answer, but what I ultimately want to do
and
can't seem to get to is:

On Sheet YTD, look up Cell A10 (Smith) in the range "Budget", (Smith is
found in cell A13 in the range), then calculate this offset for the Row
A13
in the range "Budget"

=SUM(OFFSET(A13,0,0,1,A1-4))

And change the row number based on the look up.
TIA!

In a separate question I looked for the the formula I received below (and it
works great). here is that question:
I have columns 5-16 (E:P) filled with monthly data.
Column 5 (E) contains May, column 6 (F) June, etc.
In Cell A1 I have the current month (7 for July, 8 for Aug, etc.).

I want to sum cells E5 through ?5 based on the number in cell A1.
For July the sum would be E5:G5, for August it would be E5:H5, etc.)
What fomula would I use to create the sum based on the data in cell A1?

But I can't seem to get to what I really want which is a lookup that returns
the offset formula.

Full story: I have a sheet (YTD) that totals year to date budget numbers by
employee. I have another sheet (Budget) that has the budget numbers for each
month by employee. Then I have an additional column for each month that
totals year to date (our year is May-April), so I have a May-Jun column,
May-Jul, May-Aug, May-Sep.

In A1 of the Budget sheet I manually place the column number of the current
month. (July happens to be 7). In Cell A2 I place the column number of the
YTD column that I want for the month (May-Jul is column 18).

This lookup gives me the total ytd: VLOOKUP($A10,Budget,Budget!$A$2,FALSE)
(It looks up the employee in A10, goes to the Budget range, and returns the
column number from A2).

I was trying to avoid having a separate column for each YTD total and the
OFFSET accomplished that nicely, but I can't seem to make it work with the
lookup.



"T. Valko" wrote:

=SUM(OFFSET(E13,0,0,1,A1-4))

What's in A1?


--
Biff
Microsoft Excel MVP


"Karin" wrote in message
...
Oops - I wrote the Offset wrong, s/b =SUM(OFFSET(E13,0,0,1,A1-4))

I can't seem to get match or index to work.

"Karin" wrote:

In another post I got an Offset answer, but what I ultimately want to do
and
can't seem to get to is:

On Sheet YTD, look up Cell A10 (Smith) in the range "Budget", (Smith is
found in cell A13 in the range), then calculate this offset for the Row
A13
in the range "Budget"

=SUM(OFFSET(A13,0,0,1,A1-4))

And change the row number based on the look up.
TIA!

Ok...

You don't need those extra columns.

On your Budget sheet is there data for all months or just for the months
that have passed starting from May (the start of *your* year)? In other
words, so far, you only have data for May, June and July?

On sheet Budget, you have column headers in E4:P4 - May, June, July, August
.... April

On sheet Budget, you have names in A5:An

On sheet YTD, you have some name in A10

You want the YTD sum for the name in A10:

=SUM(INDEX(Budget!E5:P10,MATCH(A1,Budget!A5:A10,0) ,))


--
Biff
Microsoft Excel MVP


"Karin" wrote in message
...
In a separate question I looked for the the formula I received below (and
it
works great). here is that question:
I have columns 5-16 (E:P) filled with monthly data.
Column 5 (E) contains May, column 6 (F) June, etc.
In Cell A1 I have the current month (7 for July, 8 for Aug, etc.).

I want to sum cells E5 through ?5 based on the number in cell A1.
For July the sum would be E5:G5, for August it would be E5:H5, etc.)
What fomula would I use to create the sum based on the data in cell A1?

But I can't seem to get to what I really want which is a lookup that
returns
the offset formula.

Full story: I have a sheet (YTD) that totals year to date budget numbers
by
employee. I have another sheet (Budget) that has the budget numbers for
each
month by employee. Then I have an additional column for each month that
totals year to date (our year is May-April), so I have a May-Jun column,
May-Jul, May-Aug, May-Sep.

In A1 of the Budget sheet I manually place the column number of the
current
month. (July happens to be 7). In Cell A2 I place the column number of
the
YTD column that I want for the month (May-Jul is column 18).

This lookup gives me the total ytd: VLOOKUP($A10,Budget,Budget!$A$2,FALSE)
(It looks up the employee in A10, goes to the Budget range, and returns
the
column number from A2).

I was trying to avoid having a separate column for each YTD total and the
OFFSET accomplished that nicely, but I can't seem to make it work with the
lookup.



"T. Valko" wrote:

=SUM(OFFSET(E13,0,0,1,A1-4))

What's in A1?


--
Biff
Microsoft Excel MVP


"Karin" wrote in message
...
Oops - I wrote the Offset wrong, s/b =SUM(OFFSET(E13,0,0,1,A1-4))

I can't seem to get match or index to work.

"Karin" wrote:

In another post I got an Offset answer, but what I ultimately want to
do
and
can't seem to get to is:

On Sheet YTD, look up Cell A10 (Smith) in the range "Budget", (Smith
is
found in cell A13 in the range), then calculate this offset for the
Row
A13
in the range "Budget"

=SUM(OFFSET(A13,0,0,1,A1-4))

And change the row number based on the look up.
TIA!

Typo correction:

You want the YTD sum for the name in A10:
=SUM(INDEX(Budget!E5:P10,MATCH(A1,Budget!A5:A10,0) ,))

Should be:

=SUM(INDEX(Budget!E5:P10,MATCH(A10,Budget!A5:A10,0 ),))


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Ok...

You don't need those extra columns.

On your Budget sheet is there data for all months or just for the months
that have passed starting from May (the start of *your* year)? In other
words, so far, you only have data for May, June and July?

On sheet Budget, you have column headers in E4:P4 - May, June, July,
August ... April

On sheet Budget, you have names in A5:An

On sheet YTD, you have some name in A10

You want the YTD sum for the name in A10:

=SUM(INDEX(Budget!E5:P10,MATCH(A1,Budget!A5:A10,0) ,))


--
Biff
Microsoft Excel MVP


"Karin" wrote in message
...
In a separate question I looked for the the formula I received below (and
it
works great). here is that question:
I have columns 5-16 (E:P) filled with monthly data.
Column 5 (E) contains May, column 6 (F) June, etc.
In Cell A1 I have the current month (7 for July, 8 for Aug, etc.).

I want to sum cells E5 through ?5 based on the number in cell A1.
For July the sum would be E5:G5, for August it would be E5:H5, etc.)
What fomula would I use to create the sum based on the data in cell A1?

But I can't seem to get to what I really want which is a lookup that
returns
the offset formula.

Full story: I have a sheet (YTD) that totals year to date budget numbers
by
employee. I have another sheet (Budget) that has the budget numbers for
each
month by employee. Then I have an additional column for each month that
totals year to date (our year is May-April), so I have a May-Jun column,
May-Jul, May-Aug, May-Sep.

In A1 of the Budget sheet I manually place the column number of the
current
month. (July happens to be 7). In Cell A2 I place the column number of
the
YTD column that I want for the month (May-Jul is column 18).

This lookup gives me the total ytd:
VLOOKUP($A10,Budget,Budget!$A$2,FALSE)
(It looks up the employee in A10, goes to the Budget range, and returns
the
column number from A2).

I was trying to avoid having a separate column for each YTD total and the
OFFSET accomplished that nicely, but I can't seem to make it work with
the
lookup.



"T. Valko" wrote:

=SUM(OFFSET(E13,0,0,1,A1-4))

What's in A1?


--
Biff
Microsoft Excel MVP


"Karin" wrote in message
...
Oops - I wrote the Offset wrong, s/b =SUM(OFFSET(E13,0,0,1,A1-4))

I can't seem to get match or index to work.

"Karin" wrote:

In another post I got an Offset answer, but what I ultimately want to
do
and
can't seem to get to is:

On Sheet YTD, look up Cell A10 (Smith) in the range "Budget", (Smith
is
found in cell A13 in the range), then calculate this offset for the
Row
A13
in the range "Budget"

=SUM(OFFSET(A13,0,0,1,A1-4))

And change the row number based on the look up.
TIA!

Hi, and thank you very much for helping. In response to your questions:

On your Budget sheet is there data for all months or just for the months that have passed starting from May (the start of *your* year)? In other words, so far, you only have data for May, June and July?
I have all the data for the whole year (May-Apr), columns E:P are filled in

On sheet Budget, you have column headers in E4:P4 - May, June, July, August ... April
I have column names May-April in cells E10:P10 (on Budget sheet)
I have column numbers in E9:P9. (5-16)
(A1 references the column number, not the month name [this makes it work
with a vlookup])

On sheet Budget, you have names in A5:An
I have names in A11:Awhatever
FYI: I have named A11:AJ203 as a range: "BudgData" (this incorporates the
extra totalling columns)

On sheet YTD, you have some name in A10
On sheet YTD I have names in column A, rows 10 through whatever (YTD Names
match the names on the budget sheet exactly. Budget sheet is sorted alpha.)

You want the YTD sum for the name in A10:
Yes (sum the budget YTD on sheet YTD for the name in A10 and down)

=SUM(INDEX(Budget!E5:P10,MATCH(A1,Budget!A5:A10, 0),))

I'd like to understand the formula:
(Budget!E5:P10 - the cell refs confuse me, why are we going E5 to P10?
Wouldn't it be E5 to P5? (based on where you thought my column names were?
- otherwise it would be E10:P10 for where they actually are? And since I'm
actually matching column numbers not month names it would be E9:P9?)

MATCH(A1,Budget!A5:A10,0),))
Ok, we're matching the manual entry I have in A1 for the month we are in,
but what is the remainder of the formula doing? Why are we referencing
Budget!A5:A10?


I love learning this stuff and reallly appreciate the help immensely.

Karin


"T. Valko" wrote:

Typo correction:

You want the YTD sum for the name in A10:
=SUM(INDEX(Budget!E5:P10,MATCH(A1,Budget!A5:A10,0) ,))

Should be:

=SUM(INDEX(Budget!E5:P10,MATCH(A10,Budget!A5:A10,0 ),))


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Ok...

You don't need those extra columns.

On your Budget sheet is there data for all months or just for the months
that have passed starting from May (the start of *your* year)? In other
words, so far, you only have data for May, June and July?

On sheet Budget, you have column headers in E4:P4 - May, June, July,
August ... April

On sheet Budget, you have names in A5:An

On sheet YTD, you have some name in A10

You want the YTD sum for the name in A10:

=SUM(INDEX(Budget!E5:P10,MATCH(A1,Budget!A5:A10,0) ,))


--
Biff
Microsoft Excel MVP


"Karin" wrote in message
...
In a separate question I looked for the the formula I received below (and
it
works great). here is that question:
I have columns 5-16 (E:P) filled with monthly data.
Column 5 (E) contains May, column 6 (F) June, etc.
In Cell A1 I have the current month (7 for July, 8 for Aug, etc.).

I want to sum cells E5 through ?5 based on the number in cell A1.
For July the sum would be E5:G5, for August it would be E5:H5, etc.)
What fomula would I use to create the sum based on the data in cell A1?

But I can't seem to get to what I really want which is a lookup that
returns
the offset formula.

Full story: I have a sheet (YTD) that totals year to date budget numbers
by
employee. I have another sheet (Budget) that has the budget numbers for
each
month by employee. Then I have an additional column for each month that
totals year to date (our year is May-April), so I have a May-Jun column,
May-Jul, May-Aug, May-Sep.

In A1 of the Budget sheet I manually place the column number of the
current
month. (July happens to be 7). In Cell A2 I place the column number of
the
YTD column that I want for the month (May-Jul is column 18).

This lookup gives me the total ytd:
VLOOKUP($A10,Budget,Budget!$A$2,FALSE)
(It looks up the employee in A10, goes to the Budget range, and returns
the
column number from A2).

I was trying to avoid having a separate column for each YTD total and the
OFFSET accomplished that nicely, but I can't seem to make it work with
the
lookup.



"T. Valko" wrote:

=SUM(OFFSET(E13,0,0,1,A1-4))

What's in A1?


--
Biff
Microsoft Excel MVP


"Karin" wrote in message
...
Oops - I wrote the Offset wrong, s/b =SUM(OFFSET(E13,0,0,1,A1-4))

I can't seem to get match or index to work.

"Karin" wrote:

In another post I got an Offset answer, but what I ultimately want to
do
and
can't seem to get to is:

On Sheet YTD, look up Cell A10 (Smith) in the range "Budget", (Smith
is
found in cell A13 in the range), then calculate this offset for the
Row
A13
in the range "Budget"

=SUM(OFFSET(A13,0,0,1,A1-4))

And change the row number based on the look up.
TIA!

All the range references used in my suggested formula are made up. Since you
didn't provide those details before I can only guess where your data might
be! As far as I knew, rhe data was somewhere in column E through P.

Let's try a different approach to this.

I put together a small sample file that demonstrates this. I put everything
on a single sheet so you can see it without having to jump between different
sheets.

The name in A3 would be the names on your YTD sheet. The formula returns the
YTD sum based on May being the start of your year. Since this is July the
sum is for May - July for the selected name. Notice that I'm using the short
month names. If you're using the long month names all you need to do is
change this portion of the formula:

TEXT(NOW(),"mmm")

To:

TEXT(NOW(),"mmmm")

Sample file:

http://cjoint.com/?hzw25cbwK7

--
Biff
Microsoft Excel MVP


"Karin" wrote in message
...
Hi, and thank you very much for helping. In response to your questions:

On your Budget sheet is there data for all months or just for the
months that have passed starting from May (the start of *your* year)?
In other words, so far, you only have data for May, June and July?
I have all the data for the whole year (May-Apr), columns E:P are filled
in

On sheet Budget, you have column headers in E4:P4 - May, June, July,
August ... April
I have column names May-April in cells E10:P10 (on Budget sheet)
I have column numbers in E9:P9. (5-16)
(A1 references the column number, not the month name [this makes it work
with a vlookup])

On sheet Budget, you have names in A5:An
I have names in A11:Awhatever
FYI: I have named A11:AJ203 as a range: "BudgData" (this incorporates
the
extra totalling columns)

On sheet YTD, you have some name in A10
On sheet YTD I have names in column A, rows 10 through whatever (YTD Names
match the names on the budget sheet exactly. Budget sheet is sorted
alpha.)

You want the YTD sum for the name in A10:
Yes (sum the budget YTD on sheet YTD for the name in A10 and down)

=SUM(INDEX(Budget!E5:P10,MATCH(A1,Budget!A5:A10 ,0),))

I'd like to understand the formula:
(Budget!E5:P10 - the cell refs confuse me, why are we going E5 to P10?
Wouldn't it be E5 to P5? (based on where you thought my column names
were?
- otherwise it would be E10:P10 for where they actually are? And since I'm
actually matching column numbers not month names it would be E9:P9?)

MATCH(A1,Budget!A5:A10,0),))
Ok, we're matching the manual entry I have in A1 for the month we are in,
but what is the remainder of the formula doing? Why are we referencing
Budget!A5:A10?


I love learning this stuff and reallly appreciate the help immensely.

Karin


"T. Valko" wrote:

Typo correction:

You want the YTD sum for the name in A10:
=SUM(INDEX(Budget!E5:P10,MATCH(A1,Budget!A5:A10,0) ,))

Should be:

=SUM(INDEX(Budget!E5:P10,MATCH(A10,Budget!A5:A10,0 ),))


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Ok...

You don't need those extra columns.

On your Budget sheet is there data for all months or just for the
months
that have passed starting from May (the start of *your* year)? In other
words, so far, you only have data for May, June and July?

On sheet Budget, you have column headers in E4:P4 - May, June, July,
August ... April

On sheet Budget, you have names in A5:An

On sheet YTD, you have some name in A10

You want the YTD sum for the name in A10:

=SUM(INDEX(Budget!E5:P10,MATCH(A1,Budget!A5:A10,0) ,))


--
Biff
Microsoft Excel MVP


"Karin" wrote in message
...
In a separate question I looked for the the formula I received below
(and
it
works great). here is that question:
I have columns 5-16 (E:P) filled with monthly data.
Column 5 (E) contains May, column 6 (F) June, etc.
In Cell A1 I have the current month (7 for July, 8 for Aug, etc.).

I want to sum cells E5 through ?5 based on the number in cell A1.
For July the sum would be E5:G5, for August it would be E5:H5, etc.)
What fomula would I use to create the sum based on the data in cell
A1?

But I can't seem to get to what I really want which is a lookup that
returns
the offset formula.

Full story: I have a sheet (YTD) that totals year to date budget
numbers
by
employee. I have another sheet (Budget) that has the budget numbers
for
each
month by employee. Then I have an additional column for each month
that
totals year to date (our year is May-April), so I have a May-Jun
column,
May-Jul, May-Aug, May-Sep.

In A1 of the Budget sheet I manually place the column number of the
current
month. (July happens to be 7). In Cell A2 I place the column number
of
the
YTD column that I want for the month (May-Jul is column 18).

This lookup gives me the total ytd:
VLOOKUP($A10,Budget,Budget!$A$2,FALSE)
(It looks up the employee in A10, goes to the Budget range, and
returns
the
column number from A2).

I was trying to avoid having a separate column for each YTD total and
the
OFFSET accomplished that nicely, but I can't seem to make it work with
the
lookup.



"T. Valko" wrote:

=SUM(OFFSET(E13,0,0,1,A1-4))

What's in A1?


--
Biff
Microsoft Excel MVP


"Karin" wrote in message
...
Oops - I wrote the Offset wrong, s/b =SUM(OFFSET(E13,0,0,1,A1-4))

I can't seem to get match or index to work.

"Karin" wrote:

In another post I got an Offset answer, but what I ultimately want
to
do
and
can't seem to get to is:

On Sheet YTD, look up Cell A10 (Smith) in the range "Budget",
(Smith
is
found in cell A13 in the range), then calculate this offset for
the
Row
A13
in the range "Budget"

=SUM(OFFSET(A13,0,0,1,A1-4))

And change the row number based on the look up.
TIA!

AWESOME! Thank you, thank you, thank you!!! I get it , I got it, it works!
I'm soooo excited.
Thank you so much for taking the time to help me learn. I really appreciate
it.
Warm regards,
Karin

"T. Valko" wrote:

All the range references used in my suggested formula are made up. Since you
didn't provide those details before I can only guess where your data might
be! As far as I knew, rhe data was somewhere in column E through P.

Let's try a different approach to this.

I put together a small sample file that demonstrates this. I put everything
on a single sheet so you can see it without having to jump between different
sheets.

The name in A3 would be the names on your YTD sheet. The formula returns the
YTD sum based on May being the start of your year. Since this is July the
sum is for May - July for the selected name. Notice that I'm using the short
month names. If you're using the long month names all you need to do is
change this portion of the formula:

TEXT(NOW(),"mmm")

To:

TEXT(NOW(),"mmmm")

Sample file:

http://cjoint.com/?hzw25cbwK7

--
Biff
Microsoft Excel MVP


"Karin" wrote in message
...
Hi, and thank you very much for helping. In response to your questions:

On your Budget sheet is there data for all months or just for the
months that have passed starting from May (the start of *your* year)?
In other words, so far, you only have data for May, June and July?
I have all the data for the whole year (May-Apr), columns E:P are filled
in

On sheet Budget, you have column headers in E4:P4 - May, June, July,
August ... April
I have column names May-April in cells E10:P10 (on Budget sheet)
I have column numbers in E9:P9. (5-16)
(A1 references the column number, not the month name [this makes it work
with a vlookup])

On sheet Budget, you have names in A5:An
I have names in A11:Awhatever
FYI: I have named A11:AJ203 as a range: "BudgData" (this incorporates
the
extra totalling columns)

On sheet YTD, you have some name in A10
On sheet YTD I have names in column A, rows 10 through whatever (YTD Names
match the names on the budget sheet exactly. Budget sheet is sorted
alpha.)

You want the YTD sum for the name in A10:
Yes (sum the budget YTD on sheet YTD for the name in A10 and down)

=SUM(INDEX(Budget!E5:P10,MATCH(A1,Budget!A5:A10 ,0),))

I'd like to understand the formula:
(Budget!E5:P10 - the cell refs confuse me, why are we going E5 to P10?
Wouldn't it be E5 to P5? (based on where you thought my column names
were?
- otherwise it would be E10:P10 for where they actually are? And since I'm
actually matching column numbers not month names it would be E9:P9?)

MATCH(A1,Budget!A5:A10,0),))
Ok, we're matching the manual entry I have in A1 for the month we are in,
but what is the remainder of the formula doing? Why are we referencing
Budget!A5:A10?


I love learning this stuff and reallly appreciate the help immensely.

Karin


"T. Valko" wrote:

Typo correction:

You want the YTD sum for the name in A10:
=SUM(INDEX(Budget!E5:P10,MATCH(A1,Budget!A5:A10,0) ,))

Should be:

=SUM(INDEX(Budget!E5:P10,MATCH(A10,Budget!A5:A10,0 ),))


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Ok...

You don't need those extra columns.

On your Budget sheet is there data for all months or just for the
months
that have passed starting from May (the start of *your* year)? In other
words, so far, you only have data for May, June and July?

On sheet Budget, you have column headers in E4:P4 - May, June, July,
August ... April

On sheet Budget, you have names in A5:An

On sheet YTD, you have some name in A10

You want the YTD sum for the name in A10:

=SUM(INDEX(Budget!E5:P10,MATCH(A1,Budget!A5:A10,0) ,))


--
Biff
Microsoft Excel MVP


"Karin" wrote in message
...
In a separate question I looked for the the formula I received below
(and
it
works great). here is that question:
I have columns 5-16 (E:P) filled with monthly data.
Column 5 (E) contains May, column 6 (F) June, etc.
In Cell A1 I have the current month (7 for July, 8 for Aug, etc.).

I want to sum cells E5 through ?5 based on the number in cell A1.
For July the sum would be E5:G5, for August it would be E5:H5, etc.)
What fomula would I use to create the sum based on the data in cell
A1?

But I can't seem to get to what I really want which is a lookup that
returns
the offset formula.

Full story: I have a sheet (YTD) that totals year to date budget
numbers
by
employee. I have another sheet (Budget) that has the budget numbers
for
each
month by employee. Then I have an additional column for each month
that
totals year to date (our year is May-April), so I have a May-Jun
column,
May-Jul, May-Aug, May-Sep.

In A1 of the Budget sheet I manually place the column number of the
current
month. (July happens to be 7). In Cell A2 I place the column number
of
the
YTD column that I want for the month (May-Jul is column 18).

This lookup gives me the total ytd:
VLOOKUP($A10,Budget,Budget!$A$2,FALSE)
(It looks up the employee in A10, goes to the Budget range, and
returns
the
column number from A2).

I was trying to avoid having a separate column for each YTD total and
the
OFFSET accomplished that nicely, but I can't seem to make it work with
the
lookup.



"T. Valko" wrote:

=SUM(OFFSET(E13,0,0,1,A1-4))

What's in A1?


--
Biff
Microsoft Excel MVP


"Karin" wrote in message
...
Oops - I wrote the Offset wrong, s/b =SUM(OFFSET(E13,0,0,1,A1-4))

I can't seem to get match or index to work.

"Karin" wrote:

In another post I got an Offset answer, but what I ultimately want
to
do
and
can't seem to get to is:

On Sheet YTD, look up Cell A10 (Smith) in the range "Budget",
(Smith
is
found in cell A13 in the range), then calculate this offset for
the
Row
A13
in the range "Budget"

=SUM(OFFSET(A13,0,0,1,A1-4))

And change the row number based on the look up.
TIA!

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Karin" wrote in message
...
AWESOME! Thank you, thank you, thank you!!! I get it , I got it, it
works!
I'm soooo excited.
Thank you so much for taking the time to help me learn. I really
appreciate
it.
Warm regards,
Karin

"T. Valko" wrote:

All the range references used in my suggested formula are made up. Since
you
didn't provide those details before I can only guess where your data
might
be! As far as I knew, rhe data was somewhere in column E through P.

Let's try a different approach to this.

I put together a small sample file that demonstrates this. I put
everything
on a single sheet so you can see it without having to jump between
different
sheets.

The name in A3 would be the names on your YTD sheet. The formula returns
the
YTD sum based on May being the start of your year. Since this is July the
sum is for May - July for the selected name. Notice that I'm using the
short
month names. If you're using the long month names all you need to do is
change this portion of the formula:

TEXT(NOW(),"mmm")

To:

TEXT(NOW(),"mmmm")

Sample file:

http://cjoint.com/?hzw25cbwK7

--
Biff
Microsoft Excel MVP


"Karin" wrote in message
...
Hi, and thank you very much for helping. In response to your
questions:

On your Budget sheet is there data for all months or just for the
months that have passed starting from May (the start of *your*
year)?
In other words, so far, you only have data for May, June and July?
I have all the data for the whole year (May-Apr), columns E:P are
filled
in

On sheet Budget, you have column headers in E4:P4 - May, June, July,
August ... April
I have column names May-April in cells E10:P10 (on Budget sheet)
I have column numbers in E9:P9. (5-16)
(A1 references the column number, not the month name [this makes it
work
with a vlookup])

On sheet Budget, you have names in A5:An
I have names in A11:Awhatever
FYI: I have named A11:AJ203 as a range: "BudgData" (this incorporates
the
extra totalling columns)

On sheet YTD, you have some name in A10
On sheet YTD I have names in column A, rows 10 through whatever (YTD
Names
match the names on the budget sheet exactly. Budget sheet is sorted
alpha.)

You want the YTD sum for the name in A10:
Yes (sum the budget YTD on sheet YTD for the name in A10 and down)

=SUM(INDEX(Budget!E5:P10,MATCH(A1,Budget!A5:A10 ,0),))

I'd like to understand the formula:
(Budget!E5:P10 - the cell refs confuse me, why are we going E5 to
P10?
Wouldn't it be E5 to P5? (based on where you thought my column names
were?
- otherwise it would be E10:P10 for where they actually are? And since
I'm
actually matching column numbers not month names it would be E9:P9?)

MATCH(A1,Budget!A5:A10,0),))
Ok, we're matching the manual entry I have in A1 for the month we are
in,
but what is the remainder of the formula doing? Why are we referencing
Budget!A5:A10?


I love learning this stuff and reallly appreciate the help immensely.

Karin


"T. Valko" wrote:

Typo correction:

You want the YTD sum for the name in A10:
=SUM(INDEX(Budget!E5:P10,MATCH(A1,Budget!A5:A10,0) ,))

Should be:

=SUM(INDEX(Budget!E5:P10,MATCH(A10,Budget!A5:A10,0 ),))


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Ok...

You don't need those extra columns.

On your Budget sheet is there data for all months or just for the
months
that have passed starting from May (the start of *your* year)? In
other
words, so far, you only have data for May, June and July?

On sheet Budget, you have column headers in E4:P4 - May, June, July,
August ... April

On sheet Budget, you have names in A5:An

On sheet YTD, you have some name in A10

You want the YTD sum for the name in A10:

=SUM(INDEX(Budget!E5:P10,MATCH(A1,Budget!A5:A10,0) ,))


--
Biff
Microsoft Excel MVP


"Karin" wrote in message
...
In a separate question I looked for the the formula I received
below
(and
it
works great). here is that question:
I have columns 5-16 (E:P) filled with monthly data.
Column 5 (E) contains May, column 6 (F) June, etc.
In Cell A1 I have the current month (7 for July, 8 for Aug, etc.).

I want to sum cells E5 through ?5 based on the number in cell A1.
For July the sum would be E5:G5, for August it would be E5:H5,
etc.)
What fomula would I use to create the sum based on the data in
cell
A1?

But I can't seem to get to what I really want which is a lookup
that
returns
the offset formula.

Full story: I have a sheet (YTD) that totals year to date budget
numbers
by
employee. I have another sheet (Budget) that has the budget
numbers
for
each
month by employee. Then I have an additional column for each month
that
totals year to date (our year is May-April), so I have a May-Jun
column,
May-Jul, May-Aug, May-Sep.

In A1 of the Budget sheet I manually place the column number of the
current
month. (July happens to be 7). In Cell A2 I place the column
number
of
the
YTD column that I want for the month (May-Jul is column 18).

This lookup gives me the total ytd:
VLOOKUP($A10,Budget,Budget!$A$2,FALSE)
(It looks up the employee in A10, goes to the Budget range, and
returns
the
column number from A2).

I was trying to avoid having a separate column for each YTD total
and
the
OFFSET accomplished that nicely, but I can't seem to make it work
with
the
lookup.



"T. Valko" wrote:

=SUM(OFFSET(E13,0,0,1,A1-4))

What's in A1?


--
Biff
Microsoft Excel MVP


"Karin" wrote in message
...
Oops - I wrote the Offset wrong, s/b
=SUM(OFFSET(E13,0,0,1,A1-4))

I can't seem to get match or index to work.

"Karin" wrote:

In another post I got an Offset answer, but what I ultimately
want
to
do
and
can't seem to get to is:

On Sheet YTD, look up Cell A10 (Smith) in the range "Budget",
(Smith
is
found in cell A13 in the range), then calculate this offset for
the
Row
A13
in the range "Budget"

=SUM(OFFSET(A13,0,0,1,A1-4))

And change the row number based on the look up.
TIA!