ms excel question

[quartzz]

i am trying to have two lists in excel sheets that are identically linked, so when i change something in either of them, the other one does exactly the same thing. they start off as copies of each other, and need to stay the same no matter what i do to them, does anyone know how to do this?

thanks
Phil

 

[loup garou]

Say you have a first list that starts on A1, create this list, then highlight the cell for the first item in second list, type in the function bar =A1, then click the little box in the lower right corner of the first cell and drag it down until all the cells are replicated. The values will update when you update the first list. You can also do this across worksheet, multiple columns, etc.

 

[quartzz]

ok....um not quite following 🙂

the second sheet has this in it

='Mbd CPU Speeds'!A6

which contains the originals contents for A6. when i insert a row into A6 on the first sheet, this second sheet's cell becomes

='Mbd CPU Speeds'!A7

rather than the second sheet developing a blank line (which i want it to)

how would i get the second sheet to follow the exact first list, rather than just keep track of the data like this?

thnx for the advice

 

[loup garou]

Ah, I didn't know you would be inserting/deleting rows. I'm not sure how to do that in excel. This is really something that should be done with a relational database, IMO.

 

[quartzz]

oh cool, if thats free and can be implemented instantly I'll have one of those then 🙂

 

[loup garou]

Do you have MS Access installed? It's fairly simple to use.

 

[quartzz]

i do (have never used it) but i need to get the information onto this spreadsheet

 

[loup garou]

If you really need to get it into an XLS file, you can from Access, but it's probably beyond the scope of what you need to do. I'd stick with it and see if someone can help you do it entirely in Excel. Sorry I couldn't help, and good luck.

 

[kranky]

I think if you want to insert a new row in two sheets, you'll have to write a macro and use it when inserting the row.

 

[noxxic]

I'm not sure if I understand your problem exactly, but if what you just want is to track the exact cell position... I'm sure there's a more simple way to do this, like a simple $-like addressing, but for now you can try a these...

(1) =INDEX('Mbd CPU Speeds'!1:65536,6, 1)
Where 6 is the row 6, and 1 is column A.

(2) =OFFSET('Mbd CPU Speeds'!A1,5,0)
Where 5 is the row 6, and 0 is column A.