• 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.

Sum an amount column depending on criteria being met

S

spk1009

New Member
I have a daily spreadsheet with ref numbers and amounts and then I have another workbook "template". Depending on the ref number in the Template, I want to lookup that ref number in the daily spreadsheet and find the amount column and sum the values (Not including the total).


Here is a sample of the daily spreadsheet:


A B C D E F G

1 ACCT: 126


4 STOCK ID NAME NUMBER WITHHELD TAKEN AMOUNT


6 26 409 company a 509156 0 64.67 1,012.89

7 26 2190 company b 509157 0 -689.15 4,755.13


------------- ------------- ------------


11 0 -624.48 $5,768.02
 
you would be better off uploading a sample of the spreadsheet....


SUMIF may be something that will work for you... a LOOKUP fucntion may work as well; its hard to visualize how your spreadsheets are setup soley from your discription.
 
That hard part is that you have two different workbooks. That limits the functionality of many functions. Any chance these could be combined into a single workbook?
 
Hi, spk1009!


Please read the first three sticky green topics at this forums main page. You'll find there the general guidelines for such task among other recommendations.


Regards!
 
Hi, spk1009!


Here's only one workbook, you mentioned two. Could you please upload the other too? Thank you.


Regards!
 
Hi, spk1009!


What a strange design for searchable data, it seems to be an old line printer or DOS screen output... it actually helps in nothing to use Excel functions, so you'll have to deal with a little of VBA code.


Please confirm if the only template cells of be used as search arguments into daily spreadsheet are D19, C22 and C23. If so, which are the search criteria:

a) D19 equal A1 or A18

b) C22 equal A4:A7 and it'll return 5768.22, and C23 equal A18:A23 and it will return 530.39 for C23

c) there won't be any more row in C24 and so on?


Regards!


PS: note that I've been very polite with the chosen adjective about searchable data design, you wouldn't like to hear what I actually thought :)
 
Hi, spk1009!


Here's the link to the two files, even only the template (first) was updated:

http://dl.dropbox.com/u/60558749/Sum%20an%20amount%20column%20depending%20on%20criteria%20being%20met%20-%20INPUT%20TEMPLATE%20%28for%20spk1009%20at%20chandoo.org%29.xlsm

http://dl.dropbox.com/u/60558749/Sum%20an%20amount%20column%20depending%20on%20criteria%20being%20met%20-ckreg_today-sample%20%28for%20spk1009%20at%20chandoo.org%29.xlsm


These are the modifications made:

a) changed cell D19 from "Stock# nn" to nn formatted "Stock# "0 so as to hold a numeric value

b) added a cyan button "Query daily spreadsheet" to perform the retrieval operations

c) coded macro for "cmdQueryDaily_Click" event


Hope it helps you, if it doesn't just advise.


Regards!
 
Hi SirJB7,


I totally appreciate your great efforts on this challenge..I agree with you on the format being "old" but that's all I have to work with.... :-(


The files you send work great....I will have to see if they work with my other templates, I know I will have to do some edits. The INPUT template remains in the same format, but what changes is the daily data file I recieve..the number of entries is different every day.


So I'm not sure if the macro will work with each day's file?????


But I will try and let you know. Thank you so much!
 
Hi, spk1009!


Glad it worked. And about the format... well, I didn't mean that you might use it in Visicalc instead of Excel but...

The input template has five named ranges, StockCell, Account1Cell, Account2Cell, Account1AmountCell and Account2AmountCell: if you change the template and reassign the sames named ranges, it'll still works without any modification.

About the daily data file, there are this constants defined at the top of the Template VBA sheet code:


-----

[pre]
Code: 
Const ksCKregAccountPrefix = "ACCT: "
Const ksCKRegStock = "STOCK"
Const ksCKRegAccountColumn = 1
Const ksCKRegStockColumn = 1
Const ksCKRegAmountColumn = 7
Const kiCKregEmptyRows = 10
[/pre]
-----


If they come in the same order, first ACCT: XXXX, then STOCK and then the data lines, you'll only have to change the value of column numbers and empty row spacing.


Just advise if in a different format. Maybe you have machines with CPM-80 still running. BTW, the display is green or amber phosphorus?


Regards!
 
I updated some of the other templates and I'm getting a compile error at this row


' start

' ranges

' daily spreadsheet

----->>>> Workbooks(ksWorkbookDailySpreadsheet).Activate

Worksheets(ksWorksheetCKReg).Activate

Set rngDaily = ActiveWorkbook.ActiveSheet.Cells

' template (this workbook)

ThisWorkbook.Activate


Any suggestions? thanx!
 
Hi, spk1009!


You'll find this constants at the very top of the code:

-----

[pre]
Code: 
Const ksWorkbookDailySpreadsheet = "Sum an amount column depending on criteria being met -ckreg_today-sample (for spk1009 at chandoo.org).xlsm"
Const ksWorksheetCKReg = "ckreg"
[/pre]
-----


The first one contains the name of the daily workbook and the second one the name of the worksheet within it. In the uploaded file they were set with the filename I used and with the worksheet name you used. Try changing both to the new daily spreadsheet source.


If it doesn't work, please upload the file to check it.


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