PDA

View Full Version : Need help: I am out of my depth.



nzginga
03-28-2017, 11:17 PM
Hi Guys,


I am trying to work out a way to have a button on one work sheet that will execute a function that goes through the data in another sheet and brings across data based on 1 set of criteria then sums that data and puts the sum in a different column of second sheet based on a column with account codes in the first sheet. I have tried to explain this in as best I can below using sheet names and identifying rows, columns and criteria. Is someone able to tell me if this is possible I am very new to macros and am bumbling around forums, google and youtube trying to work this out. Would definitely appreciate some help here.


I would need Columns A, B and F copied from "costs" worksheet to "bal" worksheet. Columns A and B of "costs" would be entered into the next available row in Columns A & B of "bal" column F of "costs" would be entered into one of columns H:AU (same row as where data for column A and B have already been placed) based on the value in column J of "Costs" matching the value in row 4 of columns H:AU of "bal".


I would then need the sum(H:AW) (of the corresponding row) to be entered into one of columns D, E or F dependent on the value in column G. If "310" or "311" are in column G of costs the sum (calculated above) would be placed in column F of bal. If "312" or "313" are in column G of "costs", the sum (calculated above) would be placed in column D of bal. If "314" is in column column "G", the sum (calculated above) would be placed in column E of bal.


I will be updating the "costs" spreadsheet's data every 2 weeks and it would be cool if the macro could pick up from the last row it entered the previous time it was executed.


Again, not sure if this is possible and definitely appreciate anyone's help on this.


Cheers

offthelip
03-29-2017, 09:50 AM
This certainly is possible, but it would be much easier if you can post your spreadsheet. Is the match for rows of data to be summed in column D of bal done on columns A and B or just A alone?

nzginga
03-29-2017, 10:57 AM
Hi,

Thanks for the response, I didn't even realise I could attach the spreadsheet, didn't notice the icon. Please find attached.

I don't completely understand your questions so I'll do my best to answer.
I have manually entered the data in row 9 to show how I want it to look after the macro runs (except I want all data from costs sheet that matches the criteria entered into bal).

After the amount is entered into row 9 in a column between H:AU (in this case K9 because in costs sheet in column J the tracking code is 10 which corresponds to column K) I would want the sum of H9:AW9 entered into column F because 426 is in column G of Costs spreadsheet (I forgot to include this account code in my original message it is in addition to "310" and "311").

Does that help?

18801

offthelip
03-29-2017, 11:15 AM
I have had a quick look and immediately all sorts of ambiguities and problems with your specification show up:
1:in your example the amount is entered in column K "because the tracking code is 10" however you also have 10 in columns L and M, do you want these to remain empty forever?.
2: You haven't specified what the "matching criteria" is ( that is what my question was) looking at your spreadsheet can one assume that the matching criteria is column B?
3: what you haven't specified is what to do when we get a second match on column B, I get the impression that you want to add data to the same line as the first match on B.
4:This then raises the question is what to do if they have the same tracking code.
5: on your example you have entered the date of the transaction in column A on the bal sheet, what do we do with the next one?

doing this is very straightforwad in VBA unfortunately I couldn't clearly understand your requirement.

mdmackillop
03-29-2017, 11:37 AM
In Row 4, you have multiple cases of 5, 10, 40. Where should the cost go?
There are figures in column G not used in your D-F allocation. Where should the totals go?

nzginga
03-29-2017, 11:44 AM
I noticed that as I sent it through. I've changed the codes on the attached spreadsheet to negate that issue.

If the figures in column G do not match any of D-F Allocation they do not need to copied from Costs to bal.

18802

nzginga
03-29-2017, 11:55 AM
1. I saw that mistake just after I sent it through and have attached a new spreadsheet in my last post that does not have double ups on the tracking code.
2. I just mean that the macro would go through all data on costs sheet and fill down on bal sheet from the next available row and hopefully the next time the macro runs it can start from where it finished. There is one criteria that places an amount from column F into one of columns H:AU dependant on the tracking code in column J of costs sheet and it matching the same code in on of the columns between H:AU. The second criteria is a sum of columns H:AW in bal and placing that sum into one of column D, E or F of bal dependant on the account code in column G of Costs sheet.
3.If the name in column B of costs matches a name found in column B of bal it does not matter to what I am trying to do. I would want the data entered into the next available row as long as both criteria (question 2) are met.
4. If they have the same tracking code it doesn't matter, it is just a matter of filling down the table on bal and inputting the approriate values into their corresponding columns.
5. The next transaction date would be in the next row with the contact name, the value from F somewhere between H:AU dependant on tracking code in column J and the sum of columns H:AW in one of columns D, E or F dependant on the account code in column G of costs.

I really appreciate you taking the time to look at the spreadsheet and ask questions. Hopefully I have made myself clearer.

Cheers

mdmackillop
03-29-2017, 11:59 AM
Without macros

nzginga
03-29-2017, 12:11 PM
Hmm, it's very close. I think the reason I'm interested in Macros is that it would update the data with a push of a button fill in the from the next available row. I will need to update this sheet every two weeks. If the macro version doesn't work out this is a fantastic back up option though.

How do columns A & B fill? Are they just copy pasted across? Is it possible that if both the account code and the tracking code are not valid that there will be no line entry?

mdmackillop
03-29-2017, 01:30 PM
All data is filled by formula from the Costs sheet. As you add data to the Costs sheet, you need only copy down the entire rows in the Bal sheet. Please note that I inserted blank rows at the head of your Costs data to align the formulae
Note that the formula in G10 is different from G9 due to the position of the preceding balance so CopyDown should be from Row 10 (should this be required)
You could add in AV9 "=IF(SUM(H9:AU9)>0,0,'Job Costs'!F9)" which will allocate costs to this column if no tracking number is allocated.
There is also external data in Column AV; I don't know how this relates to anything.
You could add error checking formula (should be zero) =SUM(H9:AV1048576)-SUM('Job Costs'!F:F) to ensure all costs are carried over