Results 1 to 15 of 38

20021102, 06:03 #1
 Join Date
 Jan 2001
 Location
 Illinois
 Posts
 552
 Thanks
 0
 Thanked 1 Time in 1 Post
Formula to reference a cell based on another cell (Excel xp)
I have a workbook with 2 sheets. Sheet2 is linked to Sheet1.
The formula in b3 on sheet2 is =sheet1!B199
I want to write a formula on sheet2 in cell b12 that will give me the value of 1 row down whatever the reference is in cell b3 of sheet2. In other words, I want b12 to have a formula that will give me sheet1!B200's value. I don't want to have to go to sheet1 and click on B200 and paste special back to sheet2. There are a lot of these and i'm hoping to use a formula that i can copy throughout sheet2.
Thank you for the help.

20021102, 16:06 #2
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula to reference a cell based on another cell (Excel xp)
I don't know of any way to do that directly. The User Defined Function below will do it. Please note that the function does not do any error checking to make sure that what is in the cell pass is really a formula like =sheet1!b199.
<pre>Public Function OneDown(oCell As Range) As Variant
Dim strSName As String, strCAdd As String
strSName = Mid(oCell.Formula, 2, InStr(oCell.Formula, "!")  2)
strCAdd = Right(oCell.Formula, Len(oCell.Formula)  InStr(oCell.Formula, "!"))
OneDown = Worksheets(strSName).Range(strCAdd).Offset(1, 0).Value
End Function
</pre>
Legare Coleman

20021102, 16:29 #3
 Join Date
 Dec 2000
 Location
 New Hampshire, USA
 Posts
 3,386
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula to reference a cell based on another cell (Excel xp)
LC, do you now have Excel 2002?

20021102, 19:42 #4
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula to reference a cell based on another cell (Excel xp)
No, I will not install any product that includes the Registration/Activation Wizard.
Legare Coleman

20021104, 01:05 #5
 Join Date
 Jan 2001
 Location
 Illinois
 Posts
 552
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Formula to reference a cell based on another cell (Excel xp)
This works great.
I need another one that will look on the other sheet and if the value (onedown) equals an "A", i want to change it to E, and if it's a "B" i want to change it to an AF.
The problem is the cell on the other sheet may have a value of AB which means on the active sheet it will need to read E,AF (i need commas between the letters). There are 10 other letters that have to be substituted but hopefully i can change them if you can help me with this part. Thank you very much.

20021104, 01:09 #6
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,216
 Thanks
 2
 Thanked 465 Times in 382 Posts
Re: Formula to reference a cell based on another cell (Excel xp)
OK, try this:
1. In your source worksheet (Sheet1), insert a new row at row 1.
2. Open a new workbook.
3. Cut (not copy) your target worksheet (Sheet2) and paste it into the new workbook
4. Close the original workbook without saving.
5. Reopen the original workbook.
6. Copy (not cut) the new worksheet out of the new workbook and paste it back over the original target worksheet (Sheet2).
All of the links from the source worksheet to the target worksheet will now be offset by one row down from their original positions. Links going the other way won't be affected.
CheersCheers,
Paul Edstein
[MS MVP  Word]

20021104, 01:33 #7
 Join Date
 Jan 2001
 Location
 Illinois
 Posts
 552
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Formula to reference a cell based on another cell (Excel xp)
Sorry, but i think i need the function from Legare altered. Something like
sheet1, A6 has a value of AB
sheet2, G15, has a formula using the onedown function. It looks at sheet2,G13's call to sheet1!A5, goes down 1 row to A6 which has a value of AB in it.
i need the formula on sheet2, cell G15 to make the A an E, and the B an AF with a comma between. The value of G15 will now be E,AF. Thank you

20021104, 01:46 #8
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,216
 Thanks
 2
 Thanked 465 Times in 382 Posts
Re: Formula to reference a cell based on another cell (Excel xp)
Sorry about the confusion there  my reply was to your original post. Your later post wasn't there when I started replying.
I'll have a think about the latter issue  at first blush it seem like you need a formula with a set of IF tests. Have you a sample worksheet we could look at?
CheersCheers,
Paul Edstein
[MS MVP  Word]

20021104, 02:12 #9
 Join Date
 Jan 2001
 Location
 Illinois
 Posts
 552
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Formula to reference a cell based on another cell (Excel xp)
Thank you. here is an example

20021104, 03:18 #10
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,216
 Thanks
 2
 Thanked 465 Times in 382 Posts
Re: Formula to reference a cell based on another cell (Excel xp)
Hi again,
Check out the attached  I think it'll do what you want with cells containing numbers or up to 3 of the specified characters. To achieve this, I've used LOOKUP to crossreference the returned characters with the required ones. Note too how I've used a redundant NOW function to create a volatile formula  the UDF doesn't recalculate without it.
Cheers
PS: Ignore my original reply  it was off on a completely different tangent.Cheers,
Paul Edstein
[MS MVP  Word]

20021104, 05:50 #11
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula to reference a cell based on another cell (Excel xp)
A function that is called from a formula on a worksheet can not change a cell on the worksheet, it can only return a value to the formula that called it. You will have to find another way to do this.
Legare Coleman

20021104, 19:52 #12
 Join Date
 Jan 2001
 Location
 Illinois
 Posts
 552
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Formula to reference a cell based on another cell (Excel xp)
Thank you so much. Is there anyway for me to put this in a new function or in a sub procedure so that the worksheet will update via a macro? I'm just afraid that the "list" of substitutions will change and i will have to change all the formulaes. any thoughts are greatly appreciated.

20021104, 22:05 #13
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,216
 Thanks
 2
 Thanked 465 Times in 382 Posts
Re: Formula to reference a cell based on another cell (Excel xp)
Hi jha,
Provided you define a large enough range in your formula for the lookup table and keep the data in its left column sorted in ascending order, you can add/remove as many substitutions as you like without having to change the formula. In the example I gave you, I defined the lookup table as occupying $A$1:$A$6 for the left column and $B$1:$B$6 for the right column. If you wanted to allow for, say, 20 possible sustitutions, you would change the lookup from $A$1:$A$6,$B$1:$B$6 to $A$1:$A$20,$B$1:$B$20. You could use all of columns A & B ($A:$A,$B:$[img]/forums/images/smilies/cool.gif[/img]. The lookup table could even be moved to another worksheet.
The only limitation, as I previously said, is that my solution only substitutes nonnumeric results for up to three characters  though it could readily be extended to cater for up to 11, after which Excel can no longer accomodate the formula's length.
If need be, you could also use different lookup tables for different parts of your worksheet.
CheersCheers,
Paul Edstein
[MS MVP  Word]

20021105, 00:55 #14
 Join Date
 Jan 2001
 Location
 Illinois
 Posts
 552
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Formula to reference a cell based on another cell (Excel xp)
ok, thank you. I put the lookup list on a separate worksheet and it works. It gives me a #N/A when there is no value in one down. Can I add something to adjust for that?

20021105, 01:14 #15
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,216
 Thanks
 2
 Thanked 465 Times in 382 Posts
Re: Formula to reference a cell based on another cell (Excel xp)
That's easy. Change:
IF(ISNUMBER
at the start of the formula to:
IF(ISNONTEXT
to return 0 if the source cell is 0 or blank.
It's a bit more complicated if you want a 'blank' result if the source is blank. Let me know if that's what you need.
CheersCheers,
Paul Edstein
[MS MVP  Word]