Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    formula help to update one sheet from another (2003 SP2)

    Evening all

    In the attached workbook the worksheets StockControl and Summary share the same column headings, the sheet StockControl will eventaully be hidden but will be completed by the userforms 'add stock' and 'remove stock', what formula could I use on the summary worksheet $B$3:$AP$25 to capture and update the information that will be recorded on the StockControl worksheet.

    I hope that makes some sort of sense

    Cheers

    Steve
    Attached Files Attached Files
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

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

    Re: formula help to update one sheet from another (2003 SP2)

    Enter the following formula in B3 on the Summary sheet:

    =SUMIF(StockControl!$A:$A,$A3,StockControl!B:

    Note the use of absolute and relative references. Fill down, then right (or fill right, then down).

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formula help to update one sheet from another (2003 SP2)

    Cheers Hans

    Off to play, I had been trying Match to no avail, I will report back

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  4. #4
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formula help to update one sheet from another (2003 SP2)

    Editted: Having experimented I see it was suppposed to change but that has caused another problem that I will try to fix.

    Hi Hans

    Just a quick question - Whenever I click the right corner of the cell and drag down the $A3 changes to $A4, $A5 etc. is there some setting I need to turn off, I am changing these manually now but for future it would save me a lot of time.

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  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: formula help to update one sheet from another (2003 SP2)

    I am confused by what you need. Hans' formula locked the formula that way to update the rows. If you want a formula which does not update the row then add a dollar sign ($) before the row

    $A$3 will copy as always being $a$3 as you drag columns or rows since both are locked. When editing <F4> toggles the 4 possibilities: $A$3, $A3,A$3,A3...

    Steve

  6. #6
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formula help to update one sheet from another (2003 SP2)

    Hi Steve

    I did edit to show that I realised that I had made a mistake, I was just on the verge of reposting to say that I had a 'grey' hair day and had put things in the wrong place, the formula did of course do exactly as it said on the tin.

    Cheers
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

Posting Permissions

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