Forum Discussion
Excel Vlookup / IF / Textbefore
This sheet is a one-time use sheet. I am converting data that one of our sub-companies held in 19 Smartsheets. This sheet is designed to help create the upload sheet that will concatenate all of this data into a CSV file that will be mapped to a current live database program,.
I cannot share this sheet because of the confidential nature of the data. In addition, I cannot go through and change the data in a way where this will still work and many of this sheet's functions are turning text into numbers.
I will try to explain what I am doing and try not to waste anyone's time.
Each row on this sheet where there is a filled cell in Column I is a complete record for this part of the sheet. Column I represents a "program", which is a service contract.
Column J shows a unique number assigned to a Program, where the first part of the number is a Store Number (Store being a business location). The second part of the number is a numerical representation of a date. This combination assures that each number in Column J is unique.
Lines 8,16,25,26 and 28 are sub-lines of line 4. These lines are designed to carry forward inventory items to the main record in the program upload. In column K, you can see the 5 possible inventory items, from each record. The quantity number for each of these rows come from the associated column in the initial Row record, those being columns AH, AI, AJ, AK, AL, AM. I have noted in the headers for each of those columns their column number in the array M4:AM10000.
In the summary sheet that I have shown in this post, there are 2800 Lines that have a reference in Column I (making them a parent line). There are 5 sub-lines (child lines) under each of those lines where I am attempting to fill in the data from the parent lines.
The challenge comes when I attempt to copy the formula(s) that sit in cells L8, L16, L25, L26, L28 and paste them (or in some other way copy them) into L53, L55, L63, L67 and L77. The reason that this continues to fail is that the row numbers under the Parent row vary in their displacement to the row above, This is caused by other calculations going on in hidden columns and on feeder and helper sheets.
For anyone who would like to offer a suggestion, thank you. It is very appreciated.
OK, Chris. I too began in the olden days. My first "spreadsheet" was created in 1970 or 1971 by me, using APL, quite literally "A Programming Language,"--a software package developed for and primarily used by mathematicians and scientists. It had the capability of creating multidimensional arrays of numbers, with each cell being addressable. I never used VisiCalc per se, but did make use of a lot of the early spreadsheets, graduated ultimately to Lotus at work, Quattro at home, and ultimately Excel in both locales. I've been retired for a bit over 20 years now, am in my early 80s, have fun helping people resolve issues with Excel. I'm by no means the most accomplished user of Excel--do not use VBA, preferring to figure out how to get from A to B using some of the amazing functions that Excel makes available. I do consider myself to be well informed in databases, database design, the use of tables, and--in general--good reliable design of a spreadsheet.
That was the basis on which I offered the suggestion that you concentrate on redesigning your worksheet because if it were to be used more than once, it's going to be a long term headache for its users.
Now that you've said it's a one-time use sheet, and that your challenge is
to copy the formula(s) that sit in cells L8, L16, L25, L26, L28 and paste them (or in some other way copy them) into L53, L55, L63, L67 and L77. The reason that this continues to fail is that the row numbers under the Parent row vary in their displacement to the row above,
I can see why total re-design isn't appropriate. Given the hints in your paragraph above, I wonder if INDIRECT, with row numbers calculated for those varying displacements--based on yet another set of "helper columns" that show the displacement appropriate for the row-- incorporated in the resulting formulas.
Frankly, though, this sentence
This is caused by other calculations going on in hidden columns and on feeder and helper sheets.
worries me. With all those other calculations going on, in all those places, well, that's why, without actually seeing what you're working with--and I get it, that it can't be shared--but it's very very difficult to offer any substantive and specific suggestions. INDIRECT can be used creatively to modify formulas and references made within formulas IF you know what those modifications need to be. I gather you need to do this not merely on row L, though, but on many more of the 2800 lines in the spreadsheet, and unless there's a fixed and known pattern to those varying displacements, you are working with a monster that is (not telling you anything you don't know) too wild to tame.
mathetes
My friend, THANK you. Nice to know I am not the very oldest goat working in this stuff (still hanging on to my 60's).
I knew this was going to be a wild one and I just wanted to see if anyone who looked at it went "Oh yeah, just do THIS".
I am going to have to go the route of a picot table to get everything where I can see it. The biggest challenge is that this is all LIVE data so I cannot just freeze anything and then move it around. On GO-LIVE day, this will be an export to CSV and an upload to the database CRM and then I am going out for a Martini or two.
Again my friend, thank you for taking a look and I do so appreciate you time, Happy 2023!This may work for you. I see you have access to TEXTBEFORE so you also have XLOOKUP (No need to involve 24+ columns with VLOOKUP).
The formula is a bit longer but more flexible:
=LET(data,$M$2:$AM$10000,store,TEXTBEFORE(INDIRECT("J"&ROW()),"-")*1,col,XMATCH("*"&INDIRECT("K"&ROW())&"*",$M$1:$AM$1,2),XLOOKUP(store,TAKE(data,,1),CHOOSECOLS(data,col),""))- You have used a number of functions I have never used and I hate to appear just plain stupid, but I have to ask:
The items you show in white in your formula (Store | Data | data,col): These appear to be references that I need to add. Am I correct? And if so, I need a bit of a better understanding as to what I am looking for for each reference.
I am sorry for having to bug you with this, and Thank You in advance!
