VBA Expertise Needed

[CLM_96]

Hello,

I have very little understanding of VBA and I believe, though could be wrong, this is what I need for what I am trying to do.

This is my attempt at explaining what I need in the most uncomplicated way possible:

I have a table of information sat in excel, for which I have a different work sheet (We'll call this Worksheet 'A'), pulling through information based upon a set of criteria (in the form of drop-down selections). Now in sheet A, once the information has pulled through, a column at the end will be filled with manual data based upon the pre-populated data. I need this info to be saved/ written, probably in a different sheet or in the table (not sure how it would work), so that when the drop-down selections change this info is no longer there as it isn't related to the new criteria. However, should the first set of criteria be re-selected, I would need said information to feed back in.

I have zero idea how to do this, and maybe I would need some sort of database program to do this, like MS Access??

PLEASE HELP!!!

Thank you.

 

[oldbrewer]

a macro will populate your new column, then when new drop down selections are made, the new column will be overwritten. It does not matter if this column is on a new sheet or not.

Stated simply, you seem to be filling a table with inputs and other parts of the table are calculations based on those inputs. So why cannot the new column be to the right of your table ?

 

[CLM_96]

Hi, Sorry it's been so long! I have been off of work.

I think it matters because of the certain criteria involved. After the drop down selections are made, the new column will be overwritten via user input, I then need this overwrite to be saved somewhere such that when the drop down selection is made again the newest values are retrieved. But obviously, those value are not related to the other drop down choices. Hence the thought that a different table is needed to store this data, with such criterias that a formula could find it. Code below has been my attempt, though it does not work!

Sub Refresh_A()
Dim rng As Range
Dim tm As Range
Dim dealer As Range


Set rng = Range("C9:C700")
Set tm = Range("D2")
Set dealer = Range("D3")

For Each cell In tm
If cell.Value = "Summary" Then
For Each cell In rng
If cell.Value <> "" Then
cell.Offset(0, 15).Value = "B Summary"
cell.Offset(0, 16).Value = "B Summary"
Else
For Each cell In d
If cell.Value = "Summary" Then
For Each cell In rng
If cell.Value <> "" Then
cell.Offset(0, 15).Value = "Summary of TM"
cell.Offset(0, 16).Value = "Summary of TM"
Else
For Each cell In rng
If cell.Value <> "" Then
cell.Offset(0, 15).Value = "TM D Product"
cell.Offset(0, 16).Value = "TM D Product"
End If
Next
Range("A1").Select
End Sub

The Cell offset value will be the sumifs to pick up the info, for ease of use I have put simpler terms in.

 

[oldbrewer]

can you make say a 3 row mock up and ask the question in terms of this, please ?

 

[CLM_96]

Sheet 1 (Master) looks like this…
https://imgur.com/a/GJdcf

Sheets 2 - X (Servant) looks like this…
https://imgur.com/a/jq0jp


On the Servant sheets D2, D3 are Drop Down choices. IF the current selection of C1,C2,C3 AND the Columns B,C, D on the Servant sheets have a corresponding row on the Master sheet, then I need to Sumifs Columns R, S. However, If no such row exists, I need to append the data so that such a row exists and the sumifs can be added. So in the example above, I would need the master sheet to be appended to add A = Apple, B = TM, C = Carrot, D = AB2, E = "", F = Blue and the AB3, then the sumif to pull through the respective values in column R and S on every row in the Master sheet.


I hope this makes sense, I wasn't sure how to upload a sheet on to the site and this is the only way I could think to.


Thanks for your help!

 

[oldbrewer]

I have looked at your two images, but still cannot understand what you want. I hope somebody else can assist. By the way if you highlight say A1 to T20 and copy, you can paste into the reply box here