Yet another Excel problem!!

  Housten 18 Jan 13

Good Afternoon,

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.

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.

  Housten 19 Jan 13

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!!!!!

Dinosaurs forever!!!!!!

  Housten 22 Jan 13

Good Afternoon,

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.


This thread is now locked and can not be replied to.

Should I upgrade to Windows 10? 8 reasons why you should upgrade to Windows 10... and 2 why you…

We are being sold the ability to spend money we don't have. And we love it

Wacom Cintiq 27QHD review

How to use Apple Music in the UK: Complete guide to Apple Music's features

We use cookies to provide you with a better experience. If you continue to use this site, we'll assume you're happy with this. Alternatively, click here to find out how to manage these cookies

hide cookie message