Excel Questions

[lfdown2]

I have a link from a pivot table in one workbook to a separate workbook, each day the pivot table updates and adds an extra row - does anyone know how I can link my excel workbook to the value as opposed to the cell, so that when the pivot table updates and I want the link to draw the value from A2 as opposed to A1 as was the previous days?

[macdanger2]

I have a link from a pivot table in one workbook to a separate workbook, each day the pivot table updates and adds an extra row - does anyone know how I can link my excel workbook to the value as opposed to the cell, so that when the pivot table updates and I want the link to draw the value from A2 as opposed to A1 as was the previous days?

Not sure it's possible to have the link floating like that.

Is there something about the new line that you could use to pull it into a cell on the same sheet and then just link to that new cel?? For example if the new line has today's date in the left column, you could use that. Or if it's the largest / smallest value, you could use that.

[Maiden1]

I have a link from a pivot table in one workbook to a separate workbook, each day the pivot table updates and adds an extra row - does anyone know how I can link my excel workbook to the value as opposed to the cell, so that when the pivot table updates and I want the link to draw the value from A2 as opposed to A1 as was the previous days?

When you are setting up the initial pivot table instead of putting the range as Sheet1!$B$1:$C$4 you could put the range as Sheet1!$B$1:$C$1000.  That way you would have the complete range for 1000 days.  After you set it up you can filter out blanks.

[Smokin Joe]

I have a link from a pivot table in one workbook to a separate workbook, each day the pivot table updates and adds an extra row - does anyone know how I can link my excel workbook to the value as opposed to the cell, so that when the pivot table updates and I want the link to draw the value from A2 as opposed to A1 as was the previous days?

Use a vlookup to look up the value of the item in the pivot table.  Then no matter where in the pivot table the item appears you will always get its correct associated value (ie the number you want to return in your other workbook)

[lfdown2]

cheers lads, ill let ye's know how that goes

[AZOffaly]

OK Excel Gurus, this is probably really simple to ye but I'd appreciate the help. 

I have a list of 700 values in a column (say column A) like D01, D02, D06 etc.
In Column B, I have a list of 1700 values in a column, like D01, D02, D03, D05 etc,

So essentially the 1700 rows in column b are a super set of column A.

What I am trying to do, via Lookup is to identify, in a column C, which of the Column B values are in the subset in Column A.

What I am looking for is basically like this

D01    D01   Yes
D04    D02   No
D06    D03   No
          D04   Yes
          D05   No
          D06   Yes

I almost had it with lookup, but the best I could have was to put the D number itself into Column C if it was found, and I really want to have something a bit neater than that.

Cheers

[majestic]

OK Excel Gurus, this is probably really simple to ye but I'd appreciate the help. 

I have a list of 700 values in a column (say column A) like D01, D02, D06 etc.
In Column B, I have a list of 1700 values in a column, like D01, D02, D03, D05 etc,

So essentially the 1700 rows in column b are a super set of column A.

What I am trying to do, via Lookup is to identify, in a column C, which of the Column B values are in the subset in Column A.

What I am looking for is basically like this

D01    D01   Yes
D04    D02   No
D06    D03   No
          D04   Yes
          D05   No
          D06   Yes

I almost had it with lookup, but the best I could have was to put the D number itself into Column C if it was found, and I really want to have something a bit neater than that.

Cheers

Add an IF function.

=If (vlookup(......),"Yes", "No")

[AZOffaly]

If(vlookup(B1,A1:A700,1),"Yes","No")  ??

[majestic]

=IF(ISNA(VLOOKUP(B4,$A$1:$A$3,1,FALSE)),"No","Yes")

[AZOffaly]

Brilliant stuff. Works like a charm. Thanks a million.

[passedit]

Dug out this topic to look for help on a similar issue to AZ (coincidentally). Large spreadsheet interrogating a smaller one where I wanted to leave blanks rather than #N/A in every non matching cell. Starting from Majestic's post i got

=IF(ISNA(VLOOKUP(A12,[070115.xlsx]AOPR!$B$11:$AD$17,18,FALSE)),"",VLOOKUP(A12,[070115.xlsx]AOPR!$B$11:$AD$17,18,FALSE))

which gives me the entry I want or a blank.

Cheers Majestic

[townof12]

Ok folks looking for some help here.  I'm recording some data in years and months, eg Cell A1 7 years and 2 months, A2 8 years and 9 months.  In A3 I want to use a formula to calculate the difference.  What's my best way of doing this?  Some idea of how to use excel, but I'm no expert.

[armaghniac]

Ok folks looking for some help here.  I'm recording some data in years and months, eg Cell A1 7 years and 2 months, A2 8 years and 9 months.  In A3 I want to use a formula to calculate the difference.  What's my best way of doing this?  Some idea of how to use excel, but I'm no expert.

Is it literally written as "7 years and 2 months"?

[townof12]

Ok folks looking for some help here.  I'm recording some data in years and months, eg Cell A1 7 years and 2 months, A2 8 years and 9 months.  In A3 I want to use a formula to calculate the difference.  What's my best way of doing this?  Some idea of how to use excel, but I'm no expert.

Is it literally written as "7 years and 2 months"?

I formatted the cell with 'custom' and then 00 "Year(s)," 00 "Month(s)"
This displays the data fine for me, but when I try to calculate the difference between the 2 cells, it isn't accurate.

[armaghniac]

check out =DATEDIF

you need something like =DATEDIF(A1,A2,"YM");