I have a spreadsheet that the field guys will enter inches. Is there a way to embed a formula in the data entry cell that will automatically change it to feet? My general knowledge is that if you have a cell with a formula the formula is replaced when you enter data.
Thanks!
You are right, as soon as you enter data in a cell, it replaces any previous content.
You have a choice:
- You could use a separate cell containing a formula to convert inches to feet
- You could use a small bit of VBA to convert the value from inches to feet within the changed cell
VBA Example:
If we assume your users will enter inches in range A2:A100, then the following code in the worksheet module will convert inches to feet as soon as data is entered:
Please Login or Register to view this content.
let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source
If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE
Walking the tightrope between genius and eejit...
Jus to make sure I am fully understanding. With that VBA code embedded in the sheet, any data entered in that range will perform the calculation and convert inches to feet? What if I wanted to apply it to multiple ranges? Do I separate with commas inside the quotation marks?
Thanks!
Yes, with that code, anything entered in the specified range will be "changed" as soon as data is entered.
If they're entering text, not values, that changes the code a bit, as does applying it to multiple ranges.
Easiest to make the necessary changes if you attach a sample workbook showing the ranges you want to consider, and what the input text will look like.
I cant really provide a sample of this workbook, my company is overzealous with privacy and security. The ranges would be J and K (for now) row 52-89.
Thanks!
In what format will the inches data will be entered?
Ok, I've mispoken a bit. I want the result in inches and they enter it in feet/inches. So sorry about that, was a little distracted this morning. The way I have it now there are two cells, one for feet and one for inches then a cell that takes those two numbers and converts it all to just inches. They are on a very busy schedule so they want it to be as easy to enter as possible, which is why they dont just figure it in inches to start with.
So you just need a formula in the third cell - e.g. if Feet number is in J52, and Inches number in K52, then in L52:
Formula:
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks