How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask The-PC-Guy Your Own Question

The-PC-Guy
The-PC-Guy, Computer Manager
Category: Microsoft Office
Satisfied Customers: 1848
Experience:  20 years experience providing remote computer support
62934938
Type Your Microsoft Office Question Here...
The-PC-Guy is online now
A new question is answered every 9 seconds

I am using the following formula:

Customer Question

Hi, I am using the following formula:
INDEX(BPSPull!$J$7:$AJ$5235,MATCH($BA$3&$BD$3&VLOOKUP($C$13,$BK$19:$BL$23,2)&$C$12&$AY$2&$AZ$2,BPSPull!$A$7:$A$5235,0),MATCH(LEFT($D$13,2),BPSPull!$J$2:$AL$2,0))
Is there is an easy way for me to use a variable for the worksheet name portion "BPSPull" to change it to other text. Like having a vlookup in a cell and if it change to "CPSPull" then the index will adjust to go to that new worksheet.
Thanks,
Otto
Submitted: 4 months ago.
Category: Microsoft Office
Expert:  The-PC-Guy replied 4 months ago.

Otto,

try something like

=INDIRECT(ADDRESS(2,2,4,1,G9)&":"&ADDRESS(4,2,4,1))

so what the indirect function does is retrieve the value of a cell or range to plug into a formula.

the address formula converts a row # and column # into an address, so for example if you use row 2 column 2, that would be B2.

so in your formula, lets say you had the text name of the sheet in cell A1 of the sheet you have your formula in, it would look like this

BPSPull!$J$7:$AJ$5235,

=INDIRECT(ADDRESS(7,10,4,1,A1)&":"&ADDRESS(5235,36,4,1))

7 is for row 7, 10 is for the 10th column or J, 5235, is for row 5235, 36 is for 36th column or AJ.

4,1 means make it absolute and use A1 reference.

and of course A1 in the first address formula is the cell that contains your sheet name.

Of course you would have to apply this type of formula anywhere you had a sheet name and adjust for row and column # to whatever range you need.

the easier method though would be to use a macro to substitute the values directly into the formula.

---------------------------------------------------------------------------------------------------------------

let me know if you have any questions, problems, or concerns

PLEASE DON'T FORGET TO RATE SO I AM PAID FOR MY TIME

IT WILL NOT COST ANYTHING ADDITIONAL BEYOND THE VALUE OF YOUR QUESTION

TO RATE, CLICK THE STARS AT THE TOP OF YOUR SCREEN

Do not rate negatively, instead continue the conversation with me so I can address any of your concerns

---------------------------------------------------------------------------------------------------------------

if you have any questions in the future you can visit my profile

http://www.justanswer.com/computer/expert-1expert-2rulethemall/

and ask a question right in my box,

also you can put "PC Guy only" in your question title if you want to get to me.

Customer: replied 4 months ago.
Hi,I am not following your answer. I only want to change the CPSPull! portion to other text like CPSPull! to pull from other worksheet. The rest of the formula I will like to keep as it is. The thing is that it need to work within the Index and Match formulas that are in the formula.
I can use an "If" and say if A1 = BPSPull!, "BPSPull!, if not have copy the same formula and substitute the text for CPSPull!. I do not want to do this because the formula will become very long.
Expert:  The-PC-Guy replied 4 months ago.

the only way to do it with a formula is the way I sugested, you would need to change it anywhere you refer to CPSPull! within a formula.

Otherwise you will need a macro

Expert:  The-PC-Guy replied 4 months ago.

so what do you want to do?