Reference all sheets in a workbook (no matter if the name of the last sheet changes)
Our take
Short version, I'm trying to reference all sheets in a workbook even when I save as with new data over the current workbook. I'm currently saving as a temp, adding a page called endpage, then saving over the old data. There has to be better - more details below.
-----------
I'm currently doing a bit of a workaround to accomplish what I'm trying to do, and I'm thinking there has to be a way to do this in the formula.
I'm working on a summary worksheet that pulls in data from a couple of reports I download each month. For one of these reports the way it comes out from the software is... inconvenient (and not something I can fix). I have a group of locations the report runs for, and each location has a name I reference by using a formula like this:
=XLOOKUP(A78,VSTACK('[worksheetlocation]Sheet1:EndPage'!$F$7),VSTACK('[worksheetlocation]Sheet1:EndPage'!F10))
Where A78 is the location name, F7 is the location on every sheet that has the building name, and F10 is the data I'm returning. This formula works. The reason I have to do this is if there is no data for a location, the sheet doesn't show. Also, the buildings don't come out in the same order every time.
That issue means that sometimes the sheet names could be Sheet1, Sheet2, Sheet3...Sheet37 then next time be Sheet1, Sheet2, Sheet3...Sheet35. So right now I download the report and name it 'TemporaryDownload', then go in and add a sheet at the end named EndPage that then lets me reference all sheets between Sheet1 and EndPage. Is there a way to simply just say I want to have the formula look at all sheets in this workbook no matter what?
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Formulas Across Worksheets that are Dynamic and can Accomodate new sheets/conditionsNot sure if title makes sense but here is a problem I'd like to solve (as simply as possible). Lets just say I want to sum the same cell on a number of worksheets but I want to be able to add worksheets. That can be accomplished by putting sheets between two sheets called First:LastCell... at least that's what google told me and it worked. So if I add sheets between First and Last it will get captured in the formula. So what if I want to do the same thing but based on a condition in each sheet? As in I have a cell reference call out to either exclude or include for each sheet in the formula? Apologies if this is confusing. submitted by /u/NegativeActivity3184 [link] [comments]
- I am trying to prevent the end user from having to insert data twice to see it on two different sheets, and the formulas I've been trying aren't copy/pasting well.I have been making a CRM spreadsheet for a finance business with over 100 clients. In trying to optimize the formatting, I have developed two different views which have two different uses. Sheet 1 is "2026 List View" where the informaiton is all on one row per client. It is annoying to side scroll to see all of the information, but it gives the best overall view as to who has paid. https://preview.redd.it/1yinsuhafikg1.png?width=3370&format=png&auto=webp&s=7559a5cfc76b671e2bbcc4b4700c446e2531ce46 Sheet 2 is "2026 Grid View" where the informaiton is organized differently to not need side-scrolling, and gives a better quick per-client view. https://preview.redd.it/2oip1qrbfikg1.png?width=2697&format=png&auto=webp&s=7b4f9f29f34db95d14e17df60efca580ff4c546a I am writing formulas for Sheet 2's cells to reference Sheet 1's data. For example, Sheet 2's cell B3 has ='2026 List View'!A2 and it is working well in terms of the formula. However, once I finished two tables refrerencing cells in rows 2 and then 3, copy/pasting made the next table do the correct columns but in the rows 16 and 17 instead of 3 and 4. I tried continuing the pattern longer, but copy/pasting still made it skip several rows though it is recognizing the correct columns. Is there a different formula I could use to make the copy/pasting more successful? Or, am I stuck doing this cell by cell for 100 rows, making it not worth the hassle? submitted by /u/urgrlB [link] [comments]
- Two workbooks, two columns in second book could include matching data, neatest most efficient way to query between them on multiple fields using excel formula only, no VBA / Power Query etc?Not even sure how to surmise this but here we go: Workbook 1, column i has a reference code, "EntID" which could be a number OR text. Workbook 2, that reference code might be found in Column A, or Column B, or neither. In Workbook 1, I need 5 lookup columns to pull data from 5 corresponding columns in Workbook 2 where there is a match for " EntID" in EITHER column A or B. If there is no match then the cell should state "Not Found". I have it working by using nested if statements, isnumber & xmatch for the first lookup column to ascertain where the match is found and then xlookups on remaining columns depending on the result returned to that cell but the formula are huge if for no other reason than the file naming and locations (which cannot be changed) and it feels slow, clunky and unrefined. How would you do it? submitted by /u/Delinquent90 [link] [comments]
- Can excel find data in a sheet, if that data sometimes may not exist/be in the same cells?I am making a sheet at my work that basically sums up stats about staff each week. However, the internal source I pull the raw data from only shows employees who actually worked on a given day. My final sheet will show daily data and weekly stats. I am trying to find a way to create a formula that will match that employee's name with their data on the raw data sheet and pull it into the finished sheet. But it will not be in the same cell each time. I am trying to avoid hand-tranferring the data if possible. What I really want is a formula that will tell it "if you see Jane Doe, put the data two columns to the right of her name into this cell." Excel LTSC MSO Version 2408 English language, Windows desktop, beginner proficiency submitted by /u/pinkysaurusrawr [link] [comments]