Another Excel programme query

  Housten 13:00 02 Dec 12

Good Afternoon, gentlemen,

This is about my fifth attempt – badly – to explain my problem. I wish to do – in Excel - something that I think is impossible, but which I hope someone will prove me wrong.

What I have is what I call my ‘ALL today’s data’, and this consists of ‘sets’ of data – each ‘set’ consisting of some 50 to 75 rows of 8 columns wide. In order to sort the data I have had to create two other spreadsheets. One has all of the accounts, and each ‘account’ I have made 90 rows long – the extra is a safety margin, and each row of each ‘account’ looks at what I call ‘today’s data’. This is because I only know how to instruct one cell in a spreadsheet to look at a particular cell in another spreadsheet. So when I get any day’s data, I copy it into my ‘ALL today’s data’ – this then keeps a permanent record of it – and then I copy it into ‘today’s data’. Now what I would like to be able to do, is have a cell on the ‘accounts’ spreadsheet into which I put either the row number – say 3470 – or the cell reference – say A3470 – to which the first row of the ‘accounts’ should look at. That after all is quite easy to do as I could just edit the first row but that is where my problem lies. What I wish is that the subsequent rows of the ‘accounts; spreadsheet look at the successive rows of the ‘ALL today’s data’. What I do at the present is that if I wish to see a previous day’s data I copy and paste it from ‘ALL today’s data’ rather than edit ‘today’s data’ then have to copy and paste all the way down, and it means the ‘accounts’ look at the same cells all the time. But it would be very, very convenient to be able to edit/amend/change one cell and everything then flowing from that!

If anyone has any idea if such a thing can be done I would be very, very appreciative of the fact that it can be done but also the formula for doing it.

Many, many thanks in advance.

  mgmcc 19:09 02 Dec 12

I suspect what you're trying to do is quite possible, the problem is in understanding how you've got it all laid out.

Would it help to say that if you want, for example, cell A2 in Sheet 2 to look at B3 in Sheet 1, click A2, Sheet2 and press = then move to B3, Sheet 1 and press Enter.

This formula can then be dragged along rows or down columns, although you may need to remove any $ signs in the formula to make it a relative rather than absolute reference.

  Housten 12:55 03 Dec 12


Many thanks for your reply, but I knew I would explain it badly/incorrectly, because I know what I want and - of course - everyone else is supposed to completely understand my ramblings!!!

What I would like the spreadsheet to be able to do is look at another sheet and pick up certain values from it, but in different places as I require them. BUT the problem that I have is that the cell I first want looked at will change on a daily basis! This is easy enough, I can just edit the cell number; but after that I have to copy it across 7 other columns and then down anything up to 75 rows!! The method I have been using is where I copy the requisite cells on to a 'master spreadsheet' and then copy each particular day's into one spreadsheet and from which the 6 accounts can get the data they need. What I do not know how to do - and what I would really like to be able to do - is that the initial cell in the single spreadsheet - say B6 - looks at a cell reference in another cell - say B4 - and then this reference is amended along the other 7 columns and down the 75 rows. The simplest way I think is to say it is a sort of BASIC programme I want which goes something like : A6=B4:B6=(A6+1):C6=(B6)+1:...H6=(G6)+1:For x=7 to 75:For y=1 to 8:(X,Y)=(((X-1)+1),((Y-1)+1)):NEXT Y,X. [ Yes I know that there isn't a full and proper reference to a spreadsheet, but this is just to show what I would like; not something that will necessarily work! ] This is what I am certain,Excel can not do, but I was hoping that there was someone out there who knows a lot more about Excel than I do and could explain to me - in very, very - simple terms that it could either be done or not done.

I wish to do this because I am just being lazy, and would like to enter a cell reference in B4 and everything thing gets changed to that day. But I am certain that I am 'Shooting for the Moon' and that it can not be accomplished. But I thought I would ask any way, you never know.........

  Zak 14:05 03 Dec 12

Index - Match function should do the trick

You can use this as with any other function in 'another spreadsheet' and combine with IF statements etc. as required.

How to use the INDEX and MATCH worksheet functions with multiple criteria in Excel

  Housten 14:08 10 Dec 12


Many apologies and thanks for your post. I have been having some real problems the last few days and thought that there would not be any more replies. Your re[ly is going to take me time to get my head round it, but it looks just what I want!!!

So many thanks for your very kind help!!

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

What is ransomware and how do I protect my PC from WannaCry?

What I learned from my mentor, Oscar-winning VFX supervisor Phil Tippett

Siri vs Google Assistant