•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Change Sheet reference based on cell value
Our take
Are you tired of the repetitive task of manually updating VLOOKUP references in your spreadsheets? Imagine a solution where the data table changes automatically based on the cell value above it. This approach not only streamlines your workflow but also frees you from the monotonous busy work that can stifle productivity. In this guide, we'll explore how to implement dynamic references, empowering you to work more efficiently and focus on what truly matters—transforming your data into actionable insights. Let’s dive in!
Hopefully the title makes sense.
Below is a snippet of what I'm working on. What I'm wanting to do is have the data table being referenced in the VLOOKUP change depending on the cell value above it. Right no I've just been manually changing each one to match. This is fine busy work but I get antsy after Data20.
I'm also open to other ideas if you have them. Thanks!
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Change contents of cells based on a drop downSo, I'm trying to generate a spreadsheet to make the forms we use in the office to issue keys. When people are given an apartment, we have a sheet with all the key numbers on it, and then they sign for it. At the moment, we've got 22 different PDFs and Word documents, and it doesn't work. So, what I want to do is this. I've built the template in Excel and created a Reference sheet which will have all the numbers in it. What I am trying to do is set it up so that when you use the Apartment number from a dropdown, it populates with all the key numbers. This sounds like it should be easy to do, but my Excel brain is broken. Advice would be much appreciated! submitted by /u/Hot_Syrup_1774 [link] [comments]
- What would it take to have a cell that does a Vlookup based on the cell that's currently clicked on?Apparently the sheet already crashes here and there so I suppose the more elegant the solution the better. We have these big meetings where we will run through hundreds of projects and their costs. It's my job to monitor how a different variable is getting managed if a project gets moved in or out. Everyone else is really only concerned with the money, Currently my steps would be: Look at the sheet the presenter is showing, open a sheet on my laptop, manually type the project number in a search, see the variable assignment, then I check another sheet to see how that variable is performing. Ideal setup would be: As the presenter clicks on a project, in the corner of his screen it looks up the project and shows the variable. Now I can just have my variable performance sheet up on my laptop and respond quickly since the info is already looked up and on his screen. I used to be pretty good with VBA, though haven't touched in 5-10 years - open to those types of solutions as well. submitted by /u/TimelyExtent2402 [link] [comments]
- Moving a series of references over a columnI have a worksheet which I'm using to help me predict the outcome of football games using a combination of average scores and average scores allowed, as well a dozen other things to determine the outcome. I had a simpler, call it the beta version, that I used last year to test if my idea held any water. what I found is that it had a bell curve of accuracy where it started relatively inaccurate but improved towards the middle of the year before it fell off. I considered the later inaccuracy to be the work of too much information dragging the predicted scores down or up so limiting the info to the last 5 inputted games. So the crux of my problem is now that I'm inputting last year's data into my current system to test the current configuration. I am having the main sheet referencing a row of info for that team specifically and I need the reference block to move over as the season advances. I have the data for up to week six inputted. But now I have to move all the team references over one column to essentially ignore the results of week 1. So what I need to do is turn =Sum('TeamScores'!C3:G3) too become =Sum('TeamScores'!D3:H3) if I could do that without having to go through each team and each reference and change that for each week it saves me some serious man hours. As well as saves me from the potential of human error. I considered changing the orientation of the references to be in a row but it comes at the cost of readability. it also doesn't really solve my problem. I'll give any other info that may need to answer my query if needed. submitted by /u/cagbeema14 [link] [comments]
- Central Data Sheet efficiencyHoping that someone with some in depth technical knowledge of Excel can help me out with a query. We issue Finance Tracker spreadsheets to projects in our organisation, and then link them back to a central sheet that we use for monitoring and reporting. There are maybe 40-50 cells in each spreadsheet that we need to pull in to our central sheet, but they’re spread around the tracker and so are generally linked individually, and we’re now approaching 120 trackers that need to be linked, so the central sheet is starting to get quite slow to load and update. My question is this: When doing cross workbook cell look ups, do arrays work more efficiently than individual cell references, or is there no noticeable difference in runtime? My idea is to, in each project’s workbook, create an “export” tab that contains all the information that needs to be pulled out, and link the central sheet by looking at that array, rather than each individual cell. submitted by /u/Jamespg614 [link] [comments]