It's free to register, to post a question or to start / join a discussion
Yet another Excel problem!!
Likes # 0
Posted January 18, 2013 at 5:54PM
Before going any further I should explain that I am something of a dinosaur, in that whilst I have Windows 7 64Bit Home Premium as my OS, I am still using Office Professional 2003.
A little while ago I was asking about an Excel problem. Having sorted that out I have now applied those answers to my own problem, with some success. My problem - at the moment - is that my wife and I have some shares, some in a CREST account and some in an ISA, and I am trying to keep a daily record of each share in each account. What I am, also, trying to do is to keep the workbook to a minimum size, and I have had some success but I think I can see a way to further decrease the overall size, if it can be made to work.
I have a workbook and a single spreadsheet. On the single spreadsheet are our shares which I download from my broker’s site every day. So our shares span about 25 rows, and what I do is to copy and paste these on to the single spreadsheet, so I now have a running detail of our shares and I can go back to any date I have in the list and copy the data for that day and compare with the data for any other day. When I have pasted the shares onto the single spreadsheet I then copy it on to a sheet in the workbook, and from this the information I want is transferred into each account! However what I would now like to do, would be to insert the share prices onto the sheet in the workbook where I work everything out and note which row the day starts on, editing the top row - or, preferably, noting in a patricular cell the cell reference where the day starts - of the workbook calculations, and then everything else changes automatically. This would mean that should I want to compare any two days I would just have to make a note of which rows they start on, edit - or change the paticular cell reference - in the top row, press enter and everything is done for me. I have been wrestling with this for some time, but I do no see a way to do it, and I am beginning to believe it is not possible.
What I have tried to get working so far - with several variations - at the moment is “=if($A3=”RonCREST”,$B3,””), and subsequent cells - going horizontally - have “=if($A3=”RonCREST”,$D3,””),and “=if($A3=”RonCREST”,C3,””). Right, so far so good, and thes are fine as they stand. But for the following rows I have to copy the complete row 3 and paste them into rows 4 to 30 [ I do extra rows just in case we get more shares. ] What has got me floored is how to get all the cells to change when I want to start looking at, say, row 320 and the ones that follow that. I have tried so that I have a column with a number at the top, and the cells below it are each increased by 1. This would mean that I paste the day’s shares onto the spreadsheet, edit the first cell and then all the other cells are changed. But I have tried using “XXX“+”XXX“, “XXX“&”XXX“, as well as concatenation. Nothing seems to work!! Does anyone know what I am doing wrong OR is there no way of doing this, and so I shall just have to continue as I am doing at present with the single sheet and workbook??
Any advice, even negative comments, help or information would be very gratefully received by me.
Many, many thanks in anticipation.
Likes # 0
Posted January 18, 2013 at 6:14PM
Housten: no help, I'm afraid, but we dinosaurs need to stick together. At home, I use Office 2003, as it does everything I want to do. My satisfaction with 2003 is hugely increased by my experience with Office 2010, which I'm forced to use at work, and which I detest with a passion.
Likes # 0
Posted January 19, 2013 at 12:26PM
Ian in Northampton,
Many thanks for that. I retired at Christmas 2007 and we were using Office Professional 2003 at work - at least I think it was that - and as I said office professional is all I want. If to do what I want means getting Excel 2007 or 2010, then I suppose that the answer to my problem is to just keep on doing what I am and what I am comfortable with!!!!!
Likes # 0
Posted January 22, 2013 at 3:04PM
Before going any further I should let you know that I am using Office Professional 2003, on my Windows 7 64Bit Home Premium computer.
As I haven’t had any replies to this I am trying, at least I think I am, a different method! What I would like is to put a number in Col A, say row 6. Then A7, can be that number +1, A8 will be A7+1, and so on for as far as I need down the spreadsheet. Now what I need in Col B is a formula that takes the number in Col A and uses it to direct the spreadsheet, on a separate spreadsheet OR the same spreadsheet, to get information from a specific cell. In other words what I would like is to insert, say, 1925 into A6, so that in B6 the formula would appear as something like “=’Daily Shares!A+”A6”, which would then give the complete reference as “=’Daily Shares’!A1925”. If there is a way this, or something similar, can work I would be very grateful if someone can give me the formula. I accept that there might be a different way, which would be better as it would work and so I am not really worried how it is done – only that I would very much like it to be done!!
Thanking you in anticipation.
Reply to this topic
This thread has been locked.