Save user entered value in hidden lookup table.

[DocBrown]

I would appreciate help on what would be the correct approach to solve this,
if possible.

The purpose of the worksheet is to keep track of how much was spent from a
set of account codes. The following depicts the relevent cell from the hidden
area where the list is stored, the Header section and the main area where the
expenses are entereed.


Hidden List area:

A B
Account code Allocated
-------------------------
10 | 01-6255-400 $1200.00
11 | 01-6255-430
12 | 01-6425-200
13 | 01-6425-430 $1500.00


The header section has:

C D E
Account code Allocated Remaining
--------------------------------------------
20 | 01-6255-400 =B10 $500.00
21 | 01-6425-430 =B13 $895.00
22 | <blank>
23 | <blank>


The main section may have:

B C
Account Expense
| ---------------------
30 | 01-6255-400 $350.00
31 | 01-6425-430 $675.00
32 | 01-6425-430 $220.00
33 | 01-6255-400 $150.00

In the header and main area, the account code is selected from a named list
at A10:A13. There is a second lookup list defined as A10:B13

Here the $1200.00 is associated with the *-400 code and the $1500.00 with
the *-430 code.

The process would be: In C20 of the header the user selects the *.400
account code, the user enters a value for Allocated, say the $1200.00 in D20
(or maybe a dialog box). That value is stored in such a way that keeps it
associated with the *.400 account code(say at B10). If the user now selects
*.430 account code in C20, then cell D20 displays the value associated with
the *.430 code, and blank if no value is yet defined.

Essentially, I want to save a user entered value in a lookup table and
redisplay it as needed.

Thanks in advance of any suggestions.
John

 

[Kassie]

You can use VLOOKUP to select the correct amount.
=IF(VLOOKUP(C20,A1:B13,2,0)="",0,VLOOKUP(C20,A1:B13,2,0)

 

[DocBrown]

Hi Kassie,

Thank you for your reply.

Yes, that function works to display the table value in the header section.
What I'm unable to do is to provide a method for the user to be able to
change the value that is stored in the hidden table without the need to
unhide the table.

Ideally, I'd like the user to also be able to do the following:

There are two named tables:
AccountCode = A10:A13
LookupAcct = A10:B13

1. Header rows start out blank. Cells c20:c23 validation is allow 'List'
with source =AccountCode.
2. User selects an account in C20. If there's a value in B10, it is
displayed in D20. (The formula above is perfect for this.)
3. At this point I want the user to be able to enter a (new) value in D20 or
somewhere else in the headerand that entry changes the value in B10.
4. This same behavior would apply to rows 20 thru 23.

Maybe a command button, macro, something... Any ideas?

Thanks a bunch
John

 

[Kassie]

You've hit the nail on the head there! A command button with a macro will do
the trick. The macro would unprotect the sheet, reset the specific cell's
value to the one inserted, redo the formula, and then protect the sheet again.

Off to church at the moment, but if you still do not have a response when I
come back, I'll write a macro for you.

 

[DocBrown]

Oh, I am soooo close!

Here's what I did. Let me know if I've overlooked something.

I defined two new cells and a command button to do the following.

C25 - Cell with validation is allow 'List' with source =AccountCode.
D25 - where user enters a value.

Command button next to D25 Macro code is:


Sub Button104_Click()
Dim rng1 As Range
Dim c As Range

With Range("AccountCode")
Set c = .Find(Range("C25").Value, , xlValues)

If Not c Is Nothing Then
c.Offset(0, 1) = Range("D25").Value
End If
End With
End Sub

To make this work, I unlocked the cells in the B column cells in the
LookupAcct table. This allows the macro to alter those cells.

This macro assumes that the entries in AccountCode are unique. I thought
this would be cleaner than having the need to deal with the formulas in
D2023.

The only issue I seem to have is that when I click in D25 and enter a value.
then when I click the button nothing happens until I tab (or click) out of
cell D25. If I tab out and back in, the button works. Why is it necesary to
tab out of the cell to get the command macro to run?

Thanks again.
John