View Single Post
 
Old 04-10-2015, 11:51 AM
bwwalker08 bwwalker08 is offline Windows 8 Office 2013
Novice
 
Join Date: Apr 2015
Posts: 1
bwwalker08 is on a distinguished road
Default Move data from one sheet to the next available row or cell of another sheet

Simplified formula to move data from one sheet to the next available row or cell of another sheet when a number is entered into a cell range?

I am trying to put together a flat rate system for our dealership. It is still in the very early stages of being formed, but I would like it to have the ability to move a whole row of data to a quote form (another sheet) when a number such as "1 or 2" has been entered into a specific range of cells, and then multiply the number entered into the specified range, in the event that the flat rate code needs to be used more than once on a service quote. I have been able to accomplish this on a cell to cell basis with the formula listed below. (note that 90AAAAAAA is the name of the sheet that I am pulling data from, Cell B9 is the cell that will activate the formula, if it is a value greater then 0.5, C9 is the cell the data will be pulled from if activated by cell B9, and as mentioned above, I wanted it to multiply the number in C9 by the activation value in B9):

=IF('90AAAAAAA'!B9>0.5,'90AAAAAAA'!C9,"")*('90AAAA AAA'!B9)

So, I have a few questions:

1.
How can I make this formula pull data from not only multiple cell ranges (i.e. C9:C500) and have it be activated by multiple cell ranges (i.e. B9:B500), but also pull from approximately 50 different sheets?

2.
Is it even possible without a complex string?

3.
Is it possible if I copy and pasted the above formula 491 more times for each one of the cells I would like it to pull data from, then do this for each sheet I would like to pull data from? (I realize that that’s like 25,000 formulas, but I guess the question I am asking there is, is there a limit on how characters can be entered into the formula box?)

4.
Lastly, if we were able to figure the above questions out without a complex string, would we be able to have the formula move the data described to the next available row? Because when we are going to be putting together a quote, we are probably going to be using several different codes at random and would like them to automatically go into the quote form neatly. Again, if this isn't possible without a complex string, then we maybe shouldn't worry about it, because that is something that is out of my very limited book of knowledge when it comes to Microsoft Excel.

A copy of what I am beginning to work on has been attached, if that will help explain things any better.

Thank you!
Reply With Quote