Formula request

mac_see

Active Member
Joined
Oct 15, 2002
Messages
419
Let me put the scenerio across
The value of A1 is 1
The value of B1 is 2
The value of C1 is 3
The value of D1 is 4
The value of E1 is 5

The value of A2 is 100
The value of B2 is 200
The value of C2 is 300
The value of D2 is 400
The value of E2 is 500

The value of G1 is Row1
The value of G2 is Row2

H1 and H2 accepts value from the user.

If i have to put the value for
H1 to 2 and H2 to 4, the H3 cell should sum up the corresponding value from 2 to 4(B1:D1) that is 200+300+400=800
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
I believe the result would be 900...
Book1
ABCDEFGHIJKL
1123452
21002003004005004
3900
4
5
6
Sheet5
 

mac_see

Active Member
Joined
Oct 15, 2002
Messages
419
That was a terrific reply within a short span of time. One more question, if we go to sum it up reverse (for example the value of G1 is more than the value of G2) an error box should appear instead of #REF!.

Thanx
Maxi
 

mac_see

Active Member
Joined
Oct 15, 2002
Messages
419

ADVERTISEMENT

Aladin Akyurek, don't wanna disappoint u but the one provided by the master looks good. Still I appreciate your efforts.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
On 2002-10-16 12:27, mac_see wrote:
That was a terrific reply within a short span of time. One more question, if we go to sum it up reverse (for example the value of G1 is more than the value of G2) an error box should appear instead of #REF!.

Thanx
Maxi

The INDEX version would still compute a sum, as it were, in reverse order. An "error box" would require VBA...
 

mac_see

Active Member
Joined
Oct 15, 2002
Messages
419

ADVERTISEMENT

#REF! appears if I put 5 in G1 and 4 in G2. Another strange thing is, If I keep a blank value in any of the cells (G1 or G2), it should bring up a error box (By error box, I mean, the text that we type in the Validation option under Data menu)
 

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
>#REF! appears if I put 5 in G1 and 4 in G2.

Of course, semantically G1 should be the 1st column -- not the 2nd. You're original post didn't indicate otherwise.

>Another strange thing is, If I keep a blank
>value in any of the cells (G1 or G2), it
>should bring up a error box (By error box,
>I mean, the text that we type in the
>Validation option under Data menu)

Change your Validation so it doesn't permit blanks by unchecking the "Ignore blank" option.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
On 2002-10-16 12:47, mac_see wrote:
#REF! appears if I put 5 in G1 and 4 in G2. Another strange thing is, If I keep a blank value in any of the cells (G1 or G2), it should bring up a error box (By error box, I mean, the text that we type in the Validation option under Data menu)

Although data validation can be defeated, you could try...

For H1...

Set Allow to: Whole Number

Choose Between

Set Minimum to: 1

Set Maximum to: =COLUMN(E1)

For H2...

Set Allow to: Custom

Set Formula to: =(H2>=H1)*(H2-INT(H2)=0)*(H2>0)*(H2<=COLUMN(E1))

I already suggested what to use in H3... :biggrin:
This message was edited by aladin akyurek on 2002-10-17 02:20
 

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
If you want to enter values in G1:G2 in any order consider using...
Book1
ABCDEFGHIJKL
1123455
21002003004005004
3900
4
5
6
Sheet5
 

Forum statistics

Threads
1,144,117
Messages
5,722,581
Members
422,447
Latest member
knopp

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top