Jump to content


 


Register a free account to unlock additional features at BleepingComputer.com
Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.


Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.

Photo

Auto-filling Formulas - Excel 2013


  • Please log in to reply
7 replies to this topic

#1 cbr2405

cbr2405

  • Members
  • 5 posts
  • OFFLINE
  •  
  • Local time:10:19 PM

Posted 30 May 2017 - 10:28 AM

Hi everyone,

 

I'm having some trouble autofilling an Excel formula. On my 'Data Entry' tab, there are several columns of data. For every three data entries, there is a merged cell in the column adjacent to the data that performs a 'SUM' function. For example, cells C4, C5 and C6 are summed in a merged cell that covers D4, D5 & D6. 

 

I am trying to transpose the data in the merged cells into another tab for analysis and other functions, including plotting. The formula for this is quite simple - " ='Data Entry'!D4 ". This is in cell A4 of the 'Processing' tab. In Cell A5 of the processing tab, I want the data from the next merged cell on 'Data Entry', so this is cells D7, D8 & D9. Again, the formula is simple - " ='Data Entry'!D7 ". I have entered these two and have the result I need. Problem is, there are several thousand merged cells that need transposing into 'Processing'. When I try to autofill my formula down, it fails to skip the cells in between and starts doing this:

 

='Data Entry'!D4

='Data Entry'!D7      (So far, so good)

='Data Entry'!D6      (Returns a zero value, because it is merged with cells D4 and D6 on 'Data Entry')

='Data Entry'!D9

='Data Entry'!D8

 

And so on.... I've filled in several formulas to try and get it to work, but no matter how far I go (I got to D34) it just refuses to work.

 

I know that I can use the formula " ='Data Entry'!D4:D6 ", and that this will solve the issue, but this will not plot in the way I need it to. I need a 'Scatter with Lines' graph, and when I tried plotting the transposed data using this formula, the lines on the graph were notably absent. As far as I can tell, this is down to the formula, because it works fine when I use the " ='Data Entry'!D4 " style formula. 

 

Please let me know if any other information is required - I've tried to be as descriptive as possible without confusing the issue. 

 

Many thanks in advance

 

CBR

 



BC AdBot (Login to Remove)

 


#2 smax013

smax013

  • BC Advisor
  • 2,329 posts
  • OFFLINE
  •  
  • Gender:Not Telling
  • Local time:04:19 PM

Posted 02 June 2017 - 04:33 PM

How are you doing the "autofill"? Are you doing it with highlighting the entered cells so far than then "pulling" the handle at the bottom right corner of the highlighted cells? Or are you copying and pasting? It may not matter, but if you have only tried one way, then try the other.

That is my best thought, which might not be much.

#3 cbr2405

cbr2405
  • Topic Starter

  • Members
  • 5 posts
  • OFFLINE
  •  
  • Local time:10:19 PM

Posted 02 June 2017 - 05:10 PM

Hi smax,

Thanks for your reply. Yes, I tried both and neither worked. Unfortunately, I am on a mobile right now - is there are way of making this as 'answered' or 'complete'? I ended up getting in touch with a guy who's email address was at the bottom of a tutorial page for a similar issue. I'm happy to report he was very happy to help and provided me with a very elegant formula and taught me something new in the process.

#4 JohnC_21

JohnC_21

  • Members
  • 23,235 posts
  • OFFLINE
  •  
  • Gender:Male
  • Local time:05:19 PM

Posted 02 June 2017 - 05:14 PM

BC doesn't mark threads Solved or Answered. When you get the time it would be nice if you posted the solution. Thanks.



#5 cbr2405

cbr2405
  • Topic Starter

  • Members
  • 5 posts
  • OFFLINE
  •  
  • Local time:10:19 PM

Posted 02 June 2017 - 05:16 PM

No problem. The formula etc are at work, but I'll try and post the solution on Monday when I'm back in the office.

#6 smax013

smax013

  • BC Advisor
  • 2,329 posts
  • OFFLINE
  •  
  • Gender:Not Telling
  • Local time:04:19 PM

Posted 03 June 2017 - 05:20 PM

No problem. The formula etc are at work, but I'll try and post the solution on Monday when I'm back in the office.


I have to admit that I am curious what the solution is myself. While I don't need that solution right now, it is useful to always learn more about how to use Excel.

#7 cbr2405

cbr2405
  • Topic Starter

  • Members
  • 5 posts
  • OFFLINE
  •  
  • Local time:10:19 PM

Posted 03 June 2017 - 05:31 PM

I remember it used an "INDEX" function, but I can't quite remember the detail off the top of my head.

#8 cbr2405

cbr2405
  • Topic Starter

  • Members
  • 5 posts
  • OFFLINE
  •  
  • Local time:10:19 PM

Posted 05 June 2017 - 05:41 AM

Hi everyone,

 

The solution involves using an 'INDEX' function and looks like this:

 

=INDEX('Data Entry'!$D$4:$D$471,3*(ROWS($A$4:A4)-1)+1)

 

This will work for any number of merged cells. For example, if you are merging five cells together in a column, change the number highlighted in bold to 5. 






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users