Return entire range based on lookup value 2

[meghan95]

I've been at this all day and could really use some fresh eyes. I'm trying to create an automated journal entry form for our accounting department. I have a sheet populated with 20 different standard journal entries. All are identified by a number and include 2 columns:
1-description, 2-account number
All of the journal entries have at least 2 rows and some have more if many different account numbers are involved. Example:
Entry # Description Acct #
1 Transfer from AB 605
Transfer to CD 210
2 Cash received 121
Accrued interest 210
Interest income 620
On my journal entry form, I'd like the user to choose the journal entry # from a dropdown box and have the description and account # auto-populate on the form in separate columns.

Any ideas? I've thought of offset, lookups, index, match and just can't come up with anything that will work. It may just not be possible, but I would appreciate you all taking a look.

 

[ck1999]

Might not be the most efficient way but what about:

column a = item
column b = description
column c = acct#
<ITEM variable in entered from dropbox>
Code:

sub populateform()
Dim a, b,c
dim entry(2,10)
dim item
a=1
b="something"
do while b<>""
c= range("a" & a).value
if c = item then
counter = 1
entry(1,counter) = range("b"&a+counter-1).value
entry(2,counter) = range("c"&a+counter-1).value

do while c=""
counter=counter +1
entry(1,counter) = range("b"&a+counter-1).value
entry(2,counter) = range("c"&a+counter-1).value
loop
b=""
else
a=a+1
b=range("b" & a).value
end if
loop
end sub

now populate form using entry() array

 

[meghan95]

Thanks for the suggestion ck. I ended up using some code that copied and pasted the entire range where I wanted it, but your answer got me thinking in the right direction.

 

[SkipVought]

Hi,

If you want a SPREADSHEET solution rather thatn a PROGRAM solution, take a look at...

How can I rename a table as it changes size faq68-1331

The OFFSET spreadsheet function is an extremely powerful range function that can do EXACTLY what ou have required.

Use the MATCH function to lookup the Entry#. That value is the ROW OFFSET (second argument) in the OFFSET FUNCTION. The only piece that you are missing is the COUNT of ENTRIES which you dont have since your table has BLANKS where the Entry# ought to be (a BAD table design!) If you need the appearance of empty cells, use Conditional Formatting, but by all means INCLUDE the data on every row!

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[meghan95]

Skip,

Thanks for pointing me to the FAQ. That would have worked as well and I may end up going that direction. Maintaining the named ranges could become cumbersome. Yes, it was bad table design. I inherited this project from someone else who said - "Here's the journal entry repository. This is what I want it to do - can't you write a macro or something?" So, before re-doing everything I figured I'd give it a shot with what I had. I think my copy/paste range solution should work since the person using the spreadsheet will often have to make changes or add more text to the description.

Admittedly I am not a code writer, but in playing around with the macro recorder, I came up with this. The user has to type the journal entry # then run the macro from the cell where the data is to be pasted. It's working well so far:

Application.Goto Reference:="INDIRECT(TEXT(RC[-3],0))"
    Selection.Copy
    Sheets("Fund-GJ").Select
    ActiveCell.Range("A1").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _False, Transpose:=False
    Application.CutCopyMode = False
    ActiveCell.Range("A1").Select
End Sub

A star for you too.
Thanks again