Excel help ! referencing another spreadsheet based on value of a cell

[mankymummymoo]

Hi, I am trying to compose a formula referencing a specific row and cell number in another worksheet, and for the row number to change according to a value I put in my current spreadsheet.

eg.

In worksheet titled "buttons" i want to put a number (eg 345) in cell A1 that will be picked up by my formula in cell A2 that references another sheet.

my formula at the mo in cell A2 is this (but I have to physically change the row number in the formula each time)....

='C:\CompanyName\WorkJob\Kitchen\Diary[workdiary.xls]Job List'!$G$345

The reason I dont want to have to change the formula each time I want to reference a different row number in the workdiary.xls spreadsheet is because I will also need to put similar formulas in other cells, eg. in cell A3 my formula is...

='C:\CompanyName\WorkJob\Kitchen\Diary[workdiary.xls]Job List'!$P$345

So I'm hoping that by typing a value in my buttons spreasheet it will change the rows referenced in several places on my worksheet.

Sorry for the long winded explanation and if anyone is still reading I would be incredibly grateful for any help.

I did try INDIRECT and got a #REF error each time. The worksheet I am referencing WORKDIARY.XLS can be open when I do this.

[Metal]

What was the formula you tried with INDIRECT?

I have just tried what you described in Excel 2007 and it worked fine with:

=INDIRECT("B"&'C:\Users\Name\Desktop[Book1.xls]Sheet1'!$B$2)

If you get a REF error you might have missed the &

It seems to only update when both are open, but since I never use this function there might be some option you can change to make it always check for updates.

[mankymummymoo]

oh thank you for replying. i will try that.

but would you mind explaining what each reference you have referred to means... ie. the "B"&'C and $B$2 refer to?

im sorry to be so dense but would it be possible to tell me what I need to type in exactly as it relates to my file reference?

[Metal]

OK, not sure if I understood you correctly the first time, so this is what I think you are trying to do (let me know if I've got it wrong):

You have a sheet called buttons
You have another sheet called workdiary

You have cells in buttons which tell you numbers from cells in row 345 of workdiary.
You want to change the formula so that it tells you numbers in whatever row number you put in cell A1.

If I've understood correctly, you should be able to do it like this:

=INDIRECT("'[workdiary.xls]Job List'!$G$"&A1)

(note that at the start, it is one " followed by a ' and not ''')

If you have the number 345 in cell A1, then this formula should give you the number which is in cell G345 of the Job List sheet of workdiary.xls

(the formula is slightly different from my first post because I thought you were aiming for something else at first)

Let me know if it doesn't work; if I still didn't understand the problem; or if you need more help in understanding what each part of the formula is for.

[mankymummymoo]

thank you so much for taking the time to reply. I will try this and let you know how I get on.

Thank you!

[mranchovy]

Metal's solution should work but I'd go for this formula instead because it will not break if you save the file in a different place etc. - the link should just update nicely:

=INDEX('[workdiary.xls]Job List'!$G:$G$,A1)

[Metal]

The (second) formula I used doesn't break for me when I move the file... You just need to have them both open.

I tried to see the difference yours makes but it has an error.