dynamic rows

[Peter Bailey]

I would like pointing in the right direction as I am not familiar with Excel
apart from normal use. I have experience of Access and VBA in Access just to
give some background.

I want to look up error codes and get the actions that the department needs
to do to correct the errors for audit purposes. so I have

Person_id, list of errors, error count.

I want to lookup the error code in another worksheet where they are listed
with their actions and dynamically add them to x number of rows of course
number of actions depends on person_id x errors.

I get:
ex 1234 has 3 errors
1234 (personid) "fill in sheet data" (action)
1234 "log off correctly"
1234 "send paperwork"
1257 "contact hub"

and so on.

In this example I need to return the rows from a lookup to dynamically fill
in the 3 rows for 1234 and then go to next unused row and fill in action for
1257.

Can this occurr as the user fills in the error codes on input (similar to I
guess an update event in access)or does a macro need to run via a button
separately once all data is filled in.

regards
Peter

 

[Tom Ogilvy]

Look in Excel VBA help at the vLookup worksheet function

You could prefill it in your action column

=if(error="","",Vlookup(error,errortable,2,false))

you would replace the error, and errortable with the appropriate addresses

to fix the error table is would be like Data!$A$1:$B$200 if it is on a
worksheet named Data in A1 to B200

 

[Peter Bailey]

The action column is not a column it sits on a pre existing form below the
error input details as a final section for the customer. in excel terms it is
sitting

at A112:I132 with merged cells crossways.

the error codes and names are from A10:s45

I dont think Vlookup will work as I would have to have 14 columns across for
the actions.

I hope I have explained well enough

here is a text example
123456

 

[Tom Ogilvy]

If you want to lookup up a value, then your choices are vlookup, lookup,
hlookup, a combination of index and match, using Index and a calculated
value, offset with a calculated value, sumproduct for a unique set of
conditions and possibly a small set of others.

Your additional information creates no clear picture of what you are trying
to do.

 

[Peter Bailey]

Hi Tom

Something happened while I was writing and it got posted incomplete I will
try and explain properly:

A| B| C| D| E NumofErrors (>1)
1 2 1 2 3 4 4
2 1 1 1 1 1 0
3 3 3 1 1 1 3
..
..
5 "Actions"
6 Row 1 learner error 1 "do some action
7 Row 1 learner error 2 "do some action
8 Row 1 learner error 3 "do some action
9 Row 1 learner error 4 "do some action
10 Row 3 learner error 1 "do some action
11 Row 3 learner error 2 "do some action
12 Row 3 learner error 3 "do some action

Looking up the errors and subsequent actions in another worksheet is the
easy part.

How do I get the action rows filled in dynamically?
I know the sum of the error count gives me the number of rows I need and I
know the count of errors per learner gives me the number of rows needed per
learner.

I thought I would create a dynamic array with the count info per learner and
populate that with all the info and actions.

Then put the actions in the rows on the form.

I guess the array size (error count) per learner allows me to fill in the
correct row as the column is always A. I dont know what object methods are
available as excel doesnt show me ie if I go range.(dot) it doesnt give a
dropdown of the methods like access does.

I hope that is a better explanation.

Sorry in advance for my stupidity!

regards
Peter