Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Oroville, California, Uruguay
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Railroad, changing single cell values (1997)

    Is there any way that the information an a given single cell can change as information is received from another sheet. In other words, I have a column (A for instance) in sheet one that the value changes in subsequent rows for an employee, depending what type of job he/she does. In sheet two, cell "S34" represents the pay rate for that job. Since sheet one, column "A" will change for each row, I want sheet two, cell "S34" to change as the values in sheet one changes.
    Thanx

    Nugget

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Railroad, changing single cell values (1997)

    If column A in sheet one contains multiple job types, how can you display a single pay rate in S34 in sheet two? Perhaps you mean that you want S34 to display the pay rate corresponding to the job type in the selected cell in sheet one. But that is not practical - what if the selected cell in sheet one is not in column A, or if the selection in sheet one consists of more than one cell?

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Railroad, changing single cell values (1997)

    Not sure I understand completely, Here is a "guess" at what you want to do:

    If you want sheet2!S34 to be dependent on Sheet1!A34 you could do this with a linked formula:
    You could for example, have the formula for Sheet2!34 be a lookup in sheet3 then do a lookup to "grab" the job desc from sheet2!A34 and "lookup" in the table for the payrate.

    Assume Sheet3!$A$1:$B$15 is a table of job desc (col A) and payrate in col B. If this formula is in Sheet2!S34:

    <pre>=VLOOKUP(Sheet1!A34,Sheet3!$A$1:$B$15,2,FALSE )</pre>


    it will get the value in Sheet1!A34 and look for the value in the Sheet3!A1:A15, if it it finds it, it will return the value in the 2nd column of the range (col [img]/forums/images/smilies/cool.gif[/img] giving the payrate. If it it does not find it, it will return a #N/A error.

    Steve

  4. #4
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Oroville, California, Uruguay
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Railroad, changing single cell values (1997)

    Sheet 1, column "X" is an ever changing value, as when a person is called to work, they can work various positions (Conductor, Trainman, Engineer, etc). I have a look table to choose which position is worked, and a cell tagged for their respective pay. The pay rate for each position worked is different. I also have various cells tagged with arbitraries. These arbitraries are dependent upon the type of job worked, and pay rate. In order to not confuse the issue, I have made a separate cell to show the rate of pay for each arbitrary, and want that cell (in sheet 2) to change as the column is moved down to a new row in shet 1 as the individual works a new position. Sheet 1 is already tagged with a HUGE lookup table, and sheet 2 is where the final answer(s) for pay is placed. When the employee prints out sheet 2, if I make all of these cells differrent, it would take reams of paper for him/her to receive a recapitulation of their pay. So, I am trying to get the cell referenced to in sgeet 2 to change as the rows change in the respective column in sheet 1. If I tag it with $A$1:$B$15, I get results of A1:B15, and I want results of sheet 1 A1:A15 to go to just only cell in sheet 2 (A34). It is just the opposite of what you have suggested.

    Hope I haven't confused the issue.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Railroad, changing single cell values (1997)

    Not sure exactly what you are saying. My next "guess"
    If you want to change the reference table so when a value changes the actual "lookup" table changes, you can use the indirect function to give the lookup table.

    If you give a small "example" book (we don't need all the other info) just the relevant sections we might be able to understand it better.

    Steve

  6. #6
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Oroville, California, Uruguay
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Railroad, changing single cell values (1997)

    Steve: I am on the road, and will not be back for a couple of days. What I want to achieve is to take column A in Book-1 (which is, for instance the column that sets pay for a meal period while on a train). The amount of monies in this column are tagged to a several column lookup table, which represents several factors, and thusly, the amount of monies is ever changing, depending upon the condition. Cell A1 can yield $6.00, Cell A2 can produce $12.00, Cell A3 can be $xx.oo, and so on.) In Book-2, I have a single cell (let's call it cell C33) that represents a meal period. There can be 12 to twenty types of positions that could receive pay for this scenario. If Column B in Book-1, so to speak, is tagged with a certain number, that number would represent the position worked, and then all of my valuse relating to that position would be calculated, and column A in Book-1 would yield results for a meal period for a certain position worked..

    What I want is cell C33 in Book-2 to be ever changing as the rows in column A in Book-1 change. Probably an impossibility, but thought that i would take a try.

    Larry

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Railroad, changing single cell values (1997)

    I'm afraid I don't understand at all.

    I think you need to provide a representative workbook with some sheets which demonstrate what you need.

    Steve

  8. #8
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Oroville, California, Uruguay
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Railroad, changing single cell values (1997)

    Steve, I have decided thatwhat I want to do is impossible. Thanks for your help. I am addint the following to my spreadsheet with your pwemission. If I quote it wrong, lease let me know.

    Technical Credits
    Efforts by the following people and organizations are gratefully acknowledged:
     Mr. Paul C. Corrado for his kindness and guidance.
     Steve, (sdckapr, a WMVP) at Woody

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Railroad, changing single cell values (1997)

    "Impossible" is such a tough word. You would be very surprised what can be done in excel <img src=/S/grin.gif border=0 alt=grin width=15 height=15>.

    I can't answer if what you want to do is or is not possible in excel, since the last question (as far as I know) was not understood by me (or anyone else on this site). If you try rewording it, we might be able to come up with something. If it can be done manually, we probably can do it with macros if not a formula or a custom function.

    Concerning credit. I appreciate the thanks, but you don't have to single me out for credit. Personally, I would prefer that you just give credit to the lounge itself with the website and not single out any particular person.

    Something like:
    "The volunteers at Woody

Posting Permissions

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