Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 69
  • Last Modified:

Dragging formula in Excel

Hi guys, I need to create a formula that will allow me to drag down and change the #s in the cell but not the word. Please see example attached.
Excel-forumal-ex.PNG
0
vmagan
Asked:
vmagan
  • 6
  • 5
  • 2
1 Solution
 
Saurabh Singh TeotiaCommented:
Assuming you type the numbers from cell a1 then you can use this formula in B2

=A1 &"-vmtemp"

Also if you want to create a random number then you can you can use..

=Rand()&"-vmtemp"

You can also use row number as your reference point which is:-

=ROW()&"-vmtemp"

Saurabh..
0
 
vmaganAuthor Commented:
=A1 &"-vmtemp" didn't work. It didnt even recognize the cell as a formula.
0
 
Saurabh Singh TeotiaCommented:
Right click on that cell-->Format cell--> and change the formatting to general from text and then reapply the same formula...

Saurabh
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
vmaganAuthor Commented:
Here is the output of that. attached.
Excel-forumal-2-ex.PNG
0
 
Saurabh Singh TeotiaCommented:
Yeah that looked fine to me..Did you change the formatting of the cell to general like i told u...alternatively ctrl+1 as well to change the format for the cell as well..

If you are still not able to do so..can you post your workbook so that i can help you with the same..

Saurabh...
0
 
vmaganAuthor Commented:
I changed the format to General but I need it to show like this:


964-vmtemp
965-vmtemp
966-vmtemp

etc
0
 
Saurabh Singh TeotiaCommented:
yeah if you drag the formula it will get update...with whatever you write in the cell from where you are linking...

Enclosed is the file for your reference...

Saurabh...
Experts.xlsx
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
There is no formula able to do that just by dragging. However, you can extract the prior value and add one. This can be used in A2 and below.
=INT(LEFT(A1, FIND("-", A1)-1))+1 & "-vmtemp"

Open in new window

0
 
vmaganAuthor Commented:
got ya. But I only want to be able to right the 1st cell in A1. Then drag down all the way for like 300 cells. I only want to right 964-vmtemp once then drag that down and have the #s change
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
We could also use the row number, add an offset, and build a number that way ...
0
 
Saurabh Singh TeotiaCommented:
I Thought so...check the solution in E Column..it exactly does that...

Saurabh...
0
 
vmaganAuthor Commented:
trying this now.
0
 
vmaganAuthor Commented:
The original formula you gave me worked. I created another row and added the #s there than hid the row and applied the formula =A1&"-vmtemp" and dragged it down.

thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 6
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now