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
Contact Forum Editor

Send an email to our Forum Editor:


PLEASE NOTE: Your name is used only to let the Forum Editor know who sent the message. Both your name and email address will not be used for any other purpose.

Tech Helproom


It's free to register, to post a question or to start / join a discussion


 

Yet another Excel problem!!


Housten

Likes # 0

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.

Like this post
Ian in Northampton

Likes # 0

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.

Like this post
Housten

Likes # 0

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

Like this post
Housten

Likes # 0

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.

Like this post

Reply to this topic

This thread has been locked.



IDG UK Sites

Best Black Friday 2014 tech deals: Get bargains on smartphones, tablets, laptops and more

IDG UK Sites

What the Internet of Things will look like in 2015: homes will get smarter, people might get fitter

IDG UK Sites

See how Trunk's animated ad helped Ade Edmondson plug The Car Buying Service

IDG UK Sites

Yosemite tips: Complete Guide to OS X Yosemite