Update to : Pulling my hair out, need some help building formula
 

After OzziJC reply, I went back to the IF function.

Using the following does return the correct value for the first calculation.
=IF(L21=1,SUM(Bronze!B1),IF(L21=2,SUM(Bronze!B1:B2 ),IF(L21=3,SUM(Bronze!B1:B3),IF(L21=4,SUM(Bronze!B 1:B4)))))

It will not however work in the second to sixth calculation, do to the
number of "allowed' calculations - the number of values a
The second range of numbers 5 - 20
The third range of number 21 - 84
The fourth range of number 85 - 340
The fifth range of numbers 341 - 1364
The sixth range of numbers 1365 - 5460

I've attempted to write a nested formula and an array formula - neither
which have worked - yet!

Once again any help/guidance would be greatfully appreciated.

John

Your formula could be replaced by

=SUM(OFFSET(Bronze!B1,,,L21,))

I have no idea what you're talking about after that, since you didn't
keep your reply in the same thread...

In article ,
"Jackanorry" wrote:

After OzziJC reply, I went back to the IF function.

Using the following does return the correct value for the first calculation.
=IF(L21=1,SUM(Bronze!B1),IF(L21=2,SUM(Bronze!B1:B2 ),IF(L21=3,SUM(Bronze!B1:B3)
,IF(L21=4,SUM(Bronze!B1:B4)))))

It will not however work in the second to sixth calculation, do to the
number of "allowed' calculations - the number of values a
The second range of numbers 5 - 20
The third range of number 21 - 84
The fourth range of number 85 - 340
The fifth range of numbers 341 - 1364
The sixth range of numbers 1365 - 5460

I've attempted to write a nested formula and an array formula - neither
which have worked - yet!

Once again any help/guidance would be greatfully appreciated.

your formula works fine for me.I dont quite follow what your problem is?Are
your actual ranges up to 3500 rows ???
--
paul
remove nospam for email addy!



"Jackanorry" wrote:

After OzziJC reply, I went back to the IF function.

Using the following does return the correct value for the first calculation.
=IF(L21=1,SUM(Bronze!B1),IF(L21=2,SUM(Bronze!B1:B2 ),IF(L21=3,SUM(Bronze!B1:B3),IF(L21=4,SUM(Bronze!B 1:B4)))))

It will not however work in the second to sixth calculation, do to the
number of "allowed' calculations - the number of values a
The second range of numbers 5 - 20
The third range of number 21 - 84
The fourth range of number 85 - 340
The fifth range of numbers 341 - 1364
The sixth range of numbers 1365 - 5460

I've attempted to write a nested formula and an array formula - neither
which have worked - yet!

Once again any help/guidance would be greatfully appreciated.

John

ahhh i see i replicated your ranges and after the third or so the "if" value
was 0.I think you make a little table with your values
A B
1 =bronze!B1
2 =sum(bronze!B1:B5)
3 =sum(bronze!B21:B84)
4 =sum(bronze!B85:B340)
5 =sum(bronze!B341:B1364)
6 =sum(bronze!B1365:B5460)

and use =Vlookup(L21,bronze!A1:B6,2,false)

--
paul
remove nospam for email addy!



"Jackanorry" wrote:

After OzziJC reply, I went back to the IF function.

Using the following does return the correct value for the first calculation.
=IF(L21=1,SUM(Bronze!B1),IF(L21=2,SUM(Bronze!B1:B2 ),IF(L21=3,SUM(Bronze!B1:B3),IF(L21=4,SUM(Bronze!B 1:B4)))))

It will not however work in the second to sixth calculation, do to the
number of "allowed' calculations - the number of values a
The second range of numbers 5 - 20
The third range of number 21 - 84
The fourth range of number 85 - 340
The fifth range of numbers 341 - 1364
The sixth range of numbers 1365 - 5460

I've attempted to write a nested formula and an array formula - neither
which have worked - yet!

Once again any help/guidance would be greatfully appreciated.

John

JE McGimpsey,
Many thanks for your post/reply. Your suggestion helped, it works now as it
should.
Also my apologies, i should have replied through original post (for various
reasons I couldn't seem to find it and frustration won out)

The original thread should be found he
http://msdn.microsoft.com/newsgroups...sloc=en-us&p=1

Thanks again,
John

"JE McGimpsey" wrote:

Your formula could be replaced by

=SUM(OFFSET(Bronze!B1,,,L21,))

I have no idea what you're talking about after that, since you didn't
keep your reply in the same thread...

In article ,
"Jackanorry" wrote:

After OzziJC reply, I went back to the IF function.

Using the following does return the correct value for the first calculation.
=IF(L21=1,SUM(Bronze!B1),IF(L21=2,SUM(Bronze!B1:B2 ),IF(L21=3,SUM(Bronze!B1:B3)
,IF(L21=4,SUM(Bronze!B1:B4)))))

It will not however work in the second to sixth calculation, do to the
number of "allowed' calculations - the number of values a
The second range of numbers 5 - 20
The third range of number 21 - 84
The fourth range of number 85 - 340
The fifth range of numbers 341 - 1364
The sixth range of numbers 1365 - 5460

I've attempted to write a nested formula and an array formula - neither
which have worked - yet!

Once again any help/guidance would be greatfully appreciated.

Paul,

Thanks for your reply and assistance.

The data is being called from three other sheets in the workbook and extends
down 5460 cells.
The suggestion from JE McGimpsey did the trick and the correct values are
returned based on input (a number)

I'm now attempting to take it a bit further and adding functionality to the
spreadsheet.

I've added text (data) to the three sheets and I'd like to have the text be
returned on the primary sheet based on the original value input by user.
EX. If number input in 3rd field is 55 and that corresponds to "earned
grocery benefit", then I would like to have "earned grocery benefit" returned
on user input sheet.

Thanks again for your help,
John

"paul" wrote:

ahhh i see i replicated your ranges and after the third or so the "if" value
was 0.I think you make a little table with your values
A B
1 =bronze!B1
2 =sum(bronze!B1:B5)
3 =sum(bronze!B21:B84)
4 =sum(bronze!B85:B340)
5 =sum(bronze!B341:B1364)
6 =sum(bronze!B1365:B5460)

and use =Vlookup(L21,bronze!A1:B6,2,false)

--
paul
remove nospam for email addy!



"Jackanorry" wrote:

After OzziJC reply, I went back to the IF function.

Using the following does return the correct value for the first calculation.
=IF(L21=1,SUM(Bronze!B1),IF(L21=2,SUM(Bronze!B1:B2 ),IF(L21=3,SUM(Bronze!B1:B3),IF(L21=4,SUM(Bronze!B 1:B4)))))

It will not however work in the second to sixth calculation, do to the
number of "allowed' calculations - the number of values a
The second range of numbers 5 - 20
The third range of number 21 - 84
The fourth range of number 85 - 340
The fifth range of numbers 341 - 1364
The sixth range of numbers 1365 - 5460

I've attempted to write a nested formula and an array formula - neither
which have worked - yet!

Once again any help/guidance would be greatfully appreciated.

John