RE: Save user entered value in hidden lookup table.

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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.

--
Hth

Kassie Kasselman
Change xxx to hotmail


"DocBrown" wrote:

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" wrote:

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

Kassie Kasselman
Change xxx to hotmail


"DocBrown" wrote:

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


.

Relevant Pages

  • RE: Save user entered value in hidden lookup table.
    ... that function works to display the table value in the header section. ... Account Expense ... In the header and main area, the account code is selected from a named list ...
    (microsoft.public.excel.programming)
  • Re: Headers and Macros
    ... The easiest way would be to have your macro create the entire header. ... Is there any reason the macro can't come up with the company name and GL account ...
    (microsoft.public.excel.misc)
  • Re: Objection rec.knives PLEASE IGNORE if you dont want to see an off topic post
    ... I ignore bragging, and I expect that if you post on a newsgroup about knives you post about knives, at least initially. ... It's at the point that if Robert posts a legitimate request, ... I definitely don't think you'd lose your account at all. ... Nah it's okay I don't feel the sarcasm at all, the header information wasn't meant for you. ...
    (rec.knives)
  • Re: Date in header
    ... There is nothing in that macro that will of itself add anything to the ... Why not simply create a letterhead template for your users ... Word MVP web site http://word.mvps.org ... No header exists before the macro is run, ...
    (microsoft.public.word.docmanagement)
  • Re: Headers (the same but different for each section)
    ... The problem with putting a macro in a document is not that it ... "Jay Freedman" wrote: ... By doing it this way my users will still have to go into the header ... Now you can put the StyleRef for Heading 7 in the appendix ...
    (microsoft.public.word.docmanagement)