Excel Problem

  Housten 12:37 05 Jun 2011

Good afternoon,

I read FE’s comment about the site move, I think on Friday, but I tried at least twice yesterday ( Saturday ) before giving up – but having had a good look round it appears to be working! This is the third time I have tried to post this question, so I am hoping this time it will work and that the new site will be better, or it will look like the site is getting ready for the move!

I have a problem in ‘Excel’ which I am hoping some one has the knowledge to deal with and help me out of my predicament. I have some companies who have products that I wish to track, and have called them com0001 upwards. I record the cost of the product on a daily basis each day having its own separate spreadsheet, and then I compare this with another ( arbitrary ) date which I have managed to work out how to do. However my problem is that I want to change the date on a more regular basis. To do this involves quite a lot of editing as each cell has to be amended, and I would like to be able to do it just by changing the date in the cell at the top of the column. What I would like to happen is that each cell in that column takes that date into ‘its own cell reference’ and refers back to the data for that company for that day. I understand ‘absolute cells’ can be made to some things but I do not know how to get such a cell to look at another cell and pull the ‘data' into its own reference. If someone knows how, I would be very, very grateful!

PS Can anyone tell me why the post is repeated, when you type it in?

  lotvic 13:09 05 Jun 2011

you say you had read FE's post about the site move? "We're planning to move the site onto its brand-new servers on Saturday morning"

The 'repeat' in the grey box underneath of box that you type in, is the preview of how your post will look on the actual thread after you have clicked on the Post button. So you can check that the layout is how you want it with line spacing, bold text etc. If you have posted a link you can also check from there that it works ok.

Can't help with the Excel prob, but hopefully someone who knows will come along to help.

  Williep 13:25 05 Jun 2011

Not sure I fully understand your question - do you understand when to use the dollar $ sign in Excel formulae? If you have a long row of formulae which need to relate to a value in one cell (say A1), then you simply enter the formula (let's say A2 is A1B2, A3 is A1B3 etc) as $A1*B2 in cell A2, then drag the cross that appears in the bottom right corner of the cell all the way down the column. That means every cell formula in that column has A1 rather than A2 in row2, A3 in row3 etc, so if you change one value on the spreadsheet (the value in A1), all the other cells will automatically update. Is that what you want or have I missed something?

  Housten 14:28 05 Jun 2011

lotvic Thanks for the explanation, that does simplify things.

Williep I do understand the use of the $ sign. What my problem is that I am bringing information form another spreadsheet and I do not know how to do what I want to do! For instance what I have in one of my spreadsheets is "[20110603]Harry!$E73-C:\\[20110421.xls]Harry!$E75". What this does is to take the particular current value in last Friday's spreadsheet and deduct the value of the same product from another spreadsheet from some time ago - "[20110421.xls]Harry!$E75". In order to do this I have to edit every cell in order to change the day I wish to compare them all with. Now I know I am a pensioner with quite a lot of time but it is extremely time consuming to have to keep doing all the editing. I have tried different ways of trying to do this, and I can - very, very occasionally - get a cell to do it, but not any others. What I would like to be able to do is to be able to get the second date - in the instance above the "[20110421.xls]Harry!$E75" - to be something I can change at the top of a column, that will be reflected automatically all the down the column concerned. If it works I would then be able to - hopefully, easily - to be able replicate it in more than one column. I must admit that before posting I had given up thinking that it was possible and I will have to devise another way entirely of doing it. But if someone knows a method I would love to know it as I have spent many fruitless hours trying to work this out. I assure you any and all help/advice/information will be very gratefully received - even a negative would help as it would stop me wasting my time.

Anyway many, many thanks in anticipation

  Williep 14:38 05 Jun 2011

OK I get it.

Bit of a longshot but INDIRECT might do what you want nicely. Takes a bit of setting up (Google it, lots on the web) but basically it lets you tell formulae exactly what words you want in them (ie the file name from another source). So you could type the file name and path in one cell at the top of the worksheet (e.g. [20110421.xls]Harry) and in each cell going down column A for instance, in cell A2, type this:


in cell A3 type =INDIRECT(A1&"!A3")


It takes a bit of editing and trial and error, you will need to remember the ! (as it's referring to another worksheet) and probably put the cell reference in inverted commas, maybe you even the file path. As it's fiddly to get right, I don't often use this approach.

But what you will end up with (if you're lucky), is that when you type "[20110421.xls]Harry)" without quotes in cell A1, the value of A2 becomes whatever [20110421.xls]Harry)!A2 is, and likewise A3 becomes [20110421.xls]Harry)!A3.

If you then change A1 from "[20110421.xls]Harry)" to "[20110422.xls]Harry)" then the values of A2 and A3 update, assuming there is a file called [20110422.xls]Harry).

Is that what you were after?

  Housten 10:48 06 Jun 2011



Good morning!,

It might be long winded and slow to do, BUT once done it should/will work from then onwards and will make life a lot easier and better. Many, many thanks for all your help!

  Williep 23:46 06 Jun 2011

Glad you found it helpful, yes INDIRECT is one of those rare functions that sometimes is a lifesaver.

Actually to speed up your formula writing, you can resort to a few tricks if you like. For instance say you need to have the following formula in the cells in column A from row 2 downwards (A1 is the name of the other file and the worksheet in which you have a referenced cell):


You could actually type, in cell B2, @INDIRECT(A1&"!A

then in C2 type 3

and in D2 type ")

Copy cells B2 and D2 down as far as you need to go, and drag C2 down likewise so it increments ie C3=3, C4=4 etc.

In E2 type =B2&C2&D2 and drag the formula down

and it will give you, in E2, @INDIRECT(A1&"!A2")

and in E3, @INDIRECT(A1&"!A3")

and in E4, @INDIRECT(A1&"!A4") etc.

Next, select all the cells from E2 down to E5 or wherever you are heading, and copy. Then do edit/paste special/paste values into cells E2 to E5 (or . You'll end up with


and then you simply highlight these cells and do find/replace and replace @ with = and they automatically become formulae. You could use any letter or symbol, not just @, just make sure you choose a letter that isn't in any cells with formulae.

OK don't know if I've explained clearly enough or if it's relevant to your formulae, but if you're smart enough to be able to figure out INDIRECT then you should be able to figure out how you can edit formulae in Excel that contain the text word "INDIRECT" too and save yourself time writing lots of formulae.

Obviously if you change the position of the cells in the referenced file, since INDIRECT uses absolute references (much like VBA macros), the formula will not know to update to the new location (unlike say if you write a formula =A1B1; if you drag A1 to A2, the formula updates itself to =A2B1, but I'm sure you realised that by now...)

  Housten 11:58 07 Jun 2011


I had a go - without very much success yesterday afternoon, so I think you can guess what I will be doing this pm!! I will let you know how I get on! OK, don't hold your breath, though..............

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

Elsewhere on IDG sites

Dell XPS 13 9370 (2018) review

No need to scan sketches into your computer with Moleskine's new smart pen

WWDC history: Apple's product launches since 2005

Comment importer des contacts d’un iPhone à un autre iPhone ?