I'm still new to the more advanced methods in excel, so my question seems a little bit simple, but I struggle with it a lot and can't find a solution. So let's say I have an entire table in excel with different prices in each cell. I want to update the whole table with a percent which can be changed from a different cell. For example if I want to increase the prices in all cells in the table by 10% I just type in that designated cell 10% and all the prices are increased with 10%. I tried t he method with Paste Special but I have to paste it again when I want to change the percentage. Any help will be greatly appreciated. Thank you for your time.
4
- Put your percentage in a cell outside the table and refer to that cell to a formula in a column of the table: Please go to gcflearnfree.org/excelformulas/4/print and scroll downt to "To create and copy a formula using absolute references"Doug Glancy– Doug Glancy2016-02-28 15:47:17 +00:00Commented Feb 28, 2016 at 15:47
- Thanks for the answer. But I don't want to create a separate column for the new prices after the increase. I want the current table to be updated from a separated cell which I can change the percentage.Cr1ms0nStraY– Cr1ms0nStraY2016-02-28 16:04:20 +00:00Commented Feb 28, 2016 at 16:04
- On SO it's expected that you will show an example. That would be helpful here. Based on your post and your comment, the example I linked to seem to do exactly what you want, i.e., change the contents of a column based on changing the percentage in a single cell.Doug Glancy– Doug Glancy2016-02-28 16:07:39 +00:00Commented Feb 28, 2016 at 16:07
- Lets say the table is like this: A1=2, A2=4, A3=5, B1=1, B2=7, B3=3. I want to create a separate cell for example C1, where I can input whatever percentage I want, lets say 10%. So when I change C1=10%, the values of A1 to A3 and B1 to B3 to be increased with 10%.Cr1ms0nStraY– Cr1ms0nStraY2016-02-28 20:58:28 +00:00Commented Feb 28, 2016 at 20:58
Add a comment |
2 Answers
Let's say your percentage cell is in G2, and the Price Cells that you want to change are in cells a2, b2, and c2.
Say cells a2 price is 2, b2 price is 4, and c2 price is 7, the formula you'd want to put into the cells is =2*g2 for a2, =4*g2 for b2, and =7*g2 for c2. Now everytime you change the value of your percentage cell in g2, your cells will automatically update.
4 Comments
Cr1ms0nStraY
Yeah that is the thing I figure out from the start. But if you have 100+ prices to update it would take a long time to do it.
micstr
A2 is 2. You put in one formula in A3 as = A2 * $G$2. (You use $ signs to make sure you keep pointing at G2 when you drag formula down - its called absolute references). You then drag down or copy and paste this formula in A3 down to end of your table whether its C3 as Daniels says or C100. So one formula only is needed. It wont take long.
Cr1ms0nStraY
Lets say the table looks like this A1=2, A2=3, A3=4, B1=5, B2=6, B3=7. I want to add 10% to all those values. If I do what you told me to, it would only calculate the value of A1=2+10%=2.2 and while I drag it down, it will show the same value as what I calculated in A1=2.2, A2=2.2, A3=2.2. Same goes for B1, B2 and B3.
Daniel Heriman
If you write your formula in the first two cells, then select both of the cells and drag the formula down the column, it should work. If you only select the one cell, it will keep a1 static throughout, but if you show it that a1 and a2, and drag both of those down, it should do what you want.
If a vba solution is acceptable, you could use the paste special method in the workbook_sheetchange event of ThisWorkbook and use the percentage cell as the target.
Without VBA, you could create a second table that references the original table and the percentage cell in a formula.
