Unable to create index and match formula

[avikchanda33]

Dear Sir

I have created a Excel invoice format but unable to create a formula which calculates HSN wise Unit wise total of taxable value (including Other charges i.e. freight/ loading charges and discount) and also Tax on them.

I have attached my work sheet, I would be grateful if anybody can help.

 

[AlanSidman]

I looked at your invoice template but could not find any data to lookup. Where are your look up tables? Without them, there is no way to magically make the data appear in your invoices.

 

[avikchanda33]

Sir

Thanks your your reply

My data is the invoice item fields i.e. HSN code, Item Description, Unit & Amount. I have attached files with data, when i convert it into table headers get changed.

 

[AlanSidman]

I don't understand what you want. In order for me to assist you, you will need to explain what you want to happen. Provide an example as you explain your needs. Looking at your invoice, I have no idea what you want or need. Mind reading is not my specialty.

 

[avikchanda33]

Sorry I am being unable to explain my need, so I have attached sample worksheet with data. Red Mark Fields in my sample sheet is my requirement which I have put up manually. Hope that will explain my problem.

Thanks in advance

 

[bosco_yip]

Hi,

1] Formula solution with helper columns added in column N and O

2] See attached file

Regards
Bosco

 

[avikchanda33]

WONDERFUL

IT IS WORKING

THANKS A LOT

 

[avikchanda33]

Hi

In the attached sheet I have a report section in RED marked field (thanks to bosco) which automatically calculates HSN wise-Unit wise values, taxes from my database but when I add or delete some data from database rows in red marked field is not reacting. You will find that in Red marked table I have 5 rows, but if the HSN & Unit wise data goes up to 6 numbers or goes down to 4 then I have to manually adjust the same.

So is it possible that Red Mark table will update automatically depending on number of HSN-Unit wise data i.e. increase of row or decrease of row automatically.

 

[bosco_yip]

Formula way cannot increase or decrease of row automatically.

Regards
Bosco

 

[pecoflyer]

Please don't zip your files when they are small