• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel cell reference, updating cells with a new value |General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Excel cell reference, updating cells with a new value |General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Excel cell reference, updating cell…
sp_PrintTopic sp_TopicIcon
Excel cell reference, updating cells with a new value
Avatar
StefiHlm
Member
Members
Level 0
Forum Posts: 15
Member Since:
February 20, 2019
sp_UserOfflineSmall Offline
1
March 7, 2019 - 2:35 am
sp_Permalink sp_Print

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

sp_AnswersTopicSeeAnswer See Answer
Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1529
Member Since:
October 5, 2010
sp_UserOnlineSmall Online
2
March 7, 2019 - 9:27 am
sp_Permalink sp_Print

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

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
3
March 7, 2019 - 10:42 am
sp_Permalink sp_Print

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

Avatar
StefiHlm
Member
Members
Level 0
Forum Posts: 15
Member Since:
February 20, 2019
sp_UserOfflineSmall Offline
4
March 7, 2019 - 5:38 pm
sp_Permalink sp_Print

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

Avatar
StefiHlm
Member
Members
Level 0
Forum Posts: 15
Member Since:
February 20, 2019
sp_UserOfflineSmall Offline
5
March 7, 2019 - 5:44 pm
sp_Permalink sp_Print

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 😀 

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1529
Member Since:
October 5, 2010
sp_UserOnlineSmall Online
6
March 8, 2019 - 11:59 am
sp_Permalink sp_Print

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

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
7
March 8, 2019 - 12:17 pm
sp_Permalink sp_Print

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

sp_AnswersTopicAnswer
Answers Post
Avatar
StefiHlm
Member
Members
Level 0
Forum Posts: 15
Member Since:
February 20, 2019
sp_UserOfflineSmall Offline
8
March 8, 2019 - 6:49 pm
sp_Permalink sp_Print

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

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
9
March 9, 2019 - 9:05 am
sp_Permalink sp_Print

Hi Stefi

No problem. Glad we are able to help.

Sunny

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Mynda Treacy, Philip Treacy, em em, Kathleen Silva
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 873
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 222
Jessica Stewart: 215
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Anthony van Riessen
Erlinda Eloriaga
Abisola Ogundele
MARTYN STERRY
Rahim Lakhani
Ngoc Qui Nguyen
Clement Mansfield
Rose .
Bindu Menon
Baruch Zemer
Forum Stats:
Groups: 3
Forums: 24
Topics: 6353
Posts: 27778

 

Member Stats:
Guest Posters: 49
Members: 32319
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.