Expanding rows based on amount of data

[bremen22]

So I have a spread sheet that queries a database and returns data based on a date. The number of rows returned can vary from month to month. To have the data look the way people want it to I have formulas that take the data from one column and translate it into a readable form.

My problem is I can seem to figure out how to input my formula in such a way that excel will automatically expand the number of rows based on the amount of data returned.

[catchnanan]

hai brem can you attach a sample excell list so that response will be quick

[DavidJAllen]

bremem22,

From what you describe the might help.

1) Create a dynamic range (formulas, name manager) using the formula
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1).
This will expand to the number of rows in your dataset

2) Loop through the range using VBA and insert the formulas you need.

Please Login or Register  to view this content.

I have attached a spreadsheet with the above.

Hope that helps. If not lets try again.

Regards

David

[bremen22]

I have added that formula into my sheet and when I have more data to work with I will know if it works.

Thank you.

[bremen22]

Ok...

So I tried that with a different data set, but it didn't work.

I am not sure about adding the formulas to the VBA script.

Here are two examples of actual formulas in my sheet. Can you show me what the VBA would look like with these two forulas?

For Columb B : =IF(K2="", "STK", LEFT(K2,4))
For Column C: =IF(P2=0, "No", "Yes")

[FDibbins]

THose 2 formulas by themselves dont tell us very much

As suggested, upload a sample work book, (all sensitive data removed), showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[bremen22]

Ok..... Since all of the data in my sheet is sensitive I have created a sheet as an example. All of the data in this sheet is fictitious, but it looks similar to what i actually have. The data highlighted in red will be hidden.

Now, what is not included in this sheet is the ODBC connection and query to my Oracle database. I am not making a fake one of those. Now the query will only return rows of data that fall within the current month. The number of purchases each month will change and thus so will the amount of rows in the data set.

Now the formulas that are in this sheet are very similar to the actual formulas I am using with the data in the formula being different.

Hope this helps.


Sample Data.xlsx

[catchnanan]

Hai i moved some of your data at the bottom of the sheet 1 to sheet2
Now you add new data right from 26 th row and see if this is what you want
and plz give feed back

Narayanan