(Msg. 1) Posted: Thu Sep 04, 2008 8:40 am
Post subject: Return Last Value in a Row formula Archived from groups: microsoft>public>excel>worksheet>functions (more info?)
I am using this formula:
=INDEX(1:1,MAX(INDEX(COLUMN(1:1)*(NOT(ISBLANK(1:1))),))) to get the last
non-blank value in my current worksheet. Does anyone know how to use this
formula to get the last rnon-blank value of a different workbook?
(Msg. 2) Posted: Thu Sep 04, 2008 12:26 pm
Post subject: Re: Return Last Value in a Row formula [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Just add the path.
Open the other workbook and use your mouse to point to the the source
ranges. This way Excel will add the path for you.
For example...
Start typing the formula: =INDEX(
Now, use the mouse and point to the other workbook and the desired sheet row
1
Excel will add the path for you:
=INDEX([Dates.xls]Sheet1!$1:$1
Then just finish the formula in this manor.
--
Biff
Microsoft Excel MVP
"Txlonghorn76" <Txlonghorn76 RemoveThis @discussions.microsoft.com> wrote in message
news:EBF3CDC7-8649-4A96-8E9A-09991B43E172@microsoft.com...
>I am using this formula:
> =INDEX(1:1,MAX(INDEX(COLUMN(1:1)*(NOT(ISBLANK(1:1))),))) to get the last
> non-blank value in my current worksheet. Does anyone know how to use this
> formula to get the last rnon-blank value of a different workbook?
>
> Thanks!
(Msg. 3) Posted: Thu Sep 04, 2008 12:26 pm
Post subject: Re: Return Last Value in a Row formula [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
I changed the formula to this but it's only giving me the contact of the
first column of row 27. Please help.
> Just add the path.
>
> Open the other workbook and use your mouse to point to the the source
> ranges. This way Excel will add the path for you.
>
> For example...
>
> Start typing the formula: =INDEX(
>
> Now, use the mouse and point to the other workbook and the desired sheet row
> 1
>
> Excel will add the path for you:
>
> =INDEX([Dates.xls]Sheet1!$1:$1
>
> Then just finish the formula in this manor.
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "Txlonghorn76" <Txlonghorn76.RemoveThis@discussions.microsoft.com> wrote in message
> news:EBF3CDC7-8649-4A96-8E9A-09991B43E172@microsoft.com...
> >I am using this formula:
> > =INDEX(1:1,MAX(INDEX(COLUMN(1:1)*(NOT(ISBLANK(1:1))),))) to get the last
> > non-blank value in my current worksheet. Does anyone know how to use this
> > formula to get the last rnon-blank value of a different workbook?
> >
> > Thanks!
>
>
>
(Msg. 4) Posted: Thu Sep 04, 2008 2:11 pm
Post subject: RE: Return Last Value in a Row formula [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
That worked. TY!!
"Txlonghorn76" wrote:
> I am using this formula:
> =INDEX(1:1,MAX(INDEX(COLUMN(1:1)*(NOT(ISBLANK(1:1))),))) to get the last
> non-blank value in my current worksheet. Does anyone know how to use this
> formula to get the last rnon-blank value of a different workbook?
>
> Thanks!
(Msg. 5) Posted: Thu Sep 04, 2008 3:57 pm
Post subject: Re: Return Last Value in a Row formula [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
You have to add the path to every function that references the other
workbook so you need to add the path to the ISBLANK function and the COLUMN
function as well.
Although the path is not needed in the COLUMN function you might want to add
it just so it doesn't lead to confusion.
--
Biff
Microsoft Excel MVP
"Txlonghorn76" <Txlonghorn76 RemoveThis @discussions.microsoft.com> wrote in message
news:36C6E378-B5B2-48F5-AC08-CE65397E62F1@microsoft.com...
>I changed the formula to this but it's only giving me the contact of the
> first column of row 27. Please help.
>
> =INDEX('[Sept - Daily Doc 08.xls]Daily Individual
> Numbers'!$27:$27,MAX(INDEX(COLUMN(27:27)*(NOT(ISBLANK(27:27))),)))
>
>
> "T. Valko" wrote:
>
>> Just add the path.
>>
>> Open the other workbook and use your mouse to point to the the source
>> ranges. This way Excel will add the path for you.
>>
>> For example...
>>
>> Start typing the formula: =INDEX(
>>
>> Now, use the mouse and point to the other workbook and the desired sheet
>> row
>> 1
>>
>> Excel will add the path for you:
>>
>> =INDEX([Dates.xls]Sheet1!$1:$1
>>
>> Then just finish the formula in this manor.
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Txlonghorn76" <Txlonghorn76 RemoveThis @discussions.microsoft.com> wrote in message
>> news:EBF3CDC7-8649-4A96-8E9A-09991B43E172@microsoft.com...
>> >I am using this formula:
>> > =INDEX(1:1,MAX(INDEX(COLUMN(1:1)*(NOT(ISBLANK(1:1))),))) to get the
>> > last
>> > non-blank value in my current worksheet. Does anyone know how to use
>> > this
>> > formula to get the last rnon-blank value of a different workbook?
>> >
>> > Thanks!
>>
>>
>>
(Msg. 6) Posted: Thu Sep 04, 2008 5:55 pm
Post subject: Re: Return Last Value in a Row formula [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
You're welcome. Thanks for the feedback!
--
Biff
Microsoft Excel MVP
"Txlonghorn76" <Txlonghorn76.TakeThisOut@discussions.microsoft.com> wrote in message
news:488A1CB9-AA81-4BC9-BEFB-51DFAD84062B@microsoft.com...
> That worked. TY!!
>
> "Txlonghorn76" wrote:
>
>> I am using this formula:
>> =INDEX(1:1,MAX(INDEX(COLUMN(1:1)*(NOT(ISBLANK(1:1))),))) to get the
>> last
>> non-blank value in my current worksheet. Does anyone know how to use this
>> formula to get the last rnon-blank value of a different workbook?
>>
>> Thanks!
All times are: Eastern Time (US & Canada) (change)
Page 1 of 1
You can post new topics in this forum You can reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
Home
Forums
Home
FAQ
Profile
Private Messages
Log in
Office