Updating linked worksheets

    (Hideously frustrated as it took me 15 mins to write my problem and the boards logged me out in the meantime. I am now having to rewrite from scratch :censored: )


    I have been asked to link two worksheets in the same workbook. I need to copy across data if a column says one thing and a blank line if it says another. I am using the formula =IF('worksheetA'!A31="s",'worksheet A'!B31,"-") i.e. if A31 in worksheet A has "s" in it, print the contents of B31 into the cell in worksheet B. If it is not an "s", then print "-" in worksheet B.


    However, if I add in a line into worksheet A (i.e. Insert a line below row 31: row 32), the formula in worksheet B jumps from row 31 to row 33 and does not add in row 32. As lines are being added into worksheet A all the time, how can I get the formula in worksheet B to recognise that a row has been added into worksheet A? I do not want to click and drag the formula through worksheet B each time worksheet A is updated as I have highlighting through worksheet B which I would have to redo each time (and worksheet A is updated several times a day)


    All help will be appreciated!!
    Thanks
    Jo

    It is a very large spreadsheet with sales forecasts for the whole year. When we get a new prospect in, or move a propect to an order in or move an order in to an invoiced, we have to insert it into the appropriate section (or cut it from where it is currently and paste it into the appropriate section). :?

    hmmmm - probably a bit late to say this but it sounds like a case of poor spreadsheet design...


    If it is not too late to change....You would be better off having ONE listing & a column for category... i.e. Prospect, Order, Invoiced ... then it would be easier to report/display only the specific categories relevant to the info yu wish to display... ther would also be no need to cut/copy & paste data around the worksheet & cause you these problems...

    Sadly, it is a difficult spreadsheet to change in the way you have said due to the way that it calculates formula for the sales team's commission (and I musn't muck about with their money!! :redface: )

    Where would this fit into the IF formula? Should it be: =IF(indirect("WorksheetA!A"&row(A31))="s",indirect("WorksheetA!A"&row(A31))B31,"-")


    What I want is:
    If I have rows 31, 32, in worksheet A, they link to rows 31, 32, in worksheet B. Currently if I insert a row into worksheet A so that I have rows 31, 32, 33, when I go to worksheet B, I have rows 31, 33 instead of 31, 32, 33.


    I have now found what happens if I cut and paste a row in the worksheet A - the formula in worksheet B changes to reflect the new location in worksheet A, but the position doesn't change. If worst comes to the worst I can cut and paste each time, but it is laborious (particularly as I will have to check each formula as I am not the only one updating the spreadsheet!) What I need most now is a way of ensuring that new rows are recognised.

    Hi,


    you can rewrite as


    if(indirect("worksheetA!A"&31)="s",indirect("worksheetA!B"&31),"-")


    "row(a31) is only the substitute of 31"


    =if(indirect("worksheetA!A"&row(A31))="s",indirect("worksheetA!B"&row(a31)),"-")


    the reason why I used row(a31) is that if you set
    =if(indirect("worksheetA!A"&row(A1))="s",indirect("worksheetA!B"&row(a1)),"-")
    in cell A1, you can drag down and it increases as you go down.


    hope it helps

    Yay! Its worked :) After a lot of puzzling over a number of things that kept going wrong (I got a row of #REF? at one point, and then a row of 'S' - due to not changing the cell letter in the second half of the formula...), it's finally doing what I want it to do.


    Thank you for all of your help.
    :thanx:

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login