• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Multiple Vlookup

Hi,

Can you please tell me how can i get the sub-total of each group, that is in modified input1 sheet, under fixed asset I have opening balance, debit & credit. I want opening balance added with debit & subtract credit, the result below them for each such group. Say E9= E6+E7-E8. Is there any single formulae to be used for this kind of sum as the data is too much so putting it manually will take too long. Please advice.

thanks
shefalika
 

Attachments

Hi Shefali..

You have changed the complete logic with your previous design..

Can you allow me to change the structure. like this..

upload_2014-5-2_12-42-58.png

Check the attached..

Use formula as..
=IFERROR(SUMPRODUCT(OFFSET('Trial Balance'!$B$2,MATCH($D4&" Total",'Trial Balance'!$B$3:$B$189,0),1,,3)*{1,1,-1}),0)
 

Attachments

Hi Debraj,

Firstly, thank you for understanding my requirement. I wanted the same. Can you please tell me how to get it.

regards
shefalika
 
Hi Debraj,

I am sorry but when I put the formula "=SUMPRODUCT(OFFSET('Trial Balance'!$B$2,MATCH(LOOKUP(2,1/ISBLANK($C$4:C4),$C$5:C5)&" Total",'Trial Balance'!$B$3:$B$189,0),1,,3)*{1,1,-1})" in cell F6 & drag along in modified input 1 sheet, its showing"N/A". Can you please tell me how can I get the values in F column in modified input 1 sheet with subtotals in another sheet with the proposed structure you have given.

thnaks
shefalika
 
shefali said:
"=SUMPRODUCT(OFFSET('Trial Balance'!$B$2,MATCH(LOOKUP(2,1/ISBLANK($C$4:C4),$C$5:C5)&" Total",'Trial Balance'!$B$3:$B$189,0),1,,3)*{1,1,-1
Click to expand...

You have to add a blank row.. before each category.. if you want to use this formula.. I constructed this formula according to your previous file structure..
 
Hi Debraj,
Please look into my file. When i give the formula which you asked "
=IFERROR(INDEX('Trial Balance'!$B$9:$E$195,MATCH(LOOKUP(2,1/ISBLANK($C$4:C4),$C$5:C5)&" Total",'Trial Balance'!$B$9:$B$195,0),MATCH(D6,'Trial Balance'!$B$9:$E$9,0)),0)" in modified input1 sheet, the cells are not reflecting the correct value(I have marked in yellow). How can i have the correct values in column F of modified input1 sheet from trial balance so that I can put this formula. If you dont mind can you explain it screen shot.

Thanks a ton
shefalika
 

Attachments

Sorry.. shefali.. I failed..

  • I told you to fixed the structure fo your file.. at least in a pattern..
  • Try to understand the structure patttern and then coem again if required.
 

Attachments

Hi Debraj,

Keeping my previous format, can you please tell me what formula to be used in yellow cells of balance sheet WS from Trial balance WS. For cell F11,F12 & F13 of balance sheet WS, I need to lookup for values C7,D7 & E7 respectively. I should able to figure out all the values of group sub-total.

thanks
shefalika
 

Attachments

Hi Shefali..

Thanks for setting a pattern for all cases..
Try this.. or check the attached..
In Balancesheet > F5 use formula as below.. and drag downward..

=IFERROR(INDEX('Trial Balance'!$B$3:$E$189,MATCH(LOOKUP(2,1/($D$5:D5="Opening Balance"),$C$5:C5)&" Total",'Trial Balance'!$B$3:$B$189,0),MATCH($D5,'Trial Balance'!$B$3:$E$3,0)),0)
 

Attachments

Hi,
I am sorry to trouble you again. I thought, if I learn for one column, I would able to do it on more columns but its not giving the correct values. As you can see, in the monthly trial balance WS, I have the same data that is in trial balance sheet. so, when I put the formula in monthly trial balance, its coming only "Zero". could you please advice.
One more doubt I have is in your formula "
=IFERROR(INDEX('Trial Balance'!$B$3:$E$189,MATCH(LOOKUP(2,1/($D$5:D5="Opening Balance"),$C$5:C5)&" Total",'Trial Balance'!$B$3:$B$189,0),MATCH($D5,'Trial Balance'!$B$3:$E$3,0)),0)" why have you taken from D5 when the data starts from cell D4. Would you mind explaining the logic.

Thanks
shefalika
 

Attachments

To be more specific, I have given the formula in modified input 1 sheet but the resulting values when i cross check is not correct. I thought, if it works for one month data, it will work for rest of it but it doesn't. could you please please help.

thanks
shefalika
 

Attachments

Hi Shefali..

Do you think.. these two sheets are in same pattern.. !!!

upload_2014-5-2_18-47-30.png

  • 1st I tried to catch the pattern.. as "Always space before the bold Cell.." in next version you removed blank cells.
  • The I tried to catch the pattern.. as "Always Opening Balance Word beside Bold Text".. In next version you again changed this..
  • If you want to track only BOLD cell to read the pattern.. if will be more complex than where we have started to track trough BOLD cell only..
Please help us to set a final pattern otherwise.. it will go to never ending thread.. !!!
 
Hi,

I really don't know how to fix a pattern, I want the figures of all sub-totals from trial balance sheet in input-1 sheet to the corresponding figure. Please see my file attached & help me how to do it as I am new to excel.

thanks
shefalika
 

Attachments

Hi,

I need help in data pull from excel sheet. In my file as you can see, there are two sheets. In sheet1, I have the data, and in sheet2, I want the data as shown. For that, I put filter in sheet1 & selected "sundry debtors" & copy,paste it in sheet2. All that i want is, if there is any formula or macro I can have, which will automatically pull all sundry debtors details from sheet1 to sheet2 whenever a change is made in sheet1, the same is reflected in sheet2. Please please help me.

Thanks
shefalika
 

Attachments

Hi Shefali..

Risk analysis.. Hmm.. good..
here is a loosely code..

try this..
In Sheet 2 > In Column C's last cell .. just write the group name..

Let us know the feedback..

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = True
On Error GoTo deb

If Target.Address = "$C$" & [d2].End(xlDown).Row + 1 Then
    If Application.CountIf(Sheets(1).[C:C], Target) = 0 Then
        MsgBox "No Such Group exist"
        Application.Undo
        GoTo deb
    Else
        With Sheets(1).UsedRange.Offset(3)
            .AutoFilter 3, Target.Value
            .Offset(1).Resize(.Rows.Count - 3).SpecialCells(12).Copy Cells(Target.Row, 1)
            .AutoFilter
        End With
    End If
End If
deb:
    Application.EnableEvents = True
End Sub
 

Attachments

You must log in or register to reply here.
Back
Top