Formula using value already within the cell

[N_Squared]

I have some part number tables that are organized a bit oddly with the prices showing up below the part number. I need to update these prices but can't think of a good way to do a formula without having to copy and paste the result back into each row of part numbers. Was hoping there would be a way to apply a formula to the cell itself so I don't have to move stuff around. Or maybe there's a way to just target all of the cells formatted for currency and globally apply the price increase?

Thanks for any insight!

1/8	1/8	43834​	37257​	320500​	320520​	320540​		320560​
				$9.32​	$9.32​	$9.32​		$11.57​
1/8	1/8	1/2	2​	320580​	320600​	320620​		320640​
				$9.60​	$9.60​	$9.60​		$12.65​
1/8	1/8	43894​	2-1/2	320660​	320680​	320700​		320720​
				$11.09​	$14.75​	$14.75​		$14.75​

 

[Taul]

Hi,
welcome to the board.
I think it would be easier to re-arrange your data so the prices and part numbers are vertical rather than horizontal, this would make copying the formula a little easier.
Having said that and based on your current layout, we can use a vlookup formula for the price cells so that it reads from the master list on the right.
Hope this helps
Paul.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	1/8	1/8	43834	37257	320500	320520	320540	320560			Part #	Price
2					$9.32	$9.32	$9.32	$11.57			320500	$9.32
3	1/8	1/8	1/2	2	320580	320600	320620	320640			320520	$9.32
4					$9.60	$9.60	$9.60	$12.65			320540	$9.32
5	1/8	1/8	43894	2-1/2	320660	320680	320700	320720			320560	$11.57
6					$11.09	$14.75	$14.75	$14.75			320580	$9.60
7											320600	$9.60
8											320620	$9.60
9											320640	$12.65
10											320660	$11.09
11											320680	$14.75
12											320700	$14.75
13											320720	$14.75
Sheet1

Cell Formulas
Range	Formula
E6:H6,E4:H4,E2:H2	E2	=VLOOKUP(E1,$K$2:$L$13,2,TRUE)

 

[N_Squared]

I wish I could reorganize! But that isn't an option at the moment, these tables get copied into a catalog which has been built in inDesign.

I'll see if I can get vlookup working, at the very least looks like it would make it easier to update in the future.

 

[N_Squared]

Ok, so I went down a bit of a rabbit hole here but I figured out how to make macros. It's FABULOUS and I can totally do what I'm trying to do using this. But now that I know it's possible I'd like to make it slightly smarter. How would I go about asking this to only apply my calculation for cells formatted as currency within my selection? That way I can just select the whole table, click a button and we're in business.

This is what I have currently:

Sub IncreasePrice()
For Each cell In Selection
If cell <> "" Then
cell.Value = (cell.Value) * 1.42
End If
Next cell
End Sub

 

[Taul]

Hi,
Give this a try

Forum-vLookup-2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	1/8	1/8	43834	37257	320520	320520	320540	320560		Multiplier Rate =	1.42
2					$13.23	$13.23	$13.23	$16.43
3	1/8	1/8	1/2	2	320580	320600	320620	320640
4					$13.63	$13.63	$13.63	$17.96
5	1/8	1/8	43894	2-1/2	320660	320680	320700	320720
6					$15.75	$20.95	$20.95	$20.95
7
Test2

Sub IncreasePrice2()
Dim c As Range
Dim Mrate As Double
'put the multiplier rate (1.42) in cell K1 or whichever cell you choose
'and change the worksheet tab name to the name you are using.
Mrate = Worksheets("Test2").Range("K1").Value

    For Each c In Selection
        If Len(c.Value) > 0 And Left(c.Text, 1) = "$" Then
            c.Value = c.Value * Mrate
        End If
    Next c
End Sub

 

[N_Squared]

Hi,
Give this a try

Forum-vLookup-2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	1/8	1/8	43834	37257	320520	320520	320540	320560		Multiplier Rate =	1.42
2					$13.23	$13.23	$13.23	$16.43
3	1/8	1/8	1/2	2	320580	320600	320620	320640
4					$13.63	$13.63	$13.63	$17.96
5	1/8	1/8	43894	2-1/2	320660	320680	320700	320720
6					$15.75	$20.95	$20.95	$20.95
7
Test2

Sub IncreasePrice2()
Dim c As Range
Dim Mrate As Double
'put the multiplier rate (1.42) in cell K1 or whichever cell you choose
'and change the worksheet tab name to the name you are using.
Mrate = Worksheets("Test2").Range("K1").Value

    For Each c In Selection
        If Len(c.Value) > 0 And Left(c.Text, 1) = "$" Then
            c.Value = c.Value * Mrate
        End If
    Next c
End Sub

Brilliant! This is great, now I don't have to go back in to change my multiplier between part tables. Thank you so much.