Excel Formula Help

Copper Contributor

Hi there, I'm creating a spreadsheet for a game and I'm a little stuck trying to work out the formula to help me. Long story short I need the formula to change the reference down one cell every 6th cell, I've got around 11000 cells to change so manually isn't the way forward. Please see attached screengrabs, the first picture is my starting cell 

 

This part is correct every time I drag down:

=H4&" "&I4&" "&J4&" "&

 

However everytime I drag down the cell the "Stock!M4" goes down to "Stock!M9" and not M5 and so on

5 Replies

@nflkeeper 

 

Images are helpful, but not at all as helpful as the actual spreadsheet. Unless it contains confidential information, may I ask you to post the actual spreadsheet. If it DOES contain confidential information, make a mock-up of it and post that. You'll get quicker and more to the point help that way.

All done @mathetes 

@nflkeeper 

 

Which column is it that you're concerned with? I'm having a tough time figuring out the structure of your sheet, why you appear to have so many duplicate rows in columns C through T for example.

I think it's column B that you're referring to but those seem to be working......  (More concerning, however: that combining of cells is NOT a good way to lay out a database, which is far better designed as single rows of data. FAR BETTER.)

 

So could we back up a bit and get you to describe how you're going to be using this.

  1. I see you have the start of what looks to be a more conventional database on the sheet called "Stock"...
  2. What would be the difference between "Specs" and "Stock" over the long term? Could those two sheets (i.e., the info on them) be combined?
  3. Then there could be a different "Dashboard" created to extract data, on demand, on any given car...make sense?
  4. Or something else....but my basic question, before I help you solve a problem that you're creating (based on a quick look) by what appears to be a bad (or questionable) design decision. I'd rather not perpetuate that, if it's possible to fix it more deeply.

@mathetes 

 

Thanks for the reply. To answer your questions as whole as I can, the duplicate cells between C & T are as follows. Each car has 5 different statistics; stock and then the 4 different upgrade points (see Column C) under the speed, 0-60, grip and weight columns will be different figures. These will then refer over to sheet "Stock" where I can then use the drop down list to choose whichever upgrade I need to see figures on 

 

In Column B I need the formula to maintain the correct pattern when dragging down i.e

=H4&" "&I4&" "&J4&" "&Stock!M4

=H9&" "&I9&" "&J9" "&Stock!M5

=H14&" "&I14&" "&J14&" "&Stock!M6

 

Currently it is 

=H4&" "&I4&" "&J4&" "&Stock!M4

=H9&" "&I9&" "&J9" "&Stock!M9

=H14&" "&I14&" "&J14&" "&Stock!M14

 

nflkeeper_2-1598456359424.png

 

nflkeeper_1-1598456267459.png

 

nflkeeper_0-1598456166317.png

 

@nflkeeper 

 

Thanks. That helps get a picture of your goal. There were a few other questions, however, and I am hoping to get those answered before tackling the question you're asking. And this is not a case of my being difficult (despite how it may appear).

 

You wrote, intriguingly: Each car has 5 different statistics; stock and then the 4 different upgrade points (see Column C) under the speed, 0-60, grip and weight columns will be different figures. These will then refer over to sheet "Stock" where I can then use the drop down list to choose whichever upgrade I need to see figures on

 

And this points me back to one of my questions from earlier: what are you doing to be doing with all of this when it's set up? How will you be using it? What you wrote, quoted above, makes it sound as if you'll be retrieving information on one model, one upgrade, at a time...wanting "to see figures on" 

 

If that is indeed the case, a single data extract from a single database could serve you well. If you want to compare two or three models, upgrades, whatever, the same idea applies. What you're creating with the Specs tab is (I guess) an Output sheet, or at least it's serving some output purposes; but it's also got all sorts of data present (on different models) that you won't need at any given point.

 

So I want to go back to the possibility of creating a single database (possibly by merging data from the Specs and Stock tabs) linked then to a single purpose Output sheet (these are often called "Dashboards") that you'd use to retrieve the information a the model or models you're interested in at the moment.

 

My point with regard to the formula on which you want help: I'm sure it's possible to write something to derive a different row reference for column B on the Specs sheet, but the deeper I look, I see there are already links between Specs and Stock...specifically, you derive the name in Stock, column B, from Specs, and you do it with a very sophisticated formula  using OFFSET to get that name FROM the very column that you're trying to fix in Specs. This is the kind of circular referencing that makes one's head spin.

 

Said another way, you're making your task more difficult by unnecessarily separating what could easily be a single database into two. IF the differences between the rows on Specs is the different statistics and different upgrades, those could become different columns on a combined database that, together with the model name, are the "keys" to identifying any given row on that combined database. It sounds like that's what you're trying to achieve here...with a separate sheet on those 'specs.'

 

By the way, I came across all this circular thinking entanglement when I started to write a formula using the MATCH function to identify the row in Stock from which you'd want to retrieve the info in column M. If you want to persist in using the design you've got, that's what I'd recommend, getting a MATCH to the row in which the name appears....but that's where the circularity comes in.

 

You clearly know how to write sophisticated formulas--so if that helps point you in the direction of a solution, great. It could be that, perhaps with INDIRECT, or OFFSET......my major recommendation, though, is that you re-think the fundamental design.