return value after a set number of character instances

nofrillz

New Member
Joined
Aug 16, 2008
Messages
11
Here is my problem...
I have simplified my excel pages as much as I can to make this issue easy but I am left with one last column I cannot complete.

I need to return only the values in a set number of cells in a larger range (always counting and including the cells starting from left to right) based on a number manually set in another cell, while the range is always static (lets say 20 cells)

so If I have the number 8 in one cell that I am going to reference in this formula as the "count" and I want to use that "count" ONLY to return the valuse of the first (8 in this case) cells in a range of lets say 20, what would that formula be?

If the number is 2 I want to then ONLY return the values in the first 2 cells in the same range of 20, etc. etc.
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
What do you want to do with the 8 or 2 first numbers.
If they need to be included in a formula ( SUM for example) then you could use an OFFSET function to cover the range for these 8 or 2 first numbers.
SUM(OFFSET(A1,0,0,COUNT,1)) NOT TESTED
Where COUNT is the cell containing = 8 or 2.
 
Upvote 0
Let me try to better explain this (i'd post the cells but Im at work and have no access to install the plug in to convert to HMTL).

I have 5 size scales, 1 has 5 sizes, 2 has 6, so on and so on alway incrementing by 1 so that the 5th has 9. On an excel order form there are 9 cells in a row that hold the quantity of units. The quantiy is dependant on the size scale so if someone is using size scale 1, I might have the following rows 1,2,1,1,2,0,0,0,0 The trailing last 4 cells of zeros are there because in size scale 1 there are only 4 sizes to order therefore the last set of columns are empty.

This file is coming from a client and I cannot change it nor the way it is filled out.

My system expects ONLY the qty for the exact amount or size scale, so in my system is someone is ordering against size scale 1, my system needs "1-2-1-1".

So my dilema is that I can pass the size count to a formula based on other formulas I wrote, so Ill know "only return the values in the first 5 cells" however I dont know how to write a formula that says grab all 9 cells, cut it after my count, then merge the content seperated by a "-".

Hope this clears this up, if not Ill wait till I get home to paste the HTML in of my examples.
 
Upvote 0
To start quickly perhaps next UDF could help.
Assuming numbers to treate are in column somewhere:
G2:G10 = 1 2 1 1 2 0 0 0 0
Cell named COUNT = 5
I9 = MYRESULT(Count,G2:G10)
Code:
Option Explicit
Function MYRESULT(MyCOUNT As Integer, MyRG As Range) As String
Dim I As Integer
    MYRESULT = MyRG.Cells(1, 1)
    For I = 2 To MyCOUNT
        MYRESULT = MYRESULT & "-" & MyRG.Cells(I, 1)
    Next I
End Function
 
Upvote 0
You could also instead of providing the count, provide the size and let the macro determine the count for that size:

Code:
Function ReturnCount(scalesize As Long, r As Range) As String
Dim i As Long, tmp As String
scalesize = scalesize + 4
For i = 1 To scalesize
    tmp = tmp & r.Cells(i) & "-"
Next
ReturnCount = Left(tmp, Len(tmp) - 1)
End Function

So, for size 1
=ReturnCount(1,G2:G10)
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top