Log In
Register
Home
February 20, 2019
Offline
Hello, i attached an excel file so that i could explain better in what i would need help on. Column B (B3:B20), takes the value that is entered in cell E1. Let's say that i have a pipe, and the diameter changes in different lengths, so i would like if i type in cell E1 another diameter (for example 0,0038 m), to change only in some cells, let's say B8:B12, and not automatically update the whole column (B3:B20) with the new value? Is there a function i could use for that? would prefer a function, but if not, a macro would be helpful too. Since i create a spreadsheet it would be easier to enter a value only in one cell rather than having to manually change it in each cell of the B column. Thank you in advance 🙂
Greetings,
Stefi
See Answer
October 5, 2010
Online
Hi Stefi,
If you just want to change the values in some of the ColB cells then just change the formula in those cells.
If you only want cells B8:B12 to be the same value as E1 then in B8:B12 you have =E1
Am I missing something?
regards
Phil
VIP
Trusted Members
June 25, 2016
Offline
Hi Stefi
It sounds like you need an IF() function.
Without seeing your expected results (with different scenarios) we can only make a guess.
Sunny
February 20, 2019
Offline
Hello Philip and Sunny,
Thank you for your replies. I have attached a new version of the excel. I have not used a formula in B column, that's why an IF function will probably work. Since i'm creating a spreadsheet, and i'm trying to make it user friendly. Let's say i have a pipe, and i want to calculate the velocity of the fluid that is inside the pipe (i haven't inserted the velocity column but i will once i will make this work), the velocity formula needs the diameter so that it can be calculated. So the user of this spreadsheet can enter the total pipe length in cell B2, and can choose the pipe segment length (since the calculations have to be carried out in segments in this case). The column A with the lengths will then automatically be updated depending on the values you have chosen. What i would like is that i would be able to type in B4 the diameter, since it can change for example every 20 meters, and i wouldn't want users to manually change each diameter in every cell of the B column , because the pipe length can be way too long. So is there a way i can somehow use B4 as a reference cell so that i could type the next diameter without changing the previous cells too? Because if i will now type in B4 0,0037 instead of 0,0057, it will change all the values (B7:B26) to that new value.
I have maybe explained it better in the new excel i just attached.
Thank you in advance 🙂
Greetings,
Stefi
February 20, 2019
Offline
I just noticed that in my first post, i was referring to an E1 cell that wasn't used in my attached excel file. Sorry for that, the second one should hopefully be more clear 😀
October 5, 2010
Online
Hi Stefi,
You can't have a formula that takes a value from a cell, and then ignores when that value changes.
What I've done is changed the formula in ColB so that it looks for a value in the adjacent cell in ColC. Rather than enter the diameter in B4, enter it into cells in ColC.
Please see attached workbook.
regards
Phil
VIP
Trusted Members
June 25, 2016
Offline
Hi Stefi
Since you need to enter the diameter many times, you might as well enter them into different cells. My solution differ from Philip's is that you can define at which length the diameter changes. You can have as many columns as you want and maybe they can be auto calculated (if we can determine your logic where the cut-off is)
Hope this helps.
Sunny
Answers Post
February 20, 2019
Offline
Hi Philip and Sunny,
Thank you so much for your replies and taking the time to help me, both answers are great, i used Sunny's version though, since i have other columns on my spreadsheet, next to the diameter column, that's why i can't add anything next to it.
Thanks to both of you, for saving my day 😀
Greetings,
Stefi
1 Guest(s)
