This is going to be a bit of a long one as I have a tricky set up going but I'll try and boil the background info down as much as possible while avoiding giving out company info!:
- My workbook automatically builds master lists that vary in number of rows and number of columns which users then input information into, those master lists are then outputted by macros to CSV for various analysis tools and web uploads to use.
- I use a 'template' spreadsheet (for easy modification if an option changes etc) which has all of the potential column headers (that do not change as they are used by the external programs) and one row of example cells for each which have data validation for the type of entry they need, error messages to guide users into entering the right information and conditional formatting to set styles depending on other cells on that row.
- Macros in the workbook create the master list by copying the columns needed from the template along with the example cell into the new sheet then copying that entire example row to each following row for as many times as is needed.
- This means the cell I need will move around in column and row so needs to act as an island in terms of any formula or process used with it.
In the example workbook I've tried to show a couple of these columns with all of their validation etc intact both as the template and I manually copied and jiggled bits around into a 'master list' sheet to show stuff still working once it has been moved.
What I am trying to achieve is partially demoed in the example workbook in the Tariff cost column. Currently I have a formula in there that calculates its value based on what is selected in the two options columns, I would like for the user to also be able to put a number on top of this cell and that be also added into the sum this is because there may be extra costs that need including that are not factored by the master list.
In my searchings generally I've seen the answer is a hard no and lots of recommendations to just use another cell which if push came to shove I could do but it means modifying my output macros to combine the two (as I cannot create another 'column' in the external programs I output csv files to and I need the total in once place) and it means yet another column along side the 40+ columns the users already have to work through (they seem to have difficulty scrolling and they use vlookup a lot which gets a bit tricky when they have to count so many columns) so if it is at all possible I would like to do it with just one cell.
I did however turn up what looks like the perfect answer to my problem in an old post on this forum which I'll quote now for reference:
However when I try to use this it interferes with the data validation I am already doing as it seems to use part of the data validation process to make it work so I was hoping either if the op was still on the forum they might be able to help modify it to work or if another expert is able to decipher it they could give me some pointers as it is a little beyond my skill level.
The code from what I can understand seems to work on any cell in the whole workbook so perhaps its scope could be focused to just any column titled 'Tariff Cost' wherever it ends up and the data validation for the cells in that column could be removed as any input from the user could simply be trapped with a pseudo IF NOT NUMBER THEN 0 trap.
Bookmarks