syntax of excel formula withindirect

    I have an excel formula like this =(b1Scores!BD3)
    but I want the digit 3 to be taken from a cell "B3" (that my VBA will write)
    I think I need to use the INDIRECT but I can't get the syntax correct. This is what I have
    =(b1Scores!BD&INDIRECT("B3"))
    It shows this error #NAME?

    You could use:


    =INDEX(b1Scores!BD:BD,B3)

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Thanks for trying, Rory but this does not work and the error is #VALUE!.

    B3 is a cell on this sheet and all the rest is from a different sheet that I am trying to copy to this sheet.

    B3 specifies what row to get data from other sheet. And I want my VBA to write to that B3 cell each time row changes.

    The only way I can think that you would get that error is if you have something in B3 that is not a number.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Actually, you are right. I have my number in B2 and not B3 so when I changed that it works!!!

    Thank-you so much for your help.

    I don't quite understand how Index knows to look at this sheet but maybe I will figure that out eventually.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login