Forum Home
Press F1
Thread ID: 61448	2005-09-05 07:26:00	excel help	jonp (7517)	Press F1

Post ID	Timestamp	Content	User
386081	2005-09-05 07:26:00	I have written a very basic spreadsheet that i can use to track the stock in a shop and at the same time enter the wholesale price and it gives you the wholsale cost and the gst content etc. Like i say its all very basic and the workbook has three worksheets and the following worksheet takes information from the previous sheets. When i add rows to the first sheet it throws the data in the second and likewise if i delete data from the first it again throws the data in the secind and third. is there a way to automate this easily or does it have to be a macro solution ? thanks in advance ps. I'm running excel 2000 on XP home edition	jonp (7517)
386082	2005-09-05 08:12:00	You should be able to just use formulas. It's hard to tell from your description exactly what the issue is but you may just need vlookup's to look up data so it doesnt matter if you move things around. Alternatively if your always needing to look at a certain cell then see the INDIRECT formula. hth	Parry (5696)
386083	2005-09-05 08:49:00	You should be able to just use formulas. It's hard to tell from your description exactly what the issue is but you may just need vlookup's to look up data so it doesnt matter if you move things around. Alternatively if your always needing to look at a certain cell then see the INDIRECT formula. hth Hmmm, currently what i do is edit the data in the first worksheet. then on the top row of the second i have in each cell "=cellx1(sheet 1)" and so on and so forth across row one. then every time i have altered the information in the first workbook i drag down the cells from the top row in the second work sheet to make the values copy over to the second sheet. it is this dragging option that i am trying to automate. Was that any clearer Parry ?	jonp (7517)
386084	2005-09-05 09:15:00	Alternatively if your always needing to look at a certain cell then see the INDIRECT formula. hth I have had a look at the two options and reckon that the INDIRECT formula is the way forward. One question however, I have the following formula below =INDIRECT("'Stock'!D5") in one cell and want to transfer it across for about 15 cells and then down for about 200 rows. Everytime i try to drag it will not move the vale to E5 or D6 for example. Does this mean I have to set up the sheet by hand and add all of the indirect formulas one at a time or is there a shortcut ? Thanks	jonp (7517)
386085	2005-09-06 02:13:00	I have had a look at the two options and reckon that the INDIRECT formula is the way forward. One question however, I have the following formula below =INDIRECT("'Stock'!D5") in one cell and want to transfer it across for about 15 cells and then down for about 200 rows. Everytime i try to drag it will not move the vale to E5 or D6 for example. Does this mean I have to set up the sheet by hand and add all of the indirect formulas one at a time or is there a shortcut ? Thanks Not really, since the address is text which is the point of indirect - to keep the address the same. However, if theres quite a few it may be worth doing the following by using several replaces to achieve the job 1) Enter the formula =('Stock'!D5) and copy down 2) Select Edit|Replace and enter = in the find what and leave replace with blank 3) Select Edit|Replace and enter ) in the find what and ") in replace with 4) Select Edit|Replace and enter ( in the find what and =INDIRECT(" in replace with	Parry (5696)
1