for an ad-free Fetcheveryone experience!

SpreadEveryone: The Fetchland Excel wire

1 lurker | 102 watchers

um Dec 2021 9:34am, 2 Dec 2021 5,483 posts	Quote Pin um Quick fix? Have the macro unlock the cells pre update and lock after completion?
Heinzster Dec 2021 2:21pm, 2 Dec 2021 1,968 posts	Quote Pin Heinzster Oh, that is clever, thank you.
NDWDave Dec 2021 11:59am, 29 Dec 2021 16,207 posts	Quote Pin NDWDave I have a spreadsheet where one of the named ranges now has a $ in front of it. It was WEIGHT in the formula and is now $WEIGHT Is this a new thing and should I be worried/excited?
NDWDave Dec 2021 12:28pm, 29 Dec 2021 16,208 posts	Quote Pin NDWDave Turns out if you copy a formula with a named range, it will more it in the same way a formula which hasn’t got the $s…. Unless you put a $ in front
GregP Dec 2021 12:31pm, 29 Dec 2021 131,901 posts	Quote Pin GregP That would have been my guess. Cool. Possibly? Although a named range that moves around leaves us uncomfortably in "constants aren't, variables don't" territory.
NDWDave Dec 2021 12:41pm, 29 Dec 2021 16,209 posts	Quote Pin NDWDave No idea Greppers. I’m struggling to replicate it so it will probably be one of those things that pops up randomly screwing up a regular spreadsheet Or some smartar$e uses it to make a regular spreadsheet impossible to follow
icenutter Feb 2022 12:23pm, 2 Feb 2022 540 posts	Quote Pin icenutter Is there a way to declare a global variable? I'm updating a costing spreadsheet for tenders. One of the things which you have to bodge with the current version is having different margins for different things. So for example you might want more margin for something that you design, then manufacture. But less margin for something standard that you just buy in. So I have hit on the idea of having three values set somewhere; lets say 0.8, 0.9 and 1. If you wanted 20% gross margin you divide by 0.8, 10% 0.9 and no margin 1. But due to the way it the sheet is constructed, I don't want to have to refer to the cell where the value is. Instead I want to put something like ST (for Standard margin), BI (bought in), and FI (for Free issue). So is 666 was the cost for something in cell A1, the sell price would be =A1/ST. I have done a bit of googling, but can't find the right search term to work out what my solution is. Can anyone help?
larkim Feb 2022 12:30pm, 2 Feb 2022 16,875 posts	Quote Pin larkim Named ranges?
3M (aka MarkyMarkMark) Feb 2022 12:31pm, 2 Feb 2022 17,328 posts	Quote Pin 3M (aka MarkyMarkMark) With just one cell in the Range.....
Bowman 🇸🇪 Feb 2022 12:32pm, 2 Feb 2022 915 posts	Quote Pin Bowman 🇸🇪 Maybe a long shot, but create the formula you need and then name that cell “ST” it doesn’t need to be named J7 or what not it can be what you want. Then you can do A1/ST