• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Can I tie one cell to another (not link)?

Roxhan

New Member
Hello Excel Gurus,

I have two workbooks - I copy the cells from the original 'Data' workbook, and paste links into my 'WIP' workbook, then reorganise the column order etc.
While most info is pulled through from 'Data' workbook, I still have several columns in my 'WIP' which require manual input.

Question
Is there any VBA coding that allows me to 'tie' the manual cells to the linked cells in the same row... so if the linked cells drop down a row, the manual cells drop down too? See example below

Scenario example
In 'WIP' doc
Cell A10 has been manually updated with a job number eg 'OPT101'.
Cell B10 is linked to 'Data' Cell F10 which has the job name eg 'New Product Launch'
Row looks as follows:
Column A (manual input job no.)Column B (linked to 'Data' Workbook, cell F10)
Row 10OPT101New Product Launch

Then someone adds a new row at row 9 in 'Data' workbook (ie Row 10 drops down to Row 11)...

Back in 'WIP' doc
Rows now look as follows:
Column A (manual input job no.)Column B (linked to 'Data' Workbook, cell F11)
Row 10OPT101
Row 11New Product Launch

Any thoughts or ideas would be greatly appreciated
Thank you in advance,
Rox
 

BobBridges

Active Member
I'm having trouble visualizing why "OPT101" wouldn't drop down to row 10, too, if someone inserted a new row at 9. Are you sure that someone isn't inserting just a cell B9, and not a whole row?
 

Roxhan

New Member
I'm having trouble visualizing why "OPT101" wouldn't drop down to row 10, too, if someone inserted a new row at 9. Are you sure that someone isn't inserting just a cell B9, and not a whole row?
Hi BobBridges,
Column A is manual input in this sheet called 'WIP'.
Column B is linked to another workbook called 'Data'.
The new row is inserted in the 'Data' workbook, hence when I refresh the links in WIP, Row 10 drops down in Col B, but Row 10 stays put in Col A... does that make sense?
Thanks again
Rox
 

BobBridges

Active Member
Sounds like I misunderstood which workbook had the straying formula. But it's still not clear to me. I think you need to attach the two workbooks so I can see it for myself. I realize you have have proprietary data in there; if so, just make a simplified copy with a few sample rows and sanitized data.
 

arwincric

New Member
I have posted an article and inserted table in it. I made the table using ninja tables WordPress plugin and table is about t20 world cup schedule. You can check it, can you tell me how to merge cells and add links in it.
 

vletm

Excel Ninja
arwincric
You should open a new thread for You.
This thread belongs to Roxhan.
Please reread Forum Rules
especially How to get the Best Results at Chandoo.org
 
Top