Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Data Entry in Excel (Excel 2000 SR1)

    Is there any way to "tie" a cell into which text/numbers must be entered to a cell that contains a VLOOKUP formula? The problem that I'm having is that I have a data entry sheet where most of the columns are VLOOKUP formulas pulling from a database sheet. These columns are on the data entry sheet for comparative purposes. One column on the data entry sheet is used for the entry of text/values. My problem is that when the database is sorted, the text./values in this column end up being in the wrong rows...I know the simple answer is not to sort the database...but....

    Is there anything else that can be done?

    Thanks in advance,

    Christa

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Data Entry in Excel (Excel 2000 SR1)

    If the vlookup lookup_value argument is tied to a constant value in the database which is sorted, then the sorts shouldn't munge the other data.

    Can you post a censored example? It would help to understand what is going on, and get past my unhelpful reaction of "you need to restructure what you are doing"! I say this because you may need to think about the relational hierachy of the datasets and which data comprise the key index in the data being sorted, and tie the vlookups to those.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Entry in Excel (Excel 2000 SR1)

    I'll try attaching a zip file of a very stripped down version of my spreadsheet. Not sure if it will work because even zipped and with most everything stripped out the file is still 111kb.

    The file contains an Accounts Sheet (the database...and I know there are REF errors in it...that's because I stripped out stuff) and a Meter entry sheet. Most of the fields on the Meter Entry sheet are VLOOKUP formulas coming from the Accounts sheet. The column called Meter Readings is the column into which the values are entered. The database (Accounts sheet) is sorted by account number. When an account is added to the database and it is sorted...all the numbers end up wonky because all the VLOOKUP formulas adjust to reflect the sorting of the database and the Meter Readings column does not adjust.

    I hope this is clear enough...if not...I'll try again.

    Thanks,

    Christa
    Attached Files Attached Files

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Data Entry in Excel (Excel 2000 SR1)

    Can multiple Accounts (over time) attach to one Meter? Can Multiple Meters attach to one Account?

    (Considering the transaction history that is being created by each Meter Read, the long-term solution here is to migrate this to Access.)
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •