help help help

A

anu

New Member
Joined
Mar 11, 2009
Messages
4
Dear all,

I need help and it is very urgent so please please help.

<TABLE style="WIDTH: 919pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=1220 border=0><COLGROUP><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3035" width=83><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><COL style="WIDTH: 206pt; mso-width-source: userset; mso-width-alt: 10020" width=274><COL style="WIDTH: 95pt; mso-width-source: userset; mso-width-alt: 4644" width=127><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" span=3 width=94><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" width=94><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" span=3 width=94><TBODY><TR style="HEIGHT: 30.75pt; mso-height-source: userset" height=41><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 62pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 30.75pt; BACKGROUND-COLOR: #ecdef6" width=83 height=41>Date</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 59pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ecdef6" width=78>Invoice Reference</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 206pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ecdef6" width=274>Particulars</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 95pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ecdef6" width=127>Analysis Account</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 71pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ecdef6" width=94>Total</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 71pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ecdef6" width=94>VAT</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 71pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ecdef6" width=94>NET</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 71pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ecdef6" width=94>Fee Income </TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 71pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ecdef6" width=94>Sale of assets </TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 71pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ecdef6" width=94>Expenses reclaimed </TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 71pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ecdef6" width=94>Other</TD></TR><TR style="HEIGHT: 16.5pt; mso-height-source: userset" height=22><TD class=xl67 style="BORDER-RIGHT: #f0f0f0 0.5pt; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0 0.5pt; BORDER-BOTTOM: #f0f0f0 0.5pt; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" height=22> </TD><TD class=xl67 style="BORDER-RIGHT: #f0f0f0 0.5pt; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: transparent"> </TD><TD class=xl67 style="BORDER-RIGHT: #f0f0f0 0.5pt; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: transparent"> </TD><TD class=xl69 style="BORDER-RIGHT: #f0f0f0 0.5pt; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: transparent">Fee Income </TD><TD class=xl74 style="BORDER-RIGHT: #f0f0f0 0.5pt; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: #f2f2f2"> </TD><TD class=xl74 style="BORDER-RIGHT: #f0f0f0 0.5pt; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: #f2f2f2"> </TD><TD class=xl74 style="BORDER-RIGHT: #f0f0f0 0.5pt; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: #f2f2f2">80.00 </TD><TD class=xl75 style="BORDER-RIGHT: #f0f0f0 0.5pt; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: #f8f2fc"> 80.00</TD><TD class=xl75 style="BORDER-RIGHT: #f0f0f0 0.5pt; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: #f8f2fc"> </TD><TD class=xl75 style="BORDER-RIGHT: #f0f0f0 0.5pt; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: #f8f2fc"> </TD><TD class=xl75 style="BORDER-RIGHT: #f0f0f0 0.5pt; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: #f8f2fc"> </TD></TR></TBODY></TABLE>

this is how it looks. Now i have list of values to be selected in analysis account column.

If you notice, all those values are also defined as columns.

So what i need is a formula which willl check value selected in analysis account and based on that it should save the value of net amount column to that particular column as shown above. Vlookup and hlookup doesn't work and it is a combination and I did try if it works but when i try to copy that formula on other cells, i need to change cell references manually which is crazy.

please help
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
Your title is very vague!

What is your formula?
Locking cell references will help to manually change them.

Craig
 
Upvote 0
anu Hi & Welcome,


You'll need to be a little more specific as to what you want, by the sounds of it what your after is doable but in order to get a specific solution your going to need to help a little more.
 
Upvote 0
i am just asking for help as i am desparate

I need formula which will sort this issue
 
Upvote 0
Suppose your data start in A1. Then, the Analysis Account is col. D, the Net account is col. G, the Fee Income column is col. H and the 'Other' column is col. K.

The first row with data will be row 2.

Now, in H2 enter the formula =IF($D2=H$1,$G2,0). Note the use of relative-absolute addresses. Copy H2 to I2:K2. Copy H2:K2 as far down as needed.

anu said:
Dear all,

I need help and it is very urgent so please please help.

<TABLE style="WIDTH: 919pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=1220 border=0><COLGROUP><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3035" width=83><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><COL style="WIDTH: 206pt; mso-width-source: userset; mso-width-alt: 10020" width=274><COL style="WIDTH: 95pt; mso-width-source: userset; mso-width-alt: 4644" width=127><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" span=3 width=94><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" width=94><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" span=3 width=94><TBODY><TR style="HEIGHT: 30.75pt; mso-height-source: userset" height=41><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 62pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 30.75pt; BACKGROUND-COLOR: #ecdef6" width=83 height=41>Date</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 59pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ecdef6" width=78>Invoice Reference</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 206pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ecdef6" width=274>Particulars</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 95pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ecdef6" width=127>Analysis Account</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 71pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ecdef6" width=94>Total</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 71pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ecdef6" width=94>VAT</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 71pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ecdef6" width=94>NET</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 71pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ecdef6" width=94>Fee Income </TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 71pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ecdef6" width=94>Sale of assets </TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 71pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ecdef6" width=94>Expenses reclaimed </TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 71pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ecdef6" width=94>Other</TD></TR><TR style="HEIGHT: 16.5pt; mso-height-source: userset" height=22><TD class=xl67 style="BORDER-RIGHT: #f0f0f0 0.5pt; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0 0.5pt; BORDER-BOTTOM: #f0f0f0 0.5pt; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" height=22> </TD><TD class=xl67 style="BORDER-RIGHT: #f0f0f0 0.5pt; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: transparent"> </TD><TD class=xl67 style="BORDER-RIGHT: #f0f0f0 0.5pt; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: transparent"> </TD><TD class=xl69 style="BORDER-RIGHT: #f0f0f0 0.5pt; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: transparent">Fee Income </TD><TD class=xl74 style="BORDER-RIGHT: #f0f0f0 0.5pt; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: #f2f2f2"> </TD><TD class=xl74 style="BORDER-RIGHT: #f0f0f0 0.5pt; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: #f2f2f2"> </TD><TD class=xl74 style="BORDER-RIGHT: #f0f0f0 0.5pt; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: #f2f2f2">80.00 </TD><TD class=xl75 style="BORDER-RIGHT: #f0f0f0 0.5pt; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: #f8f2fc"> 80.00</TD><TD class=xl75 style="BORDER-RIGHT: #f0f0f0 0.5pt; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: #f8f2fc"> </TD><TD class=xl75 style="BORDER-RIGHT: #f0f0f0 0.5pt; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: #f8f2fc"> </TD><TD class=xl75 style="BORDER-RIGHT: #f0f0f0 0.5pt; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0 0.5pt; BACKGROUND-COLOR: #f8f2fc"> </TD></TR></TBODY></TABLE>

this is how it looks. Now i have list of values to be selected in analysis account column.

If you notice, all those values are also defined as columns.

So what i need is a formula which willl check value selected in analysis account and based on that it should save the value of net amount column to that particular column as shown above. Vlookup and hlookup doesn't work and it is a combination and I did try if it works but when i try to copy that formula on other cells, i need to change cell references manually which is crazy.

please help
Click to expand...
 
Upvote 0
i did try this but the trouble is is changes the cells so when i copy that in i2 then formuila becomes H2= i1
I then to amend formula everywhere.

Please tell me any other way todo this if possible
 
Upvote 0
Then, you did not get the absolute-relative address correct. Look at my formula. Some references have a $ in front of them (absolute reference) and others do not (relative reference). The latter change when copied to another cell, the former remain static.
anu said:
i did try this but the trouble is is changes the cells so when i copy that in i2 then formuila becomes H2= i1
I then to amend formula everywhere.

Please tell me any other way todo this if possible
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

2
  • Solved
A little help with looping please
2
Replies
10
Views
882
GWteB
GWteB
D
  • Question
SpreadsheetML Font - Calibri vs Calibri (Body)
Replies
3
Views
1K
Alex Blakenburg
A
M
  • Question
left algin table by pixels
Replies
0
Views
564
MinMax
M
M
  • Question
Drop down select
Replies
1
Views
1K
madu
M
C
  • Question
Request for a VBA code to login
Replies
7
Views
2K
Rijnsent
Rijnsent

Forum statistics

Threads
1,203,456
Messages
6,055,543
Members
444,794
Latest member
HSAL

Share this page

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
Back
Top