# Find first and last values in a row

#### NalaBogie

##### New Member
My spreadsheet has analysis sheet in the front fed by numerous data sheets. The data sheets are named by dates (typically quarter ends e.g. 3/31/05, 6/30/05, etc.) The data sheets analyze construction projects as they progress. New data sheets are added as time progresses. The analysis sheet allows the user to look at a single project over time. The analysis sheet has three columns headed "Original Contract Price", "Adjusted Contract Price" and "Original Gross Profit". I would like a formula (preferably non-VBA) that can look across the row and find the first column in which real data appears ("non-data columns have "" in them) and then go to the correct sheet to pull the "Origninal" data and another formula that looks at the last column in which real data appears and then go to the correct sheet to pull the "Adjusted Contract Price". Clear as mud? I have attached an HTML page of the analysis sheet showing the headings and one job. There isn't room to include one of the data sheets. If if is permissable within the rules of this forum I would gladly e-mail a copy of the workbook to anyone that wants to see the whole deal. I appreciate any assistance.
TestWOH.xls
ABCDEFGHIJKLMN
1Acme Construction Company, Inc.
2Job No.ProjectOriginal Contract PriceAdjusted Contract PriceOriginal Gross ProfitDate03/31/200406/30/200409/30/200412/31/200403/31/200506/30/200509/30/2005
315Office Building\$ 476,000\$ 476,000\$ 95,875% Comp.  49%89%100% #REF!
4Profit  \$ 95,875\$ 96,800\$ 96,800 #REF!
Analysis

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
It's ugly but as long as you only have seven items you could use:

=IF(H1<>"",H1,IF(I1<>"",I1,(IF(J1<>"",J1,IF(K1<>"",K1,IF(L1<>"",L1,IF(M1<>"",M1,IF(N1<>"",N1,""))))))))

and

=IF(N2<>"",N2,IF(M2<>"",M2,(IF(L2<>"",L2,IF(K2<>"",K2,IF(J2<>"",J2,IF(I2<>"",I2,IF(H2<>"",H2,""))))))))

If you have MORE than seven for the percentages you could use:

=LEFT(TRIM(CONCATENATE(H1,I1,J1,K1,L1,M1,N1,O1,P1)),4)*1
=RIGHT(TRIM(CONCATENATE(H1,I1,J1,K1,L1,M1,N1,O1,P1)),4)*1

I'm still considering the dollar amounts for more then seven.

and to find the first and last columns, use these arrays

=MIN(IF(E1:I1<>"",COLUMN(E1:I1)))
=MAX(IF(E1:I1<>"",COLUMN(E1:I1)))

(In my test sample, I used columns E thru I, some being blank) The value it returns is the first column data appears in and the last column data appears in.

Oorang - Nice picture! Either I'm too stupid to follow your solution or I led you down the wrong path. Either way I appreciate the effort.

nbrcrunch - Your solution put me on the path to solving the problem. I was able to use the column numbers generated by your formulas to point me in the right direction. THANKS!!

I forgot to say I was using Excel 97 and Windows XP Home. Sorry

The only problem I have left is this. Is there any way to "nest" an array formula (one in braces) within a non-array formula?

Thanks again for your VERY quick help.

Oops I gave you the solution for columns.. just change the cell refrence.

Replies
3
Views
2K
Replies
15
Views
850
Replies
7
Views
341
Replies
6
Views
382
Replies
1
Views
205

1,207,439
Messages
6,078,569
Members
446,349
Latest member
Malroos7912

### 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.

### Which adblocker are you using?

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

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