• 100% Satisfaction Guarantee
Richard, Software Engineer
Category: Software
Satisfied Customers: 36526
Experience:  Software Systems Specialist.
32989067
Richard is online now

# I want to change formula in row within excel. The formula

### Resolved Question:

Hi
I want to change formula in row within excel. The formula is =Doors!\$H\$55*2. I want to change the letter from H to J and then change again when required. Using replace all changes all the H in entire spreadsheet to J. At the moment I am going down the row changing in cell individually but is there a way I can change the letter in the entire row without changing the letter anywhere else in spreadsheet
Regards
Terry
Submitted: 1 year ago.
Category: Software
Expert:  Richard replied 1 year ago.
Hi there and welcomeyou can use the indirect function for this.So you put in for example in cell A1 the value of HThen you use the function=indirect(Doors!\$ & A1 & "\$55)*2that way when you change H to G in A1, the formula will use G55 instead If you have any problems, or would like any additional information or assistance, please do not hesitate to let me know. Else if you can take a second to rate my service by clicking one of the stars at the top of the screen then submit, that would be greatly appreciated. Thank you
Customer: replied 1 year ago.

Apologies I do not think I was clear in my question. The number changes throughout the row, so could be =Doors!\$H\$55*2 or =Doors!\$H\$55 or =Doors!\$H\$65 etc. Using the indirect function will it only change the letter ie the H to a J as example

Expert:  Richard replied 1 year ago.
ok, then you use the indirect for the number instead, so it would be=INDIRECT("Doors!H" & A1)*2Indirect is just used to reference a cell as part of the formula.
Customer: replied 1 year ago.

Sorry not getting this.If my formula is =Doors!\$H\$55 throughout the row with the column number changing from 10 to 324 to pick up the various values but when I use the formula in different spreadsheets the row letter changes. I have never used Indirect function or place & in formula. Do I leave original formula in and somehow use indirect function or is it new formula of =indirect("doors!\$H\$"&55)

H is column and various rows are used, dollar sign as LETTER is constant down row.Sorry if I am not making sense burt as I said not understanding

Expert:  Richard replied 1 year ago.
Sorry, I do not understand now what you are trying to explain.Initially you askedI want to change formula in row within excel. The formula is =Doors!\$H\$55*2. I want to change the letter from H to J and then change again when requiredThen you indicated The number changes throughout the row, so could be =Doors!\$H\$55*2 or =Doors!\$H\$55 or =Doors!\$H\$65 etc. But this last explanation I do not understand at all. Can you try to please provide me a very clear description of what it is you require Terry?Thank you
Expert:  Richard replied 1 year ago.
.
Customer: replied 1 year ago.

Ok, going down the row my formulas are as follows,

=Doors!\$H\$55*2

=Doors!\$H\$58

=Doors!\$H\$55*2

=Doors!\$H\$34*2

=Doors!\$H\$56

The formula pulls the information from different sheet called DOORS and I have columns ranging from C to Y with different prices in each column. I then have pages with the names of doors example being Gresham and Gresham requires the pricing from column H. Another page is called Boston and Boston requires the pricing from column G. So the only change in the formula as I move along the pages is the letter. So as I move from the Gresham page to the Boston page I want to change the letter from H to G but in the column only not everywhere in sheet as would happen if I select the replace function.

I hope this helps.

Expert:  Richard replied 1 year ago.
you would then need to manually change it through you have to do this to put in the sheet name as well