Hi, I'm trying to automate the task of creating new formulas based on a 'master' formula.
This is my master formula; (Starting at Lamp_2 as Lamp_1 is quite different. Once there are 2 or more lamps the pattern in the formula is very similar)
The difference between them is what I need to make automated as I need this pattern to carry on until I get up to Lamp_55!!!
What's happening at the minute is, because it was getting so complicated I had to name all the cells, which (correct me if I'm wrong) makes them an absolute reference? So when I try and drag the cell down, nothing is updating. I'm having to copy and paste the previous formula in the next cell, and go in and edit all the relevant lamp numbers, add the additional line of code at the end, and add an additional bracket on the end of the formula.
Because your formula is constructed entirely of named ranges it is impossible to see which references should be relative, and which (if any) should be absolute.
Could you post a sample workbook that includes the relevant data for the sample formula, and the formula without using named ranges?
I think it's basically a case of telling the formula to add in a new last line every time I drag and copy the cell down, but also updating the relative cell reference number with some sort of counter? The counter would have to count up in twos, 11, 13, 15 etc. It would also need to know to add in an additional bracket at the end to account for the additional line of formula.
The SUMPRODUCT part is where it all happens, each time you copy the formula down 1 row, the range used in the formula increases by 2 rows, different sections of that formula then work with either the odd, or even numbered rows.
Everything above SUMPRODUCT is the common first park of your formula, rewritten so that the cells referred to in column BJ move down 2 rows each time you copy the formula down 1.
I've copied your formula in and tried to copy it down the cells, but it's either me not using it correctly or maybe it needs a bit of tweaking?
If you look at the two attached jpegs, you can see that my labour intensive version looks at all the cells above it and returns the result it should, i.e. no ERROR warning. When I paste yours in it falls on certain cells and doesn't look at all the cells above.
The first formula was based on increasing 2 rows each time you copy down 1 row, without taking merged cells into consideration, i.e. a 20 row table of data, being reported in a 10 row table of results. In the format you have, although you only have 10 cells with a formula, it's still 20 rows so the formula counts them incorrectly.
Because of this, the first formula that incorrectly returned "ERROR" was not actually looking at the data in the same row as the formula, but looking at data further down the sheet. It's not that it wasn't looking at the rows above, but rather that it was looking at them and some rows below as well
The formula in the merged cell adjacent to lamp2x and lamp2y was processing the data for lamps 1 and 2 (x and y), if the cells were not merged, the top one would have looked at lamp 1 (x and z) the bottom one at lamp 2 (x and y).
Moving down the column, the formula next to lamp3x and lamp3y was actually looking at the data for lamps 3 and 4. By the time you get to the first 'ERROR', following this pattern, you will notice that the formula will, theoretically be looking at lamps 11 and 12, which, as the cells are empty, don't fall within the tolerances.
If you created a new results table elsewhere on the sheet, with lamp1 to lamp10 listed down in single cells (1 row per lamp), then copy my original formula next to lamp 2 and down, then you should find the results are as expected.
Working with your existing layout and merged cells, this less complex version should give the expected results.
Enter into the merged cell BK12:BK13 and copy down as needed.
As your merged cells occupy 2 rows, the formula ranges will increase by 2 anyway as you copy down, so there is no need for the extra functions used for that purpose in the first formula (INDEX(BJ:BJ,ROWS(BJ$13:BJ13)-1)*2+13), etc.
Effectively, this is the same as your original lamp2 formula, with sumproduct replacing the final AND(). The difference being that the sumproduct part expands to include multiple rows, so you don't need to keep adding extra bits.
The first part of your formula had a lot of bits repeated that didn't need to be, so I've just trimmed it down a little.
You're an absolute genius! I thought that due to the niche nature of this request I'd never find the solution I wanted. Your formula works a treat so thanks very much!
I actually noticed a mistake on my part made during the switch back to non named cells, so here's the final formula below. Only one cell reference change in the AND function.
I'd actually read on the forum about the dangers of merged cells but because each ERROR message needed to apply to both the X and Y rows of each lamp I thought it was the best way, so I'm glad you found a way around it.
I've attached the file I've been working, just for reference. It's actually for a SolidWorks design table hence all the stuff before column AW. Columns BI:BJ are the user input areas.
Bookmarks