Electricity Bill Sheet

Hi,
I posted a similar question 2 days back but seems like i was not clear 
enough with the question. The following table contains UNITS of electricity 
and corresponding rates.

UNITS		RATES
50			2.5
100			3.6
150			4.5
300			5.4

This table shows that for first 50 units consumed, rate will be 2.5 PER UNIT.
For next 100 units, rate will be 3.6 per unit.  Not 50 to 100, BUT rest 100 
above 50. That would total to 150. After that for next 150 units 4.5 per 
unit. This totals up TOTAL UNITS to 300. For units above 300, rate will be 
5.4 per unit.
If i enter 425 units in H1, i should get the following in column C as under. 

UNITS		RATES	TOTAL UNITS
50			2.5		50
100			3.6		100
150			4.5		150
300			5.4		125

If i enter 260 units in H1, i should get the following in column C as under. 

UNITS		RATES	TOTAL UNITS
50			2.5		50
100			3.6		100
150			4.5		110
300			5.4		0

If i enter 55 units in H1, i should get the following in column C as under. 

UNITS		RATES	TOTAL UNITS
50			2.5		50
100			3.6		5
150			4.5		0
300			5.4		0


If i enter 35 units in H1, i should get the following in column C as under. 

UNITS		RATES	TOTAL UNITS
50			2.5		35
100			3.6		0
150			4.5		0
300			5.4		0




2
Utf
3/19/2010 4:05:01 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

6 Replies
1861 Views

Similar Articles

[PageSpeed] 32

Apply this formula in cell C2 and copy down as required

=MIN(A2,$H$1-SUM($C$1:C1))

-- 
Jacob


"XKruodo" wrote:

> Hi,
> I posted a similar question 2 days back but seems like i was not clear 
> enough with the question. The following table contains UNITS of electricity 
> and corresponding rates.
> 
> UNITS		RATES
> 50			2.5
> 100			3.6
> 150			4.5
> 300			5.4
> 
> This table shows that for first 50 units consumed, rate will be 2.5 PER UNIT.
> For next 100 units, rate will be 3.6 per unit.  Not 50 to 100, BUT rest 100 
> above 50. That would total to 150. After that for next 150 units 4.5 per 
> unit. This totals up TOTAL UNITS to 300. For units above 300, rate will be 
> 5.4 per unit.
> If i enter 425 units in H1, i should get the following in column C as under. 
> 
> UNITS		RATES	TOTAL UNITS
> 50			2.5		50
> 100			3.6		100
> 150			4.5		150
> 300			5.4		125
> 
> If i enter 260 units in H1, i should get the following in column C as under. 
> 
> UNITS		RATES	TOTAL UNITS
> 50			2.5		50
> 100			3.6		100
> 150			4.5		110
> 300			5.4		0
> 
> If i enter 55 units in H1, i should get the following in column C as under. 
> 
> UNITS		RATES	TOTAL UNITS
> 50			2.5		50
> 100			3.6		5
> 150			4.5		0
> 300			5.4		0
> 
> 
> If i enter 35 units in H1, i should get the following in column C as under. 
> 
> UNITS		RATES	TOTAL UNITS
> 50			2.5		35
> 100			3.6		0
> 150			4.5		0
> 300			5.4		0
> 
> 
> 
> 
0
Utf
3/19/2010 4:56:01 AM
Your question still isn't very clear, mostly, in this case, because you 
didn't ask one. You're not making it easy for people to help you when you 
make them guess what you are looking for.

Assuming you want the formula which creates the results in column C, and 
assuming your data in this column starts in row 2, try this in C2:

=IF(SUM(A$2:A2)<$H$1,A2,MAX(0,$H$1-SUM(A$1:A1)))
and copy down

PS. What result do you want if you put 1000 in H1?

Regards,
Fred

"XKruodo" <XKruodo@discussions.microsoft.com> wrote in message 
news:6F9CE26D-02BF-4DE1-BE28-A305BE3E44CC@microsoft.com...
> Hi,
> I posted a similar question 2 days back but seems like i was not clear
> enough with the question. The following table contains UNITS of 
> electricity
> and corresponding rates.
>
> UNITS RATES
> 50 2.5
> 100 3.6
> 150 4.5
> 300 5.4
>
> This table shows that for first 50 units consumed, rate will be 2.5 PER 
> UNIT.
> For next 100 units, rate will be 3.6 per unit.  Not 50 to 100, BUT rest 
> 100
> above 50. That would total to 150. After that for next 150 units 4.5 per
> unit. This totals up TOTAL UNITS to 300. For units above 300, rate will be
> 5.4 per unit.
> If i enter 425 units in H1, i should get the following in column C as 
> under.
>
> UNITS RATES TOTAL UNITS
> 50 2.5 50
> 100 3.6 100
> 150 4.5 150
> 300 5.4 125
>
> If i enter 260 units in H1, i should get the following in column C as 
> under.
>
> UNITS RATES TOTAL UNITS
> 50 2.5 50
> 100 3.6 100
> 150 4.5 110
> 300 5.4 0
>
> If i enter 55 units in H1, i should get the following in column C as 
> under.
>
> UNITS RATES TOTAL UNITS
> 50 2.5 50
> 100 3.6 5
> 150 4.5 0
> 300 5.4 0
>
>
> If i enter 35 units in H1, i should get the following in column C as 
> under.
>
> UNITS RATES TOTAL UNITS
> 50 2.5 35
> 100 3.6 0
> 150 4.5 0
> 300 5.4 0
>
>
>
> 

0
Fred
3/19/2010 5:04:09 AM
Thanks Jacob, your formula worked.
Fred, if i enter 1000 in H1 i wanted this,

> > If i enter 1000 units in H1, i should get the following in column C as 
> > under.
> >
> > UNITS RATES TOTAL UNITS
> > 50 2.5         50
> > 100 3.6       100
> > 150 4.5       150
> > 300 5.4        700

"Fred Smith" wrote:

> Your question still isn't very clear, mostly, in this case, because you 
> didn't ask one. You're not making it easy for people to help you when you 
> make them guess what you are looking for.
> 
> Assuming you want the formula which creates the results in column C, and 
> assuming your data in this column starts in row 2, try this in C2:
> 
> =IF(SUM(A$2:A2)<$H$1,A2,MAX(0,$H$1-SUM(A$1:A1)))
> and copy down
> 
> PS. What result do you want if you put 1000 in H1?
> 
> Regards,
> Fred
> 
> "XKruodo" <XKruodo@discussions.microsoft.com> wrote in message 
> news:6F9CE26D-02BF-4DE1-BE28-A305BE3E44CC@microsoft.com...
> > Hi,
> > I posted a similar question 2 days back but seems like i was not clear
> > enough with the question. The following table contains UNITS of 
> > electricity
> > and corresponding rates.
> >
> > UNITS RATES
> > 50 2.5
> > 100 3.6
> > 150 4.5
> > 300 5.4
> >
> > This table shows that for first 50 units consumed, rate will be 2.5 PER 
> > UNIT.
> > For next 100 units, rate will be 3.6 per unit.  Not 50 to 100, BUT rest 
> > 100
> > above 50. That would total to 150. After that for next 150 units 4.5 per
> > unit. This totals up TOTAL UNITS to 300. For units above 300, rate will be
> > 5.4 per unit.
> > If i enter 425 units in H1, i should get the following in column C as 
> > under.
> >
> > UNITS RATES TOTAL UNITS
> > 50 2.5 50
> > 100 3.6 100
> > 150 4.5 150
> > 300 5.4 125
> >
> > If i enter 260 units in H1, i should get the following in column C as 
> > under.
> >
> > UNITS RATES TOTAL UNITS
> > 50 2.5 50
> > 100 3.6 100
> > 150 4.5 110
> > 300 5.4 0
> >
> > If i enter 55 units in H1, i should get the following in column C as 
> > under.
> >
> > UNITS RATES TOTAL UNITS
> > 50 2.5 50
> > 100 3.6 5
> > 150 4.5 0
> > 300 5.4 0
> >
> >
> > If i enter 35 units in H1, i should get the following in column C as 
> > under.
> >
> > UNITS RATES TOTAL UNITS
> > 50 2.5 35
> > 100 3.6 0
> > 150 4.5 0
> > 300 5.4 0
> >
> >
> >
> > 
> 
> .
> 
0
Utf
3/19/2010 6:03:01 AM
Opps! I dint check for 1000 and it doesn't work either :))

It works fine till 600. Above 600 units, C5 doesn't change. It shows 300..

"XKruodo" wrote:

> Thanks Jacob, your formula worked.
> Fred, if i enter 1000 in H1 i wanted this,
> 
> > > If i enter 1000 units in H1, i should get the following in column C as 
> > > under.
> > >
> > > UNITS RATES TOTAL UNITS
> > > 50 2.5         50
> > > 100 3.6       100
> > > 150 4.5       150
> > > 300 5.4        700
> 
> "Fred Smith" wrote:
> 
> > Your question still isn't very clear, mostly, in this case, because you 
> > didn't ask one. You're not making it easy for people to help you when you 
> > make them guess what you are looking for.
> > 
> > Assuming you want the formula which creates the results in column C, and 
> > assuming your data in this column starts in row 2, try this in C2:
> > 
> > =IF(SUM(A$2:A2)<$H$1,A2,MAX(0,$H$1-SUM(A$1:A1)))
> > and copy down
> > 
> > PS. What result do you want if you put 1000 in H1?
> > 
> > Regards,
> > Fred
> > 
> > "XKruodo" <XKruodo@discussions.microsoft.com> wrote in message 
> > news:6F9CE26D-02BF-4DE1-BE28-A305BE3E44CC@microsoft.com...
> > > Hi,
> > > I posted a similar question 2 days back but seems like i was not clear
> > > enough with the question. The following table contains UNITS of 
> > > electricity
> > > and corresponding rates.
> > >
> > > UNITS RATES
> > > 50 2.5
> > > 100 3.6
> > > 150 4.5
> > > 300 5.4
> > >
> > > This table shows that for first 50 units consumed, rate will be 2.5 PER 
> > > UNIT.
> > > For next 100 units, rate will be 3.6 per unit.  Not 50 to 100, BUT rest 
> > > 100
> > > above 50. That would total to 150. After that for next 150 units 4.5 per
> > > unit. This totals up TOTAL UNITS to 300. For units above 300, rate will be
> > > 5.4 per unit.
> > > If i enter 425 units in H1, i should get the following in column C as 
> > > under.
> > >
> > > UNITS RATES TOTAL UNITS
> > > 50 2.5 50
> > > 100 3.6 100
> > > 150 4.5 150
> > > 300 5.4 125
> > >
> > > If i enter 260 units in H1, i should get the following in column C as 
> > > under.
> > >
> > > UNITS RATES TOTAL UNITS
> > > 50 2.5 50
> > > 100 3.6 100
> > > 150 4.5 110
> > > 300 5.4 0
> > >
> > > If i enter 55 units in H1, i should get the following in column C as 
> > > under.
> > >
> > > UNITS RATES TOTAL UNITS
> > > 50 2.5 50
> > > 100 3.6 5
> > > 150 4.5 0
> > > 300 5.4 0
> > >
> > >
> > > If i enter 35 units in H1, i should get the following in column C as 
> > > under.
> > >
> > > UNITS RATES TOTAL UNITS
> > > 50 2.5 35
> > > 100 3.6 0
> > > 150 4.5 0
> > > 300 5.4 0
> > >
> > >
> > >
> > > 
> > 
> > .
> > 
0
Utf
3/19/2010 6:06:01 AM
The formula which I suggested will not retrive the balance amount in the last 
slot...Try th ebelow...

=IF(AND(A3="",$H$1>SUM($C$1:C1)),$H$1-SUM($C$1:C1),
MIN(A2,$H$1-SUM($C$1:C1)))

-- 
Jacob


"XKruodo" wrote:

> Thanks Jacob, your formula worked.
> Fred, if i enter 1000 in H1 i wanted this,
> 
> > > If i enter 1000 units in H1, i should get the following in column C as 
> > > under.
> > >
> > > UNITS RATES TOTAL UNITS
> > > 50 2.5         50
> > > 100 3.6       100
> > > 150 4.5       150
> > > 300 5.4        700
> 
> "Fred Smith" wrote:
> 
> > Your question still isn't very clear, mostly, in this case, because you 
> > didn't ask one. You're not making it easy for people to help you when you 
> > make them guess what you are looking for.
> > 
> > Assuming you want the formula which creates the results in column C, and 
> > assuming your data in this column starts in row 2, try this in C2:
> > 
> > =IF(SUM(A$2:A2)<$H$1,A2,MAX(0,$H$1-SUM(A$1:A1)))
> > and copy down
> > 
> > PS. What result do you want if you put 1000 in H1?
> > 
> > Regards,
> > Fred
> > 
> > "XKruodo" <XKruodo@discussions.microsoft.com> wrote in message 
> > news:6F9CE26D-02BF-4DE1-BE28-A305BE3E44CC@microsoft.com...
> > > Hi,
> > > I posted a similar question 2 days back but seems like i was not clear
> > > enough with the question. The following table contains UNITS of 
> > > electricity
> > > and corresponding rates.
> > >
> > > UNITS RATES
> > > 50 2.5
> > > 100 3.6
> > > 150 4.5
> > > 300 5.4
> > >
> > > This table shows that for first 50 units consumed, rate will be 2.5 PER 
> > > UNIT.
> > > For next 100 units, rate will be 3.6 per unit.  Not 50 to 100, BUT rest 
> > > 100
> > > above 50. That would total to 150. After that for next 150 units 4.5 per
> > > unit. This totals up TOTAL UNITS to 300. For units above 300, rate will be
> > > 5.4 per unit.
> > > If i enter 425 units in H1, i should get the following in column C as 
> > > under.
> > >
> > > UNITS RATES TOTAL UNITS
> > > 50 2.5 50
> > > 100 3.6 100
> > > 150 4.5 150
> > > 300 5.4 125
> > >
> > > If i enter 260 units in H1, i should get the following in column C as 
> > > under.
> > >
> > > UNITS RATES TOTAL UNITS
> > > 50 2.5 50
> > > 100 3.6 100
> > > 150 4.5 110
> > > 300 5.4 0
> > >
> > > If i enter 55 units in H1, i should get the following in column C as 
> > > under.
> > >
> > > UNITS RATES TOTAL UNITS
> > > 50 2.5 50
> > > 100 3.6 5
> > > 150 4.5 0
> > > 300 5.4 0
> > >
> > >
> > > If i enter 35 units in H1, i should get the following in column C as 
> > > under.
> > >
> > > UNITS RATES TOTAL UNITS
> > > 50 2.5 35
> > > 100 3.6 0
> > > 150 4.5 0
> > > 300 5.4 0
> > >
> > >
> > >
> > > 
> > 
> > .
> > 
0
Utf
3/19/2010 6:12:01 AM
Worked..
Thanks.

"Jacob Skaria" wrote:

> The formula which I suggested will not retrive the balance amount in the last 
> slot...Try th ebelow...
> 
> =IF(AND(A3="",$H$1>SUM($C$1:C1)),$H$1-SUM($C$1:C1),
> MIN(A2,$H$1-SUM($C$1:C1)))
> 
> -- 
> Jacob
> 
> 
> "XKruodo" wrote:
> 
> > Thanks Jacob, your formula worked.
> > Fred, if i enter 1000 in H1 i wanted this,
> > 
> > > > If i enter 1000 units in H1, i should get the following in column C as 
> > > > under.
> > > >
> > > > UNITS RATES TOTAL UNITS
> > > > 50 2.5         50
> > > > 100 3.6       100
> > > > 150 4.5       150
> > > > 300 5.4        700
> > 
> > "Fred Smith" wrote:
> > 
> > > Your question still isn't very clear, mostly, in this case, because you 
> > > didn't ask one. You're not making it easy for people to help you when you 
> > > make them guess what you are looking for.
> > > 
> > > Assuming you want the formula which creates the results in column C, and 
> > > assuming your data in this column starts in row 2, try this in C2:
> > > 
> > > =IF(SUM(A$2:A2)<$H$1,A2,MAX(0,$H$1-SUM(A$1:A1)))
> > > and copy down
> > > 
> > > PS. What result do you want if you put 1000 in H1?
> > > 
> > > Regards,
> > > Fred
> > > 
> > > "XKruodo" <XKruodo@discussions.microsoft.com> wrote in message 
> > > news:6F9CE26D-02BF-4DE1-BE28-A305BE3E44CC@microsoft.com...
> > > > Hi,
> > > > I posted a similar question 2 days back but seems like i was not clear
> > > > enough with the question. The following table contains UNITS of 
> > > > electricity
> > > > and corresponding rates.
> > > >
> > > > UNITS RATES
> > > > 50 2.5
> > > > 100 3.6
> > > > 150 4.5
> > > > 300 5.4
> > > >
> > > > This table shows that for first 50 units consumed, rate will be 2.5 PER 
> > > > UNIT.
> > > > For next 100 units, rate will be 3.6 per unit.  Not 50 to 100, BUT rest 
> > > > 100
> > > > above 50. That would total to 150. After that for next 150 units 4.5 per
> > > > unit. This totals up TOTAL UNITS to 300. For units above 300, rate will be
> > > > 5.4 per unit.
> > > > If i enter 425 units in H1, i should get the following in column C as 
> > > > under.
> > > >
> > > > UNITS RATES TOTAL UNITS
> > > > 50 2.5 50
> > > > 100 3.6 100
> > > > 150 4.5 150
> > > > 300 5.4 125
> > > >
> > > > If i enter 260 units in H1, i should get the following in column C as 
> > > > under.
> > > >
> > > > UNITS RATES TOTAL UNITS
> > > > 50 2.5 50
> > > > 100 3.6 100
> > > > 150 4.5 110
> > > > 300 5.4 0
> > > >
> > > > If i enter 55 units in H1, i should get the following in column C as 
> > > > under.
> > > >
> > > > UNITS RATES TOTAL UNITS
> > > > 50 2.5 50
> > > > 100 3.6 5
> > > > 150 4.5 0
> > > > 300 5.4 0
> > > >
> > > >
> > > > If i enter 35 units in H1, i should get the following in column C as 
> > > > under.
> > > >
> > > > UNITS RATES TOTAL UNITS
> > > > 50 2.5 35
> > > > 100 3.6 0
> > > > 150 4.5 0
> > > > 300 5.4 0
> > > >
> > > >
> > > >
> > > > 
> > > 
> > > .
> > > 
0
Utf
3/19/2010 6:22:01 AM
Reply:

Similar Artilces:

free autocad electrical autodesk full download crack torrent OLYGr@eiuG
We can crack or emulate any protection type: Dongle, Hardlock, Hasp, Serial, Password, Hasp4, Flexlm, Sentinel, Wibu, Eutron Smartkey, Hasphl, Proteq, All the Protections!! email = yshowsoft@gmail.com email = yshowsoft at gmail.com NEW UPDATES 2010 Artpro 9.5r4 with license (minimal of 2 license) Oris Press Matcher fiery impose Fiery Color Profiler 2 Preps 6.0r3 Dynagram Dynastrip v6.0.1 Dynagram inpO2 Artpro 9.0r8 (all modules included) Esko Visualizer 7.5.5 Esko Softwares Suite V7.5 PC/Mac Trueflow SE6.0.1 with SPM fully crack Heidelberg SignaStation v4.5.10.91...

Sheet #'s
Hi! My question is: Is there anyway to re-number sheet numbers, for example if you move your original sheets around and use the footer to print page of page’s they become out of sequence? Thank you in advance… Mark This is no "rename sheets in sequence" feature in Excel. You can of course right click the tabs of the sheets you want to rename and pick Rename. If there were a lot of sheets it might be worth writing a macro to do this, but if the renaming would take only a few minutes it would be quicker to do it manually.. -- Jim "mak" <mak@discussions.micro...

Wrong date in automatic upcoming bills
I have a problem with Money 2003. I have Upcoming Bills configured. When I add a new withdrawal, I click on Common Withdrawals and Upcoming Bills and choose one of them. In the menu-list the date is correct, but when I click it the date on the "withdrawal page" is old, maybe from when I first added the upcoming bill. I have to change it manually. And every month it's the same. Has anyone seen this? What to do? ...

Merging two sheets
This is what I have > > Sheet 1 has two columns > Col1 ; Col2 > Job; program ( combination of Job + program is unique ) > ABC4DX; ABC123 > ABC4DX; ABC475 > AXC123X; AXC123 > ABC135x; ABC124 > > > > > > Sheet 2 (This is a super set, has many rows , some are matching to th > sheet 1 and some not , the job + program will have duplicates) > > Col1; Col2; Col3; Col4; Col5; Col6 > Job; program; DBB; insert; update; lock > ABC4DX; ABC123; PVM; I; ; L > ABC4DX; ABC123; PTM; I; ; L ...

Time input in w/sheet #2
I had a problem which was resolved (or so I thought) yesterday regarding input of time. I was kindly given the solution Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, Range("A1:A10")) Is Nothing Then Exit Sub End If If Target.Cells.Count > 1 Then Exit Sub End If If Target.Value = "" Then Exit Sub End If Application.EnableEvents = False With Target If .HasFormula = False Then Select Case Len(.Value) Case 1 ' e.g., 1 = 00:01 AM ...

Matching bills to transactions
I am using Money 2007 Deluxe for a month, and i have a MAJOR problem. I schedule bills to record and so i downloaded the statement from my bank. It matched my bill (e.g. "bellsouth") and so i entered it, but it wont reconcile from my bill summary and it keep screaming i have bills past due. I need to match these transactions and tell Money it is already paid. HOW? HELPPPP!!!!!! Was the bill just scheduled but not in your checking register before you downloaded? If think it needs to be in the register first for it to be able to "match" on a download. Cindy "Bo...

Electrical diagram
Hi! I would like to know if anyone knows how to make electrical diagrams in Excel. (3 phases, voltages, currents and phase-angles). -- Greetings KristerA ...

Change sheet number in formula
Is there a way to do the following. I have a sheet with a formula that reference to another sheet. I need to pull a number from the same cell in every sheet. I want to do a summary of some sheets using the formula... =+'Sheet1'!A1 =+'Sheet2'!A1 Instead of hard coding the sheet number in the above formula, is there a way to reference a sheet number from another cell.?? ie Cell A1 = Sheet1" Cell A2 = "Sheet2" Then have another cell that pulls from A1 =+'CellA1'!A1 Very difficult to explain.... hope that made some sense... Thanks ** Post...

Schedule Bill and deposit
Hi all, I had add a schedule deposit on the 25 Oct. So now i will see the calander 25 Oct being hightlighted. The budget income will thus show the among that is being deposit. Now when i deleted the schedule deposit, the calander date is still being highlighted and the budget income is still having a value inside. If were to to creat a regular income , let said $1000, the budget income page will show $1000 plus the amount i had schedule before. Does any one know how can i delete the schedule amount away even thought it does not show any transaction in the "Schedule bill and depo...

Protect/unprotect all sheets at once?
Is there a way to protect or unprotect all sheets at once without going into each sheet? for each sh in sheets sh.unprotect next sh -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "wx4usa" <wx4usa@gmail.com> wrote in message news:2ebd153c-b438-4def-948d-0d32b6c5ae7c@k30g2000hse.googlegroups.com... > Is there a way to protect or unprotect all sheets at once without > going into each sheet? On Jul 21, 10:31 am, "Don Guillett" <dguille...@austin.rr.com> wrote: > for each sh in sheets > sh.unprotect > next sh >...

color coding the tabs on excel sheets and bolding them
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) excel Spreadsheet. I used to be able to right click and color a tab on excel sheet..is this ability available on MAC version?? <br><br>Also, when in a tabbed sheet, it was bold or enlarge so you knew which tab you were in, is this available on MAC version or is there some other method to obtain similar goal to either of these? <br><br>thanks for your help and input. On 14/03/10 1:05 PM, in article 59bb52be.-1@webcrossing.JaKIaxP2ac0, "mspbgh58@officeformac.com" <mspbgh58@officeformac.com>...

different pages on each sheet in publisher?
I am trying to do wedding invitations, each page is 3.5 x 7.50, 3.5 x 7.75 and 3.5 x 8. How can I put one of each of these on the page? Also, How can I do tear off reply cards and have the guests names and quantity invited on each one? These are 3.5 x 8.25. Thank you On the three pages sizes, set separate files for each page making three like pages per sheet of paper. On the last page, the one with the tear off reply, you didn't say what size page it was being torn off; if it's an 8.5 x 11 just make it the bottom 3.5 inches. Seems you will be using a paper cutter extensive...

Search for values between two sheets
Hi again I think I've confused everyone with the previous thread. Is it at all possible to do something like a VLOOKUP or anythin similar to search between sheets/ workbooks I appreciate any help you can give guys Joe -- Message posted from http://www.ExcelForum.com Hi Joey I gave you one answer for two sheets. If you have several this becomes more complicated. What do you want to return? - text values - numbers For the latter one there could be a solution using SUMPRODUCT, etc. So you may explain this with a little bit more detail :-) -- Regards Frank Kabel Frankfurt, Germany ...

While linking to other work sheets
Hello, I am creating an EXCEL sheet where in each of 4 sheets in that corresponds to data in sheet1 of a 4 seperate work sheets. I am able to link the other sheets and getting the data from the other work sheets also..But where ever there is a blank cell is there , i am getting a zero. I don't want to appear un necessary zeros in my consolidated work sheet. I tried to use the option from TOOLs -->options-->view and removed the check against zero values.. This way we can't see the zero values but it occupies the space. so the memory of the sheet is getting increased as i am goi...

how to unit data in different sheet into one sheet?
I have same style data in differet sheet: sheet1 q ww w ww e r r e t r (and adding) sheet2 5 f 6 g d h e h g juh (adding) and in sheet3 I want automatic display: q ww w ww e r r e t r 5 f 6 g d h e h g juh any solutions? One play via non-array formulas .. In Sheet1, data is assumed in cols A & B, from row1 down Use an empty col to the right, say col E Put in E1: =IF(COUNTBLANK(A1:B1)=2,"",ROW()) Copy E1 down to say, E100, to cover the max expected data in cols A & B In Sheet2, data is assumed in cols A & B, from row1 down Use an empty col to the right, say col E...

userform commnad from a sheet command
Hello I have the following piece of code in Excel sheet 1 to trigger an event when a cell content changes Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$4" Then 'This Code Runs When Cell A4 Changes' End If End Sub What I want to do is insert here code to interrupt the running of a particular userform and goto a particular point and resume that same userform. So something like Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$4" Then userform1.stop goto userform 1line 220 userform1.resume End If End Sub ...

need to automate a sheet re-organization
Hello. I download a spreadsheet workbook often online that is a DVD database. I need to correct its structure and bring it into 2k7 form factor to make my use of it practical. It is located here: http://www.hometheaterinfo.com/dvdlist.htm The file link is: http://www.hometheaterinfo.com/download/dvdlist.zip It is a 12.7 MB file. More unzipped. 2k3 format. What I need to do is make a small macro that converts it to a 2k7 single worksheet file, and shift one column to the leftmost position. I have been manually performing these tasks by manually saving the...

From Budget to Bills List
Money 2004. Have entered an a monthly entry into the budget. How do I make it appear in the "Bills and Deposit List" so I can record the oayment each month? Thanks Elk. What I would recommend is to remove the manual amount you entered into the budget, then add a new bill in the Bills and Deposits area. If you then go back to the budget, you'll see that it picked up the amount from the bills area. Think of the Bills area as a place for setting up bills that you pay regularly at specific intervals and use the budget area if you want to enter an amount for something that is mo...

Dynamically changing Info from one sheet to another
Hi all, Im not sure if this can be done (pretty new to all this) but what i would like to do is: have one excel document with 26 sheets, each of the 26 sheets will contain a list of approx 1000 names. When a sheet gets updated (a name is deleted or added) i would like it to dynamically change all sheets larger than it. For example if i delete a name of sheet # 9, i would like sheets 10 onwards to to contain that name either but sheets 8 and below must remain unchanged. anyone have an idea how i could tacke this problem? thanks ------------------------------------------------ ~~ Message...

Bills Summary out of Sync with MSN Bill Pay
I am currently using Money 2006 Deluxe. I am currently using MSN Bill Pay as my online bill pay service. I have noticed over the last few months of using Money 2006 (and this happened with 2005, though not as frequently) that the Bills Summary gets out of sync with my MSN Bill Pay. Basically, it will show bills due in February of 2006 when they are due in October 2005. It throws off the cash flow since the cash flow uses the data from the Bills Summary. I can check the settings in MSN Bill Pay and the payment dates are correct. The only way to "fix" this problem is to re-...

Electrical Stencils
Can anyone please point out web sites for free downloads of electrical symbols Cheers Ian Have you tried www.visiostencils.com? ...

Hidden Sheet
I have downloaded an excell spreadsheet. I am trying to work out how the calculations have been done but see that all the formulas are coming from a sheet I can't find on the workbook. I have seen this sort of thing once before but I can't remember how to unhide it. I have tried the window toolbar but the unhide button is greyed out. Probably the Workbook (as opposed to the Worksheet) is protected, Regards "Terrick" <Terrick@discussions.microsoft.com> wrote in message news:8450A5BF-D36E-416C-B5BA-F2B9B85F4CFD@microsoft.com... >I have downloaded an excell sprea...

Money 2007 and MSN Bill Pay
I can logged onto MSN Bill Pay web site but when I try to do it from Money 2007 I received this error message: The password or PIN you entered was wrong. Any suggestions? Thanks It's not your MSN Billpay login (passport ID) -- it's your service account number -- which is most likely your social security number -- to find out for sure, contact MSN Billpay support (located somewhere in the Money help file) and ask for the Checkfree MSN support number -- they'll provide you with your service account number if you don't already have it... hstorey <hstorey@discussions.m...

New to Excel, have to do a grade sheet!
Hello folks-- I have only very limited experience with Excel (just opening documents I've gotten by e-mail, and have never made an Excel document before myself), and I have a course assignment for a *intro to computers* class. I have to make up a grade sheet where different grade types are weighted differently. For example, the Quiz grades are weighted 25%, Unit Tests 40%, and the Final Exam 35%. Also, workbook grades are added into the unit test grades. There are grades listed for 6 people, and of course there are a number of quiz, unit test, and workbook grades for each person, alon...

Self Updating Copy of a Sheet
I have a lot of columns and need different views at a glance. I am thinking of creating copies of the worksheet and hiding columns that aren't needed for certain views. How can I have multiple copies of the sheet so, as the master is changed, it updats the copies? Thanks! -- StompS Portland, OR http://www.geocities.com/pdxinvestr/Stomps.html Hi, Try creating custom views by setting up the page with certain columns hidden & then creating the view [alt+v+v] "add". This approach prevents the need of creating copies of a master sheet & therefore decreases likelihood ...